MySQL8.0学习记录13 - 校对规则Collation 的相关问题

Collation 的多种使用方式

COLLATE 可以在SQL的不同部分出现，这个是以前几乎不会使用到的，基本上除了from外，几乎可以出现在任何地方：

• Order By
• As
• Group By
• DISTINCT
• 聚合函数
• WHERE
• HAVING

MySQL官网中只提到了聚合函数，实际上普通函数也可以：

SET NAMES gb18030;
SELECT  COLLATION(CONCAT('风华' COLLATE gb18030_bin, '正文' COLLATE gb18030_bin));

gb18030_bin

INFORMATION_SCHEMA 查询中使用Collation

INFORMATION_SCHEMA 中的内容查询的时候校对规则区不区分大小写，有变量lower_case_table_names 控制，它有几个值：

• 0 表示区分大小写
• 1 表示存储以小写方式，但是比较的时候不区分大小写
• 2 表示存储是以给定的方式，但是比较的时候以小写比较

我在windows 上通过docker建的MySQL上的值为2：

create table t_Test(
	id int
);

select * from information_schema.TABLES t where TABLE_NAME  = 'T_TEST';

TABLE_CATALOG|TABLE_SCHEMA|TABLE_NAME|
-------------+------------+----------+----------+------+
def          |test_db     |t_Test    |

但是大部分数据库应该是在linux系统上，默认值应该是0，这个时候就可以用到COLLATE:

WHERE TABLE_NAME COLLATE utf8mb3_general_ci = 'T_TEST'

比较的两边校对规则不一致

为了解决不一致的问题，MySQL定义了一个coercibility 值（衡量可被转换的可能性），值的取值定义如下：

1. COLLATE 的coercibility 值为0，表示完全不接受强转
2. 两个不同COLLATE 的字符串串联，coercibility = 1
3. 列的COLLATE 或变量的coercibility =2
4. 系统常量(比如USER() or VERSION()返回的值)，coercibility =3
5. 对于字面量的collation，coercibility =4
6. 对于数字或时间值，coercibility =5
7. NULL或由NULL派生出来的表达式，coercibility = 6

转换规则如下：

• coercibility 值较小的一边取胜
• 如果coercibility 相同：
  • 两边都是Unicode，那么会报错
  • 一边是Unicode，那么转Unicode
  • 如果相同的字符集，一边是_bin, 那么转_bin

可以使用COERCIBILITY（）来判断coercibility 值。来看几个例子：

• 向小的一方转

SET NAMES utf8mb4  COLLATE  utf8mb4_bin;
SELECT COERCIBILITY(USER()),COERCIBILITY('abcd'),COLLATION(USER()),COLLATION('abcd'),COLLATION(CONCAT(USER(), 'asf'));

COERCIBILITY(USER())|COERCIBILITY('abcd')|COLLATION(USER())|COLLATION('abcd')|COLLATION(CONCAT(USER(), 'asf'))|
--------------------+--------------------+-----------------+-----------------+--------------------------------+
                   3|                   4|utf8_general_ci  |utf8mb4_bin      |utf8_general_ci                 |

• coercibility 相同，都是unicode,校对规则不同，不能比较

    create table t_column(
    	a varchar(10) COLLATE utf8mb4_general_ci,
    	b varchar(10) COLLATE utf8mb4_bin,
    	c varchar(10) COLLATE utf8mb4_unicode_ci,
    	d varchar(10) character set latin1
    );
    select * from t_column where a = c
  

  Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
  

• 上面的例子，a与b的比较时可以的，因为b是_bin,比较时a会转为utf8mb4_bin

• a与d也可以比较，两者字符集不同，比较时会先转utf8mb4

其他需要注意的问题

binary与_bin的不同

• _bin 很多时候可以自动转为其他字符集，binary不会
• _bin字符可以通过lower()或upper()函数改变大小写，binary不行
• _bin的CHAR类型，与binary的BINARY类型，插入数据较短的时候都会填充，前者是空字符，后者是 0x00字节；而且_bin查询出来会自动去掉补充的空字符，而binary填充的 0x00不会自动去掉；

_bin不同的校对规则对空字符的处理
校对规则都有一个属性 PAD SPACE或NO PAD，PAD SPACE的校对规则会忽略结尾的空字符，NO PAD相反；

SET NAMES utf8mb4 COLLATE utf8mb4_bin; -- PAD SPACE
SELECT 'a ' = 'a'; -- 结果是1

SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin; -- NO PAD
SELECT 'a ' = 'a'; -- 结果是0