关于mysql:技术分享-MySQL测试排序规则-collation

作者：姚嵩
外星人...
本文起源：原创投稿
*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

摘抄：

https://dev.mysql.com/doc/ref...

背景：

客户反馈查问返回的后果不合乎预期，读取时想要实现⼤⼩写辨别；

简介：

排序规定 collation 是⼀个字符集的字符进⾏⽐较的⼀组规定；

⾄少会有两个规定：
1) 是否辨别⼤⼩写；
2) 编码；

常⽤的规定是是否辨别⼤⼩写。

字符集和排序规定的默认抉择：

如果你仅指定字符集，⽽不指定排序规定，则排序规定为字符集默认的排序规定；

如果你仅指定排序规定，不指定字符集，则字符集为排序规定对应的字符集；

例外项⻅：

"设置对象的字符集和排序规定" 中 "阐明"列中蕴含的内容。

查看字符集与其默认的排序规定：

SHOW CHARACTER SET ;

或者

select * from INFORMATION_SCHEMA.CHARACTER_SETS ;

查看字符集蕴含的排序规定：

SHOW COLLATION WHERE Charset = 'utf8mb4'; -- 这⾥的utf8mb4是具体的字符集

查看数据库的默认字符集和排序规定：

USE db_name;

SELECT @@character_set_database, @@collation_database;

或者

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME

FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

设置对象的字符集和排序规定：

对象	字符集	排序规定	阐明
server	character_set_server	collation_server	如果create database时未带上字符集和排序规定，则使⽤server中申明的作为默认值；
database	建库时的CHARACTER SET ⼦句；character_set_database	建库时的COLLATE ⼦句；collation_database	如果create table时未带上字符集和排序规定，则使⽤数据库中申明的作为默认值；如果load data时未带上character set⼦句，则使⽤character_set_database作为默认值；如果在创立routine时未带上字符集和排序规定，则使⽤数据库中申明的作为默认值；
table	建表时的CHARACTER SET ⼦句；	建表时的COLLATE ⼦句；	如果未在单个列上指定字符集和排序规定，则将表中申明的作为默认值；
column	建表时字段定义上的CHARACTER SET ⼦句；	建表时字段定义上的 COLLATE ⼦句；
字符串	SELECT _utf8mb4'abc' ;	SELECT 'abc' COLLATE utf8_general_ci;	如果未指定字符集但指定了排序规定，则字符集使⽤ character_set_connection；如果未指定字符集和排序规定，则使⽤character_set_connection和collation_connection作为默认值；

阐明：

如果客户是查问表中的数据，那么寻找数据时是否疏忽⼤⼩写，取决于对应字段上的COLLATE⼦句中定义的排序规定；

如果客户是间接查问字符串，那么寻找数据时是否疏忽⼤⼩写，取决于collation_connection的值；

数据存⼊不受collation影响，数据查问受collation影响。

测试案例：

测试环境：

MySQL Version：8.0.28

根底环境：

create database if not exists test_db ;use test_db;drop table if exists test_db.a ;create table test_db.a(i char(1)) engine=InnoDB default charset=utf8mb4 ;

测试:确认collate是否影响数据的存⼊

查问表中的数据时，值是否返回受字段上的collation影响

步骤：

alter table test_db.a change i i char(1) collate utf8mb4_0900_ai_ci ; -- 设置列上疏忽⼤⼩写insert into test_db.a values('a'),('A'); select * from test_db.a ; -- 存⼊的值含 "a" "A"select * from test_db.a where i='a'; -- 返回 "a" "A"truncate table test_db.a ; alter table test_db.a change i i char(1) collate utf8mb4_0900_as_cs ; -- 设置列上辨别⼤⼩写insert into test_db.a values('a'),('A'); select * from test_db.a ; -- 存⼊的值含"a" "A"select * from test_db.a where i='a'; -- 返回 "a"truncate table test_db.a ;

后果截图：

阐明：

数据存⼊不受表字段上的collate影响；

然而搜寻记录时会受表字段上的collate影响。