PostgreSQL 中如何找出记录中是否包含编码范围内的字符,例如是否包含中文

本文涉及的产品
云原生多模数据库 Lindorm,多引擎 多规格 0-4节点
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
云数据库 MongoDB,通用型 2核4GB
简介:
+关注继续查看

标签

PostgreSQL , 是否含有中文


背景

从已有字符串内容中找出含有中文,或者找出含有单字节字符的记录。

方法要从字符串在数据库中的编码和存储说起。比如多字节字符集,单字节字符SQL_ASCII。

比如PostgreSQL中UTF8, EUC_CN属于多字节字符集,编码为变长编码。

SQL_ASCII为无编码字符集,存储为字节流。

要从不同字符集中找出含有中文的记录,该怎么找?

测试环境

已有数据库如下,注意编码

postgres=# \l  
                                      List of databases  
        Name        |  Owner   | Encoding  |  Collate   |   Ctype    |   Access privileges     
--------------------+----------+-----------+------------+------------+-----------------------  
 contrib_regression | postgres | UTF8      | C          | C          |   
 db                 | postgres | SQL_ASCII | C          | C          |   
 db1                | postgres | EUC_CN    | C          | C          |   
 postgres           | postgres | UTF8      | C          | C          |   
 template0          | postgres | UTF8      | C          | C          | =c/postgres          +  
                    |          |           |            |            | postgres=CTc/postgres  
 template1          | postgres | UTF8      | C          | C          | =c/postgres          +  
                    |          |           |            |            | postgres=CTc/postgres  
 test               | postgres | UTF8      | en_US.UTF8 | en_US.UTF8 |   
(7 rows)  

在postgres, db, db1中分别创建测试数据如下

create table test(id int, info text);  
insert into test values (1,' digoal 123');  
insert into test values (1,' digoal 德哥');  
insert into test values (1,'德哥');  

中文编码范围

UTF8的中文编码范围

参考如下文档

http://www.iteye.com/topic/977671

本次研究的unicode对象是unicode 5.2.0版本。现在最新的是6.0版

对于这次研究的unicode把编码分为以下几个平面(英文中是plane,可以认为就是不同的区位)

Unicode可以逻辑分为17平面(Plane),每个平面拥有65536( = 216)个代码点,虽然目前只有少数平面被使用。

平面0 (0000–FFFF): 基本多文种平面(Basic Multilingual Plane, BMP).

平面1 (10000–1FFFF): 多文种补充平面(Supplementary Multilingual Plane, SMP).

平面2 (20000–2FFFF): 表意文字补充平面(Supplementary Ideographic Plane, SIP).

平面3 (30000–3FFFF): 表意文字第三平面(Tertiary Ideographic Plane, TIP).

平面4 to 13 (40000–DFFFF)尚未使用

平面14 (E0000–EFFFF): 特别用途补充平面(Supplementary Special-purpose Plane, SSP)

平面15 (F0000–FFFFF)保留作为私人使用区(Private Use Area, PUA)

平面16 (100000–10FFFF),保留作为私人使用区(Private Use Area, PUA)

最有用的当然就是BMP平面0了编码从U+0000至U+FFFF。那里包含了几乎全部的常用字符。

unicode基本平面区的编码区间含义

为鉴于Unicode原有的16位空间不足以应用,于是从Unicode 3.1版本开始,设立了16个扩展字码空间,称为辅助平面,

使 Unicode 的可使用空间由6万多字增至约100万字。辅助平面字符要用上4字节来存储。

unicode中的几大区间

最后小结下:

1. 现在网上大多数用于判断中文字符的是 U+4E00..U+9FA5 这个范围是只是“中日韩统一表意文字”这个区间,但这不是全部,如果要全部包含,则还要他们的扩展集、部首、象形字、注间字母等等;

2E80-A4CF 加上 F900-FAFF 加上 FE30-FE4F

其中 

2E80-A4CF 

包含了中日朝部首补充、康熙部首、表意文字描述符、中日朝符号和标点、日文平假名、日文片假名、注音字母、谚文兼容字母、象形字注释标志、注音字母扩展、中日朝笔画、日文片假名语音扩展、带圈中日朝字母和月份、中日朝兼容、中日朝统一表意文字扩展A、易经六十四卦符号、中日韩统一表意文字、彝文音节、彝文字根

F900-FAFF

中日朝兼容表意文字

FE30-FE4F

中日朝兼容形式

所以,一般用4E00-9FA5已经可以,如果要更广,则用2E80-A4CF || F900-FAFF || FE30-FE4F

2. 全角ASCII、全角中英文标点、半宽片假名、半宽平假名、半宽韩文字母:FF00-FFEF

3. 不要太关心简繁中文的区别,如果要明确非要简体中文可参考unicode中简体中文编码

EUC_CN的中文编码范围

参考

http://tools.jb51.net/table/gb2312

GB2312标准共收录6763个汉字,其中一级汉字3755个,二级汉字3008个;同时,GB 2312收录了包括拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母在内的682个全角字符。整个字符集分成94个区,每区有94个位。

GB2312,又称为GB0,由中国国家标准总局发布,1981年5月1日实施

GB2312标准共收录6763个汉字,其中一级汉字3755个,二级汉字3008个

GB2312是一种区位码。分为94个区(01-94),每区94个字符(01-94)

01-09区为特殊符号

10-15区没有编码

16-55区为一级汉字,按拼音排序,共3755个

56-87区为二级汉字,按部首/笔画排序,共3008个

88-94区没有编码

GB2312只是编码表,在计算机中通常都是用"EUC-CN"表示法,即在每个区位加上0xA0来表示。区和位分别占用一个字节。

中文从啊到齄 编码区间是b0a1-f7ff

根据编码范围找出包含非ASCII字符的记录

1. 数据库为UTF8编码

2E80-A4CF 加上 F900-FAFF 加上 FE30-FE4F

postgres=# select * from test where info ~ '[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]';  
 id |     info       
----+--------------  
  1 |  digoal 德哥  
  1 | 德哥  
(2 rows)  

2. 数据库为EUC_CN编码

中文从啊到齄 编码区间是b0a1-f7ff

db1=# select * from test where info ~ '[\ub0a1-\uf7ff]';  
 id |     info       
----+--------------  
  1 |  digoal 德哥  
  1 | 德哥  
(2 rows)  

3. 数据库为SQL_ASCII编码

由于SQL_ASCII不检查编码,存入的数据完全取决于客户端编码,所以这种方法有一定的漏洞。

比如

db=# select info::bytea from test;  
              info                
--------------------------------  
 \x206469676f616c20313233  
 \x206469676f616c20e5beb7e593a5  
 \xe5beb7e593a5  
(3 rows)  

由于我的客户端为UTF8编码,存入的就是UTF8编码的值,这样查是可以的。

db=# select info from test where info ~ '[啊-齄]';  
     info       
--------------  
  digoal 德哥  
 德哥  
(2 rows)  

找出包含非单字节字符的记录

使用转换函数,或者长度判断即可。

1. SQL_ASCII编码

SQL_ASCII编码,以字节流形式存储,所以字节长度字符长度一样。

db=# select info,length(info),octet_length(info) from test ;  
     info     | length | octet_length   
--------------+--------+--------------  
  digoal 123  |     11 |           11  
  digoal 德哥 |     14 |           14  
 德哥         |      6 |            6  
(3 rows)  

所以只能使用正则如下,找到包含非ASCII的记录

db=# select * from test where info ~ '[^(\x00-\x7f)]';  
 id |     info       
----+--------------  
  1 |  digoal 德哥  
  1 | 德哥  
(2 rows)  

2. 多字节编码

多字节编码的数据库,使用字节长度和字符长度比较,不一样,说明包含非单字节字符。

db1=# select * from test where length(info) <> octet_length(info);  
 id |     info       
----+--------------  
  1 |  digoal 德哥  
  1 | 德哥  
(2 rows)  

或者使用正则

db1=# select * from test where info ~ '[^\u0000-\u00ff]';  
 id |     info       
----+--------------  
  1 |  digoal 德哥  
  1 | 德哥  
(2 rows)  

参考

1. https://www.postgresql.org/docs/9.6/static/functions-matching.html

\uwxyz  (where wxyz is exactly four hexadecimal digits) the character whose hexadecimal value is 0xwxyz  

\Ustuvwxyz  (where stuvwxyz is exactly eight hexadecimal digits) the character whose hexadecimal value is 0xstuvwxyz  

Hexadecimal digits are 0-9, a-f, and A-F. Octal digits are 0-7.  

Numeric character-entry escapes specifying values outside the ASCII range (0-127) have meanings dependent on the database encoding.   

When the encoding is UTF-8, escape values are equivalent to Unicode code points, for example \u1234 means the character U+1234.   

For other multibyte encodings, character-entry escapes usually just specify the concatenation of the byte values for the character.   

If the escape value does not correspond to any legal character in the database encoding, no error will be raised, but it will never match any data.  

The character-entry escapes are always taken as ordinary characters. For example, \135 is ] in ASCII, but \135 does not terminate a bracket expression.  

2. 《PostgreSQL WHY ERROR: invalid byte sequence for encoding "UTF8"》

3. 《PostgreSQL Oracle 兼容性之 - 字符编码转换 CONVERT》

4. 《PostgreSQL Oracle 兼容性之 - ASCIISTR》

5. https://www.postgresql.org/docs/9.2/static/multibyte.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
12月前
|
关系型数据库 PostgreSQL
PostgreSQL 计算字符串字符数函数(CHAR_LENGTH(str))和字符串长度函数(LENGTH(str))
PostgreSQL 计算字符串字符数函数(CHAR_LENGTH(str))和字符串长度函数(LENGTH(str))
704 0
|
SQL 关系型数据库 HIVE
hive/postgresql日期时间范围查询数据,没有的数据补0
hive/postgresql日期时间范围查询数据,没有的数据补0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 9 章 函数和操作符_9.19. 范围函数和操作符
9.19. 范围函数和操作符 范围类型的概述请见第 8.17 节。 表 9.50展示了范围类型可用的操作符。 表 9.50. 范围操作符 操作符 描述 例子 结果 = 等于 int4range(1,5) = '[1,4]'::int4range t <> 不等于 numrange(1.
1233 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.17. 范围类型
8.17. 范围类型 8.17.1. 内建范围类型 8.17.2. 例子 8.17.3. 包含和排除边界 8.17.4. 无限(无界)范围 8.17.5. 范围输入/输出 8.17.6. 构造范围 8.17.7. 离散范围类型 8.17.8. 定义新的范围类型 8.17.9. 索引 8.17.10. 范围上的约束 范围类型是表达某种元素类型(称为范围的subtype)的一个值的范围的数据类型。
1029 0
|
存储 SQL 关系型数据库
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.3. 字符类型
8.3. 字符类型 表 8.4. 字符类型 名字 描述 character varying(n), varchar(n) 有限制的变长 character(n), char(n) 定长,空格填充 text 无限变长 表 8.4显示了在PostgreSQL里可用的一般用途的字符类型。
1185 0
相关产品
云原生多模数据库 Lindorm
云数据库 Redis 版
云数据库 MongoDB 版
推荐文章
更多