关于 Db2 character set collation

已于 2023-05-25 19:39:07 修改
阅读量2k 收藏 30
点赞数 10
文章标签： 数据库 sql
于 2022-06-26 09:12:16 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zzrisme/article/details/125466607
版权

说明

  本文总结了 Db2 在不同字符集（character set）和校对（collation）设置情况下的排序和比较行为。下面的标题表示在 CREATE DATABASE 语法中的指定。

  根据参考9，如果创建数据库的时候没有指定 COLLATE USING，那么默认的 collate 是 system，规则如下：
For non-Unicode databases, the collating sequence is based on the database territory. For Unicode databases, this parameter maps to a language-aware collation, based on the client code set and territory. If an appropriate language-aware collation is turned off, then the IDENTITY collation is used.

  创建数据库的时候也可以指定某一种特定的 collate，对于 Unicode 字符集的数据库来说，Db2 提供了三种 collate：IDENTITY collation, language-aware collation, 和 locale-sensitive UCA-based collation.（参考10）

using codeset GBK territory CN

  非 Unicode 字符集，不指定 collation 的情况下，默认是 system，collation 由 codeset 和 territoy 决定，这里 territoy 是 CN，根据参考1，使用 SYSTEM_1386（参考2），表示单级比较，先比较字母顺序，再比较大小写，这里字母 j 的顺序在 J 的前面，因此排序是这样的：

db2 get db cfg for test1|grep -i "collating sequence"
 Database collating sequence                             = UNIQUE

db2 "select * from test1 order by c1"

C1        
----------
china     
China     
j2        
j6        
J2        
J6        

  6 record(s) selected.

using codeset GBK territory CN collate using identity

  非 Unicode 字符集，指定 collation 是 identity，也就是按照 codeset 编码，这时候根据 GBK 码表，大写在小写的前面，也就是：

db2 get db cfg for test2|grep -i "collating sequence"
 Database collating sequence                             = IDENTITY

db2 "select * from test2 order by c1"

C1        
----------
China     
J2        
J6        
china     
j2        
j6        

  6 record(s) selected.

注：GBK identity 和 MySQL 的 gbk_bin 排序结果是一样的。

using codeset UTF-8 territory CN

  Unicode 字符集，不指定 collation 的情况下，默认会根据 client codeset 和 territory 决定（文章第二段阐述的规则），假设 client codeset 也是 Unicode，根据参考3，collation 是 identity，因此根据 UTF-8 码表，大写在小写的前面，于是排序是：

db2 get db cfg for test2|grep -i "collating sequence"
 Database collating sequence                             = IDENTITY

db2 "select * from test3 order by c1"

C1        
----------
China     
J2        
J6        
china     
j2        
j6        

  6 record(s) selected.

注：UTF-8 identity 和 MySQL 的 utf8mb4_bin 排序结果是一样的。

using codeset UTF-8 territory CN collate using SYSTEM_1386_CN

  Unicode 字符集，指定了 language-aware-collation 为 SYSTEM_1386_CN，就会按照参考2 进行排序，先比较字母顺序，再比较大小写，也就是：

db2 get db cfg for test4|grep -i "collating sequence"
 Database collating sequence                             = SYSTEM_1386

db2 "select * from test3 order by c1"

C1        
----------
china     
China     
j2        
j6        
J2        
J6        

  6 record(s) selected.

using codeset UTF-8 territory CN collate using CLDR2701_LZH_KUNIHAN

  Unicode 字符集，指定了 locale-sensitive-collation 为 CLDR2701_LZH_KUNIHAN，根据参考4，知道这是一种 UCA 算法（多级排序），默认的排序规则是 CLDR2701_LZH_KUNIHAN_AN_CX_EX_FX_HX_NX_S3，输出是这样的：

db2 get db cfg for test5|grep -i "collating sequence"
 Database collating sequence                             = CLDR2701_LZH_KUNIHAN_AN_CX_EX_FX_HX_NX_S3

db2 "select * from test3 order by c1"

C1        
----------
china     
China     
j2        
J2        
j6        
J6        

  6 record(s) selected.

  可以看到，这是和 SYSTEM_1386 以及 identity 都不同的一种排序方式，这是因为属性 CLDR2701_LZH_KUNIHAN_AN_CX_EX_FX_HX_NX_S3 中的 S3 表示不忽略重音符和大小写，EX 表示关闭大小写级别，CX 表示如果字符串没有其他差别，那么是小写优先，这些属性组合在一起便是：
  S3 区分大小写，EX 不区分大小写，于是区分大小写，因为没有其他差别，根据小写优先，所以 china 在 China 的前面，j2 在 J2 的前面，j6 在 J6 的前面。
  注意这里非常重要的一点是，为什么小写优先，而 J2 会在 j6 的前面呢？这是因为 UCA 算法是多级比较（Multi-Level Comparison，参考5 和参考6），通俗地讲就是先不考虑大小写和重音，比较两个字符串里的字符，其次才根据是否忽略大小写和重音的属性规则来进行排序。

使用 COLLATION_KEY_BIT 进行调整和测试

  如上述，我们知道了 Db2 数据库在创建伊始就决定了排序方式，后续是无法更改的，但是有一个变通的方法是使用 COLLATION_KEY_BIT 函数可以显式指定一种 collation，这里可以指定 Unicode Collation Algorithm-based collations 或者 language-aware collations for Unicode data，它可以在任何一种数据库中操作，因此可以方便地进行验证和测试，这里举几个例子：

  1. 我们在一个 using codeset GBK territory CN 的数据库中进行操作，这显然不是一个 UCA 算法的数据库，正如第一小节所描述的，正常情况下，排序和比较应该是这样的：
db2 "select * from test1 order by c1"

C1        
----------
china     
China     
j2        
j6        
J2        
J6        

  6 record(s) selected.

db2 "select * from sysibm.sysdummy1 where 'a'='A'"

IBMREQD
-------

  0 record(s) selected.
  1. 使用 S1 不区分重音和大小写，使用 EX 不区分大小写，结果是不区分大小写：
db2 "select * from sysibm.sysdummy1 where COLLATION_KEY_BIT('a','CLDR2701_LZH_KUNIHAN_S1_EX')=COLLATION_KEY_BIT('A','CLDR2701_LZH_KUNIHAN_S1_EX')"

IBMREQD
-------
Y      

  1 record(s) selected.
  1. 用 S1 不区分重音和大小写，使用 EO 区分大小写，结果是区分大小写：
db2 "select * from sysibm.sysdummy1 where COLLATION_KEY_BIT('a','CLDR2701_LZH_KUNIHAN_S1_EO')=COLLATION_KEY_BIT('A','CLDR2701_LZH_KUNIHAN_S1_EO')"

IBMREQD
-------

  0 record(s) selected.
  1. S2 和 S1 的差别仅在重音，大小写方面没有差别，测试结果也完全一样：
db2 "select * from sysibm.sysdummy1 where COLLATION_KEY_BIT('a','CLDR2701_LZH_KUNIHAN_S2_EX')=COLLATION_KEY_BIT('A','CLDR2701_LZH_KUNIHAN_S2_EX')"

IBMREQD
-------
Y      

  1 record(s) selected.

db2 "select * from sysibm.sysdummy1 where COLLATION_KEY_BIT('a','CLDR2701_LZH_KUNIHAN_S2_EO')=COLLATION_KEY_BIT('A','CLDR2701_LZH_KUNIHAN_S2_EO')"

IBMREQD
-------

  0 record(s) selected.
  1. 使用 S3 区分重音和大小写，使用 EX 不区分大小写，结果是区分大小写：
db2 "select * from sysibm.sysdummy1 where COLLATION_KEY_BIT('a','CLDR2701_LZH_KUNIHAN_S3_EX')=COLLATION_KEY_BIT('A','CLDR2701_LZH_KUNIHAN_S3_EX')"

IBMREQD
-------

  0 record(s) selected.
  1. 使用 S3 区分重音和大小写，使用 EO 区分大小写，结果是区分大小写：
db2 "select * from sysibm.sysdummy1 where COLLATION_KEY_BIT('a','CLDR2701_LZH_KUNIHAN_S3_EO')=COLLATION_KEY_BIT('A','CLDR2701_LZH_KUNIHAN_S3_EO')"

IBMREQD
-------

  0 record(s) selected.
  1. 使用 CU 大写优先，注意这里依然是多级比较：
db2 "select * from test1 order by COLLATION_KEY_BIT(c1,'CLDR2701_LZH_KUNIHAN_CU')"

C1        
----------
China     
china     
J2        
j2        
J6        
j6        

  6 record(s) selected.
  1. 使用 CL 小写优先，注意这里依然是多级比较：
db2 "select * from test1 order by COLLATION_KEY_BIT(c1,'CLDR2701_LZH_KUNIHAN_CL')"

C1        
----------
china     
China     
j2        
J2        
j6        
J6        

  6 record(s) selected.
  1. 不管是 CU 还是 CL，至少要开启区分大小写，如果都关闭（(S1 或 S2) 且 EX），那么对于相同的字符串，大写或是小写在前面都是合理的，会按照输入（INSERT）的顺序排序输出：
db2 "select * from test1 order by COLLATION_KEY_BIT(c1,'CLDR2701_LZH_KUNIHAN_S1_EX_CU')"

C1        
----------
china     
China     
J2        
j2        
j6        
J6        

  6 record(s) selected.

db2 "select * from test1 order by COLLATION_KEY_BIT(c1,'CLDR2701_LZH_KUNIHAN_S2_EX_CU')"

C1        
----------
china     
China     
J2        
j2        
j6        
J6        

  6 record(s) selected.

Db2 和 MySQL 关于 collation 的比较

总结来说：

  • Db2 的 UTF-8 identity 和 GBK identity 分别等价于 MySQL 的 utf8mb4_bin 和 gbk_bin
  • Db2 非 Unicode 的 system 映射到 language-aware 的 collation 无法在 MySQL 中找到等价的 collation
  • Db2 Unicode 的 UCA 中的 KUNIHAN_S1_EX 或 KUNIHAN_S2_EX 等价于 MySQL 的 utf8mb4_unicode_ci，但需要注意这里忽略大小写，所以不同大小写的相同字符串会按照输入顺序来排序输出。
  • MySQL 的 gb18030_chinese_ci 或 gbk_chinese_ci 在算法上也是 UCA 的多级比较（优先看整个字符串，再考虑大小写），所以 Db2 Unicode 的 UCA 中的 KGB2312HAN_S1_EX 或 KGB2312HAN_S2_EX 等价于 MySQL 的 gb18030_chinese_ci 或 gbk_chinese_ci，但需要注意这里忽略大小写，所以不同大小写的相同字符串会按照输入顺序来排序输出。
  • Db2 UCA 中的 CLDR2701_LZH_KGB2312HAN 和 CLDR2701_LZH 经测试可以根据拼音排序中文字符
  • Db2 UCA 中的 CLDR2701_LZH_KSTROKE 经测试可以根据笔画多少排序

附录

  1. Supported territory codes and code pages
  2. Code page 1386, Generic (SYSTEM_1386)
  3. Code set and collation combinations
  4. Unicode Collation Algorithm based collations
  5. UNICODE COLLATION ALGORITHM
  6. Unicode 字符串排序规则（二）：如何比较字符串
  7. Unicode 归类算法 (UCA)
  8. 10.10.1 Unicode Character Sets
  9. CREATE DATABASE command
    10.Choosing a collation for a Unicode database
朱峥嵘（朱髯）
关注
  • 10
    点赞
  • 踩
  • 30
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
Db2 Create database时COLLATE USING的解释
qq_42616592的博客
12-18 1012
Db2 Create database时COLLATE USING的解释 摘自IBM官网，个人渣翻译，欢迎指正 个人理解下来collating sequence是用于数据库在比较数据大小的基准表，简单点的例子就是用于判断 正序排序时 字符A与字符B 谁在前谁在后的一个用于比较的基准表。 The database manager compares character data using a col...
MySQL collation方法
09-11
MySQL中的Collation是一种用于排序和比较字符串的规则，它与字符集（Character Set）紧密相关。字符集定义了可以存储的字符范围，而Collation决定了这些字符如何进行比较和排序。在Oracle数据库中，可能由于其不同的...
DB2中文排序
lennonlau的博客
01-12 902
一般来说，使用UTF8字符集，容易导致中文排序混乱 解决方法：ORDER BY COLLATION_KEY_BIT(字段名, ‘UCA500R1_LZH_KGB2312HAN_AN_CX_EX_FX_HX_NX_S3’) asc; 基于 Unicode 整理算法的整理 http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.nls.doc/doc/r0050489.html CO
在db2中varchar和character有何区别
whxwkb的博客
04-20 8559
character 其实就是char，1. 最大长度不同   char 最大254 bytes,而varchar 最大 32672 bytes2. 存储不同char(n)  在数据库占用 n 个字节，在数据库中以空格补足,但在取出来时末尾的空格将被去掉 varchar(n) 在数据库中至少占用1个字节，在数据库中末尾的空格将自动去掉，实际占用录入数据长度 ＋1 或者 +2 字节。 一般使用varc...
DB2迁移MySQL注意事项：MySQL中字符串比较时默认不区分大小写
匿_名_用_户的专栏
10-02 680
在DB2数据库中，字符/字符串比较的时候，默认是区分大小写的，即'abc'和'ABC'是两个不同的字符串，但在 MySQL中，默认是不区分大小写的，因此迁移的时候需要注意。 示例1中，可以看到'abc' 和 'ABC' 在innodb中存放的数据是不一样的，但比较的时候认为是一样的 mysql> create table mqs1(col1 char(20)) CHARSET=utf8mb4; Query OK, 0 rows affected (0.26 sec) mysql> ins
TreeSet 和 TreeMap 排序
qq_42585916的博客
09-13 398
TreeSet 和 TreeMap 默认排序均为按照字典顺序升序排序，TreeMap排序依据为key值 以下为TreeSet 和 TreeMap 自定义倒序排序样例： new TreeSet<Integer>(new Comparator<Integer>() { @Override public int compare(Integer o1, Integer o2) { return o2 - o1; } }); new TreeMap<Integer, .
语法教学02 建立与删除数据库, 简单说明Character与Collation的选择
08-31
[MySQL][關聯式資料庫][DB]_語法教學#02__建立與刪除資料庫,_簡單說明Character與Collation的選
SQL Server 更改DB的Collation
09-11
在SQL Server中，Collation（排序规则）定义了数据库中字符数据的存储方式以及比较和排序的规则。它包括字符集、排序顺序和大小写敏感性等元素。Collation是数据库创建时的一个重要属性，影响着数据库中的字符串比较...
自动清理 MSSQL Server Table Collation问题的解决方法
09-10
排序规则包括字符集（Character Set）和排序规则（Collation）。在这个问题中，用户遇到了“Cannot resolve the collation conflict between 'Latin1_General_CI_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the ...
mysql字符集与比对方法collation
最新发布
02-21
字符集（Character Set）定义了数据的编码方式，而比对方法（Collation）则规定了字符的排序和比较规则。在SQL中，这两个概念对于保证数据的一致性和正确性至关重要。 首先，我们来详细了解一下字符集。字符集是...
DB2数据库，按照指定顺序排序
四块的博客
07-14 636
DB2数据库，按照指定顺序排序
DB2 中的基本数据类型
larry_lv的专栏
02-16 683
DB2内置数据类型可以分成数值型(numeric)、字符串型(character string)、图形字符串(graphic string)、二进制字符串型(binary string)或日期时间型(datetime)。还有一种叫做 DATALINK 的特殊数据类型。DATALINK 值包含了对存储在数据库以外的文件的逻辑引用。 　　数值型数据类型包括 SMALLINT、INTEGER、BIGI
技术分享 | MySQL--测试--排序规则 collation
ActionTech的博客
10-19 427
排序规则 collation 是⼀个字符集的字符进⾏⽐较的⼀组规则；是否区分⼤⼩写；编码；常⽤的规则是是否区分⼤⼩写。如果你仅指定字符集，⽽不指定排序规则，则排序规则为字符集默认的排序规则；如果你仅指定排序规则，不指定字符集，则字符集为排序规则对应的字符集；例外项⻅：“设置对象的字符集和排序规则” 中 "说明"列中包含的内容。或者– 这⾥的utf8mb4是具体的字符集或者对象字符集排序规则说明server。
db2 常用命令
lsz_ch的博客
08-28 506
db2常用命令大全 近一年来在项目开发中使用到了IBM的DB2 9.1的数据库产品，跟Oracle相比一些命令有很大的区别，而它最大的功能是支持 xml存储、检索机制，通过XPath进行解析操作，使开发人员免于对xml文件在应用进行解析处理，先对其常用命令进行一下汇 总，以免遗忘。 注意：在执行如下命令时，需要首先安装db2客户端并通过在运行中输入db2cmd进行初始化 ...
sql实例总结
qq_37158147的博客
08-25 1154
sql经典实例
一文理清 TiDB 与 MySQL 中的常用字符集及排序规则
TiDB 社区干货传送门
04-24 316
作者： 沈阳盛京征信有限公司 原文来源： https://tidb.net/blog/0c5b6025 ...
engine = innodb character set = utf8mb4 collation = utf8mb4_unicode_ci
05-24
character set = utf8mb4表示使用UTF-8编码来存储数据，utf8mb4是MySQL支持的一种字符编码，它可以存储更多的Unicode字符。collation = utf8mb4_unicode_ci表示使用utf8mb4字符集的Unicode排序规则进行排序。这些...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

朱峥嵘（朱髯）

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值