标签
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平面０了编码从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