各种类型的编码
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
5.7 及更早版本默认字符集和 Collation 是 latin1 和 latin1_swedish_ci
8.0 之后改成 utf8mb4 和 utf8mb4_0900_ai_ci
上面就有 6 种 全局的编码:
- client 客户端编码
- connection 连接编码
- database 数据库编码,创建表时的默认编码
load data, 以及创建 - filesystem 文件系统编码
- results 结果编码
- server 服务端编码,创建数据库时的默认编码
- system 系统编码
还有两种局部的编码:表的编码和列(字段)的编码。
列主要是 char, varchar, text 类型。
字符集
# 都可以 LIKE 搜索
SHOW CHARACTER SET;
SHOW CHARSET;
SHOW CHAR SET;
Charset | Description | Default collation | Maxlen |
---|---|---|---|
armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
ascii | US ASCII | ascii_general_ci | 1 |
big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
binary | Binary pseudo charset | binary | 1 |
cp1250 | Windows Central European | cp1250_general_ci | 1 |
cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
cp1256 | Windows Arabic | cp1256_general_ci | 1 |
cp1257 | Windows Baltic | cp1257_general_ci | 1 |
cp850 | DOS West European | cp850_general_ci | 1 |
cp852 | DOS Central European | cp852_general_ci | 1 |
cp866 | DOS Russian | cp866_general_ci | 1 |
cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
dec8 | DEC West European | dec8_swedish_ci | 1 |
eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
euckr | EUC-KR Korean | euckr_korean_ci | 2 |
gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
greek | ISO 8859-7 Greek | greek_general_ci | 1 |
hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
hp8 | HP West European | hp8_english_ci | 1 |
keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
latin1 | cp1252 West European | latin1_swedish_ci | 1 |
latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
macce | Mac Central European | macce_general_ci | 1 |
macroman | Mac West European | macroman_general_ci | 1 |
sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
tis620 | TIS620 Thai | tis620_thai_ci | 1 |
ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 |
utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
SELECT * FROM information_schema.character_sets;
SELECT * FROM information_schema.character_sets WHERE CHARACTER_SET_NAME LIKE "%utf%";
CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
---|---|---|---|
utf8mb3 | utf8mb3_general_ci | UTF-8 Unicode | 3 |
utf16 | utf16_general_ci | UTF-16 Unicode | 4 |
utf16le | utf16le_general_ci | UTF-16LE Unicode | 4 |
utf32 | utf32_general_ci | UTF-32 Unicode | 4 |
utf8mb4 | utf8mb4_0900_ai_ci | UTF-8 Unicode | 4 |
排序规则
SHOW COLLATION;
这就多了,两百多。
SHOW COLLATION LIKE "%ascii%";
Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
---|---|---|---|---|---|---|
ascii_bin | ascii | 65 | Yes | 1 | PAD SPACE | |
ascii_general_ci | ascii | 11 | Yes | Yes | 1 | PAD SPACE |
SELECT * FROM information_schema.collations WHERE CHARACTER_SET_NAME = "utf8mb4" AND COLLATION_NAME LIKE "%zh%";
COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
---|---|---|---|---|---|---|
utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | Yes | 0 | NO PAD |
命名规则
字符集名称,语言,通用后缀
ai
Accent-insensitive 重音不敏感as
Accent-sensitive 重音敏感ci
Case-insensitive 大小写不敏感cs
Case-sensitive 大小写敏感ks
Kana-sensitive 假名敏感(日语)bin
二进制
8.0 之后,很多编码中多了 0900 字样,表示 Unicode 9.0 规范。
参考资料与拓展阅读
- 博客园,2021/07/10,mysql 设置了 utf8mb4,为什么还有 utf8mb4_general_ci 和 utf8mb4_0900_ai_ci?
- CSDN 博客,2023-07-07,数据库之 MySQL 字符集与数据库操作