金仓数据库 KingbaseES V8R6 MySQL 和 KingbaseES 字符串排序规则对比

原创 lanpangzi 2023-07-14

583

在 MySQL 和 KingbaseES 下，相同数据排序后查询得到的排序顺序不一致。本案例从 MySQL 和 KingbaseES 的排序规则分析，两种数据库排序的异同点。

适用版本： KingbaseES V8R6、MySQL 8.0

一、MySQL 的排序规则

1、排序规则 (collation)
排序规则是依赖于字符集，字符集是用来定义 MySQL 存储不同字符的方式，而排序规则一般指对字符集中字符串之间的比较、排序制定的规则。一种字符集可以对应多种排序规则，但是一种排序规则只能对应指定的一种字符集，两个不同的字符集不能有相同的排序规则。 file

上图中，Collation 列表示排序方式，Charset 列表示字符集，可以看出 utf8 字符集对应着许多的排序方式，排序方式那一列每一项的值都不一样，并且每一项都对应唯一一种字符集，在这里是 utf8 字符集。

2、默认排序规则 (字符集的默认排序规则)
每种字符集都有一个默认的排序规则，可以通过下面的 SQL 语句查询指定字符集的默认排序规则：

mysql> show character set like 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)

上面的例子是查询字符集前缀包含 utf8 的默认排序方式，从中可以得知：

utf8字符集的默认排序方式是 utf8_general_ci 字符集中字符最大长度占3个字节
utf8mb4 字符集的默认排序方式是 utf8mb4_general_ci 字符集中字符最大长度占4个字节

3、指定数据库和表字符集及排序规则

1) 指定数据库字符集和排序规则

mysql> create database prod1 
       CHARACTER SET =utf8  COLLATE=utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
    ->  from INFORMATION_SCHEMA.SCHEMATA
    ->  where SCHEMA_NAME='prod1';
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| prod1       | utf8                       | utf8_general_ci        |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

2) 指定表字符集和排序规则

mysql> create table t1 (id int)
    -> ENGINE=InnoDB DEFAULT CHARSET=utf8 collate=utf8_general_ci ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

4、查看表的字符集和排序规则

1) 字符集为 utf8mb4 (默认排序规则)

mysql> show create table bdsj_bdgl_test\G
*************************** 1. row ***************************
       Table: bdsj_bdgl_test
Create Table: CREATE TABLE `bdsj_bdgl_test` (
  `BDSJ_BDGL_NM` varchar(32) NOT NULL,
  `BDSJ_BDGL_BDBH` varchar(32) DEFAULT NULL,
  `BDSJ_BDGL_BDLJ` varchar(256) DEFAULT NULL,
 ......
  PRIMARY KEY (`BDSJ_BDGL_NM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

如下图所示，(CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci) 排序规则： file

2) 字符集为 utf8 (默认排序规则)

mysql> show create table bdsj_bdg2_test\G
*************************** 1. row ***************************
       Table: bdsj_bdg2_test
Create Table: CREATE TABLE `bdsj_bdg2_test` (
  `BDSJ_BDGL_NM` varchar(32) NOT NULL,
  `BDSJ_BDGL_BDBH` varchar(32) DEFAULT NULL,
  `BDSJ_BDGL_BDLJ` varchar(256) DEFAULT NULL,
  .......
  PRIMARY KEY (`BDSJ_BDGL_NM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

如下图所示，(CHARSET=utf8) 默认排序规则： file

二、KingbaseES 排序规则

1、排序规则 (collation)

排序规则定义中有一个提供程序，它指定哪个库提供语言环境数据。一个标准的提供者名称是 libc ，它使用操作系统 C 库提供的语言环境。这些是操作系统提供的大多数工具使用的语言环境，不同操作系统的 libc 版本不同可能会导致排序顺序略有不同。另一个提供者是 icu ，它使用外部 ICU 库。只有在构建 KingbaseES 时配置了对 ICU 的支持，才能使用 ICU 区域设置。

libc 提供的排序规则对象映射到 setlocale () 系统调用接受的 LC_COLLATE 和 LC_CTYPE 设置的组合。 (正如其名字，一个排序规则的主要目的是设置 LC_COLLATE ，它控制排序顺序。但是在实际中，很少有将 LC_COLLATE 设置成与 LC_CTYPE 不同，因此在同一个概念下收集这些设置比创建另一个基础结构来设置每一个表达式的 LC_CTYPE 更加方便)。此外，一个 libc 排序规则是和一个字符集编码 (见字符集) 绑定在一起的。相同的排序规则名字可能存在于不同的编码中。

如下图所示，数据库字符集和对应的排序规则：

file

2、指定数据库的字符集

test=# create database prod2
test-# WITH ENCODING 'utf8' lc_collate='en_US.UTF-8' lc_ctype='en_US.UTF-8';
CREATE DATABASE

test=# \l prod2
                             List of databases
 Name  | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-------+--------+----------+-------------+-------------+-------------------
 prod2 | system | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

3、查看表数据排序

表结构信息
字符集为 utf8，collate 为 ci_x_icu 的排序：
1) 数据库字符集 2) 字段排序信息
字符集为 utf8，collate 为 en_US.UTF-8 的排序：
1) 数据库字符集 2) 字段排序信息

三、总结

在数据库中，字符串 (character) 类型的字段数据在排序时，从左到右一个一个字符的进行比较，按照数据库或表及字段的排序规则进行排序。通过对 MySQL 和 KingbaseES 字符串类型的排序比较，可以获取以下结果： file

本文简单对比了 MySQL 和 KingbaseES 的排序规则，需要更深入了解数据库的排序，可以参考相关数据库的官方文档说明。更多信息，参见 https://help.kingbase.com.cn/v8/index.html在 MySQL 和 KingbaseES 下，相同数据排序后查询得到的排序顺序不一致。本案例从 MySQL 和 KingbaseES 的排序规则分析，两种数据库排序的异同点。