【版本】社区版 4.2.2.1 和 企业版 4.2.2.1 。其他版本未验证。
【概述】
当将 OB MySQL 租户的所有有关 collation 都设置为 utf8mb4_bin ，并且数据库默认 collation 也设置为 utf8mb4_bin 时，建表的时候如果不指定字符集参数，则数据的查询结果里大小写是敏感的。但是如果建表的时候带上了字符集 utf8mb4的时候，列的collation 设置就没有跟随全局设置或数据库设置。 从而带来跟 MySQL 不一样的结果。
复现步骤。
[admin@RS-OBDB-P1 ~]$ mysql -h127.1 -uroot@ten100#obrs -P2883 -paaAA11__ -c -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3190770
Server version: 8.0.20 OceanBase_CE 4.2.2.1 (r101000012024030709-083a68a2907b6a1a12138c4a9e0994949166bfba) (Built Mar 7 2024 10:10:58)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show global variables like '%collate%';
Empty set (0.00 sec)
MySQL [(none)]> show global variables like '%collat%';
+----------------------+-------------+
| Variable_name | Value |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
| collation_database | utf8mb4_bin |
| collation_server | utf8mb4_bin |
+----------------------+-------------+
3 rows in set (0.00 sec)
MySQL [(none)]> show variables like '%collat%';
+----------------------+-------------+
| Variable_name | Value |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
| collation_database | utf8mb4_bin |
| collation_server | utf8mb4_bin |
+----------------------+-------------+
3 rows in set (0.00 sec)
MySQL [(none)]> show create database test2;
+----------+-------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------+
| test2 | CREATE DATABASE `test2` DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE = utf8mb4_bin REPLICA_NUM = 3 |
+----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> use test2;
Database changed
MySQL [test2]> create table t1(id bigint not null primary key, c1 varchar(50));
Query OK, 0 rows affected (0.07 sec)
MySQL [test2]> create table t2(id bigint not null primary key, c1 varchar(50)) default charset=utf8mb4;
Query OK, 0 rows affected (0.06 sec)
MySQL [test2]> insert into t1 values(1,'a'),(2,'A');
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL [test2]> insert into t2 values(1,'a'),(2,'A');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL [test2]> select * from t1 where c1='a';
+----+------+
| id | c1 |
+----+------+
| 1 | a |
+----+------+
1 row in set (0.00 sec)
MySQL [test2]> select * from t2 where c1='a';
+----+------+
| id | c1 |
+----+------+
| 1 | a |
| 2 | A |
+----+------+
2 rows in set (0.00 sec)
MySQL [test2]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` bigint(20) NOT NULL,
`c1` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE
TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.00 sec)
MySQL [test2]> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` bigint(20) NOT NULL,
`c1` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 1342177
28 PCTFREE = 0
1 row in set (0.00 sec)
这个在 MySQL 8.0 里面，也有类似问题。不过 MySQL 新增了一个实例变量 default_collation_for_utf8mb4 去弥补了这个问题。
【问题影响】
DDL 里带字符集时表的查询行为不符合预期，这个会带来业务问题。期待官方能尽快修复这个 BUG（社区版跟企业版同时修复）。
当然通常开发建表也不会去指定字符集，跟随全局设置或数据库设置就行了。这个表结构恰好是 OMS 在做 表结构迁移的时候创建的。牵涉的表非常多，不得不一一用 ALTER TABLE 去修复。
1 个赞
xuyu
#4
我理解一下，也就是说在不依赖default_collation_for_utf8mb4这个mysql 8.0的情况下mysql与ob的表现是一致么？可以用mysql5.x比较下，目前ob对5.x的兼容性做得稍微好一点
晓楚
#5
如果仅设置了 utf8mb4 字符集，没有指定collation，会找 utf8mb4 字符集默认的collation（这个是定死的，通过 SHOW COLLATION WHERE Charset = ‘utf8mb4’ 看 Default 列）
mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';
+-----------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-----------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
+-----------------------+---------+-----+---------+----------+---------+
22 rows in set (0.00 sec)
以上是 MySQL 的输出。
在 OceanBase 中，也是一样：
OceanBase(admin@test)>SHOW COLLATION WHERE Charset = 'utf8mb4';
+--------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 1 |
+--------------------+---------+-----+---------+----------+---------+
3 rows in set (0.002 sec)
用户如果想要指定collation，就要两个都制定全；如果想要继承，那就都charset 和 collation都不要写，自动继承db的；default_collation_for_utf8mb4 确实可以修改utf8的默认值，但是其他字符集就没办法了。所以，default_collation_for_utf8mb4 是 MySQL 8的一个补丁，一个看上去非常临时的补丁。
2 个赞
晓楚
#6
这倒是一个很有趣的设计问题。
问：在 create table 时如果指定了 charset 是 utf8mb4，必须也明确指定 collation，才能让表的 collation 是 binary 吗？有没有办法只指定 charset，让 collation 继承什么值？
答：理论上不太可能简单做到。从产品的角度思考，大概是因为，假设用户指定的 charset 不是utf8mb4，而是一个什么 latin1，那么这个 collation_database 即使指定了 utf8mb4_bin，也毫无意义。除非弄出一堆变量，default_collation_for_latin1, default_collation_for_utf8mb4，等等，这显然不是一个好的设计。
如果可以自由设计，倒是有一种思路，提供一个 alter charset 命令，改变各种 charset 的 default collation，也就是改变 SHOW COLLATION WHERE Charset = 'utf8mb4'; 输出的 default 列的取值。
alter charset utf8mb4 default collation binary;
alter charset latin1 default collation swidish_ci;
1 个赞
旭辉
#7
mysql8的default_collation_for_utf8mb4取值仅支持utf8mb4_0900_ai_ci，utf8mb4_general_ci，这种场景下做不到大小写敏感支持。
1 个赞
确实如此。指定参数时字符集和排序规则要同时指定。
谢谢回答。
我再测试了一下又有些新的发现，明白为什么我会认为 OMS 只指定了字符集没有指定排序规则。
MySQL [test]> create table t20(id bigint not null primary key,c1 varchar(50)) default charset=utf8mb4;
Query OK, 0 rows affected (0.12 sec)
MySQL [test]> create table t21(id bigint not null primary key,c1 varchar(50)) default charset=utf8mb4 default collate=utf8mb4_general_ci;
Query OK, 0 rows affected (0.11 sec)
MySQL [test]> create table t22(id bigint not null primary key,c1 varchar(50)) default charset=utf8mb4 default collate=utf8mb4_bin; Query OK, 0 rows affected (0.12 sec)
MySQL [test]> show create table t20\G
*************************** 1. row ***************************
Table: t20
Create Table: CREATE TABLE `t20` (
`id` bigint(20) NOT NULL,
`c1` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 13421
7728 PCTFREE = 0
1 row in set (0.06 sec)
MySQL [test]> show create table t21\G
*************************** 1. row ***************************
Table: t21
Create Table: CREATE TABLE `t21` (
`id` bigint(20) NOT NULL,
`c1` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 13421
7728 PCTFREE = 0
1 row in set (0.03 sec)
MySQL [test]> show create table t22\G
*************************** 1. row ***************************
Table: t22
Create Table: CREATE TABLE `t22` (
`id` bigint(20) NOT NULL,
`c1` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FAL
SE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.01 sec)
当 指定 排序规则是 utf8mb4_general_ci 后，show create table 后居然看不到 collate 的定义。而指定为 utf8mb4_bin 就能显示。
OB 这里应该也能优化一下。
晓楚
#9
我猜测内部的实现逻辑是：如果用户指定的 collate = 默认 collate，那么就不展示，否则就展示。
我认为比较好的逻辑是：如果用户指定过，无论是否等于默认的 collate，就总是 show create table 的时候展示出来。
@xuyu
2 个赞
恩，就是这个意思 
mysql 在 show 表定义上不太严谨。在建表语句不指定字符集，会用默认字符集设置。show 的时候它也展示了默认字符集。 上面 t1 表。
而在 collate 上就没保持行为的一致性。
OB 可以做的更严谨一些。
