前言
对于MySQL一堆的排序规则是否一脸懵,不知道每个排序规则是什么,为什么选择这个排序规则
排序规则
后缀名含义说明
- utf8mb4_0900_ai_ci基于UCA 9.0.0重量键(http://www.unicode.org/Public/UCA/9.0.0/allkeys.txt)。
- utf8mb4_unicode_520_ci基于UCA 5.2.0配重键(http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt)。
- utf8mb4_unicode_ci(未命名版本)基于UCA 4.0.0重量键(http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt)。
设置字符集和排序规则
服务器端
配置文件端可如下设置,变量可动态修改
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
若不设置 collation ,会自动使用字符集下的默认排序规则,默认排序规则如下
root@yq [(none)]> SHOW COLLATION LIKE 'utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | PAD SPACE |
| utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 0 | NO PAD |
| utf8mb4_cs_0900_as_cs | utf8mb4 | 289 | | Yes | 0 | NO PAD |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | PAD SPACE |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | PAD SPACE |
| utf8mb4_da_0900_ai_ci | utf8mb4 | 267 | | Yes | 0 | NO PAD |
| utf8mb4_da_0900_as_cs | utf8mb4 | 290 | | Yes | 0 | NO PAD |
| utf8mb4_de_pb_0900_ai_ci | utf8mb4 | 256 | | Yes | 0 | NO PAD |
| utf8mb4_de_pb_0900_as_cs | utf8mb4 | 279 | | Yes | 0 | NO PAD |
数据库
每个数据库可单独设置对应的字符集和排序规则
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
查看数据库默认字符集与排序规则
SELECT
SCHEMA_NAME,
DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM
INFORMATION_SCHEMA.SCHEMATA;
表单独设置字符集和排序规则
CREATE TABLE t1 ( ... ) CHARACTER SET latin1 COLLATE latin1_danish_ci;
列字符集和排序规则
CREATE TABLE t1 (
col1 CHAR (10) CHARACTER
SET utf8 COLLATE utf8_unicode_ci
) CHARACTER
SET latin1 COLLATE latin1_bin;
若不指定,默认使用表的字符集和排序规则
注:
表或字段设计的时候,关联字段一定要使用相同的类型与字符集,否则会出现隐式转换,导致所建的索引不可用