Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

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

3 minute read

背景

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

方法要从字符串在数据库中的编码和存储说起。比如多字节字符集，单字节字符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

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...