MySQL之字符集与排序规则详解
#########################
一、基础
1.什么是编码与解码？
计算机中储存的信息都是用二进制数表示的；而我们在屏幕上看到的英文、汉字等字符是二进制数转换之后的结果。通俗的说，按照何种规则将字符存储在计算机中，如'a'用什么表示，称为"编码"；反之，将存储在计算机中的二进制数解析显示出来，称为"解码"，如同密码学中的加密和解密。在解码过程中，如果使用了错误的解码规则，则导致'a'可能解析成'b'或者乱码。
2.什么是字符？
通俗的讲字符就是计算机中字母、数字、符号的统称，一个字符可以是一个中文汉字、一个英文字母、一个阿拉伯数字、一个标点符号等等。
3.什么是字符集？
一组字符和对应每个字符唯一编号的映射关系集合。字符集里的每一个字符都有一个编号，即映射到的数字，也被称作码点（Code Point）。字符集种类较多，每个字符集包含的字符个数不同，常见字符集：ASCII字符集、ISO-8859-1字符集、GB2312字符集、GBK字符集、 GB18030字符集、Unicode字符集等。
4.什么是UNICODE？
ASCII作为一个最早实现的字符集，后面陆续出现了各种字符集，各种编码方案。各种字符集互不兼容，而且因为各种字符集映射的唯一数字编号还有重复，这就造成不同字符集共享或交换信息时候会造成乱码和混乱。为解决这一系列的问题UNICODE字符集应运而生，UNICODE是一个世界级的字符集，保证了全世界的每种语言字符都有唯一的编号，从而可以在国际范围内交换文本数据不受以上问题困扰。
5.什么是字符编码？
规定了如何将字符的编号存储到计算机中。大部分字符集都只对应一种字符编码。例如：ASCII、ISO-8859-1、GB2312、GBK、GB18030，都是既表示了字符集又表示了对应的字符编码方式。所以一般情况下，可以将两者视为同义词。Unicode字符集除外，Unicode有三种编码方式，即UTF-8、UTF-16和UTF-32。 UTF是英文Unicode Transformation Format缩写，意为Unicode字符转换格式
字符编码的思路： 字符 -> 映射成唯一数字编号 -> 编码成计算机可以存储的二进制格式
UTF-8字符编码优势？ 与ASCII编码完全兼容。 变长编码方式节省存储空间。 自同步和纠错能力强，适合网络传输和通信。 UTF-8字符编码劣势？ 变长编码方式不利于程序内部处理。
UTF-8的编码规则： 对于单字节的字符，字节的第一位设为0，后面7位为这个字符的Unicode码。因此对于英语字母，UTF-8编码和ASCII码是相同的。 对于n字节的字符（n > 1），第一个字节的前n位都设为1，第n+1位设为0，后面字节的前两位一律设为10。剩下的没有提及的二进制位，全部为这个字符的Unicode码。
什么是排序规则(COLLATE)? 排序规则是一组用于比较字符集中字符的规则，用于定义如何比较和排序字符串。 MySQL中的每个排序规则都属于一个指定的字符集。 每个字符集至少有一个排序规则，大多数都有两个或多个排序规则。排序规则根据权重对字符进行比较排序。 字符集中的每个字符都映射到一个权重编码。 具有相等权重编码的字符比较表示相等，具有不等权重编码的字符根据其权重编码相对大小进行排序
二、MySQL中的字符集和排序规则
mysql> show character 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 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.06 sec)
mysql> 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 | | utf8mb4_eo_0900_ai_ci | utf8mb4 | 273 | | Yes | 0 | NO PAD | | utf8mb4_eo_0900_as_cs | utf8mb4 | 296 | | Yes | 0 | NO PAD | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | PAD SPACE | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | PAD SPACE | | utf8mb4_es_0900_ai_ci | utf8mb4 | 263 | | Yes | 0 | NO PAD | | utf8mb4_es_0900_as_cs | utf8mb4 | 286 | | Yes | 0 | NO PAD | | utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 | | Yes | 0 | NO PAD | | utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 | | Yes | 0 | NO PAD | | utf8mb4_et_0900_ai_ci | utf8mb4 | 262 | | Yes | 0 | NO PAD | | utf8mb4_et_0900_as_cs | utf8mb4 | 285 | | Yes | 0 | NO PAD | | utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | PAD SPACE | | utf8mb4_hr_0900_ai_ci | utf8mb4 | 275 | | Yes | 0 | NO PAD | | utf8mb4_hr_0900_as_cs | utf8mb4 | 298 | | Yes | 0 | NO PAD | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | PAD SPACE | | utf8mb4_hu_0900_ai_ci | utf8mb4 | 274 | | Yes | 0 | NO PAD | | utf8mb4_hu_0900_as_cs | utf8mb4 | 297 | | Yes | 0 | NO PAD | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | PAD SPACE | | utf8mb4_is_0900_ai_ci | utf8mb4 | 257 | | Yes | 0 | NO PAD | | utf8mb4_is_0900_as_cs | utf8mb4 | 280 | | Yes | 0 | NO PAD | | utf8mb4_ja_0900_as_cs | utf8mb4 | 303 | | Yes | 0 | NO PAD | | utf8mb4_ja_0900_as_cs_ks | utf8mb4 | 304 | | Yes | 24 | NO PAD | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | PAD SPACE | | utf8mb4_la_0900_ai_ci | utf8mb4 | 271 | | Yes | 0 | NO PAD | | utf8mb4_la_0900_as_cs | utf8mb4 | 294 | | Yes | 0 | NO PAD | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | PAD SPACE | | utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 0 | NO PAD | | utf8mb4_lt_0900_as_cs | utf8mb4 | 291 | | Yes | 0 | NO PAD | | utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 0 | NO PAD | | utf8mb4_lv_0900_as_cs | utf8mb4 | 281 | | Yes | 0 | NO PAD | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | PAD SPACE | | utf8mb4_pl_0900_ai_ci | utf8mb4 | 261 | | Yes | 0 | NO PAD | | utf8mb4_pl_0900_as_cs | utf8mb4 | 284 | | Yes | 0 | NO PAD | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | PAD SPACE | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | PAD SPACE | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | PAD SPACE | | utf8mb4_ro_0900_ai_ci | utf8mb4 | 259 | | Yes | 0 | NO PAD | | utf8mb4_ro_0900_as_cs | utf8mb4 | 282 | | Yes | 0 | NO PAD | | utf8mb4_ru_0900_ai_ci | utf8mb4 | 306 | | Yes | 0 | NO PAD | | utf8mb4_ru_0900_as_cs | utf8mb4 | 307 | | Yes | 0 | NO PAD | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | PAD SPACE | | utf8mb4_sk_0900_ai_ci | utf8mb4 | 269 | | Yes | 0 | NO PAD | | utf8mb4_sk_0900_as_cs | utf8mb4 | 292 | | Yes | 0 | NO PAD | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | PAD SPACE | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | PAD SPACE | | utf8mb4_sl_0900_ai_ci | utf8mb4 | 260 | | Yes | 0 | NO PAD | | utf8mb4_sl_0900_as_cs | utf8mb4 | 283 | | Yes | 0 | NO PAD | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | PAD SPACE | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | PAD SPACE | | utf8mb4_sv_0900_ai_ci | utf8mb4 | 264 | | Yes | 0 | NO PAD | | utf8mb4_sv_0900_as_cs | utf8mb4 | 287 | | Yes | 0 | NO PAD | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | PAD SPACE | | utf8mb4_tr_0900_ai_ci | utf8mb4 | 265 | | Yes | 0 | NO PAD | | utf8mb4_tr_0900_as_cs | utf8mb4 | 288 | | Yes | 0 | NO PAD | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | PAD SPACE | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | PAD SPACE | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | PAD SPACE | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | PAD SPACE | | utf8mb4_vi_0900_ai_ci | utf8mb4 | 277 | | Yes | 0 | NO PAD | | utf8mb4_vi_0900_as_cs | utf8mb4 | 300 | | Yes | 0 | NO PAD | | utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD | +----------------------------+---------+-----+---------+----------+---------+---------------+ 75 rows in set (0.02 sec)
MySQL排序规则命名格式举例说明：utf8mb4_unicode_520_ci
- 字符集名称(utf8mb4)
- 语言标识符(unicode)
- 统一字符编码标准版本(520)
- 大小写敏感性，ci表示大小写不敏感
排序规则后缀命名约定:：
如何选择MySQL中的字符集和排序规则
如何选择字符集？ UNICODE是一个世界级的字符集，保证了全世界的每种语言字符都有唯一的编号，从而可以在国际范围内交换文本数据不受以上问题困扰。UTF-8是Unicode的编码实现方式之一，UTF-8最大的一个特点，就是它是一种变长编码方式。它可以使用1~4个字节表示一个字符，根据不同的字符而变化字节长度。
MySQL字符集中UTF8和UTF8MB4如何选？ MySQL的utf8是一种早期采用旧版的UTF-8标准(RFC 2279)的字符集，它能够编码的Unicode字符并不多。只支持每个字符最多三个字节，而现在使用的真正UTF-8标准(RFC 3629)是每个字符最多四个字节。所以说MySQL的utf8mb4才是真正意义上通用的UTF-8。
如何选择排序规则？
utf8mb4_unicode_ci：基于UCA4.0.0版本的UNICODE排序规则算法。 utf8mb4_unicode_520_ci：基于UCA5.2.0版本的UNICODE排序规则算法。版本更新，排序更精准。相应的因为Unicode排序规则元素表(DUCET)更大，速度相对更慢一些。 UCA（UNICODE COLLATION ALGORITHM）UNICODE排序规则算法。
设定MySQL字符集和排序规则
MySQL的四层字符集和排序规则：如果本层未设置字符集和排序规则，默认用上一层的作为自己的字符集和排序规则。
- 服务器
- 数据库
- 表
- 列
1.设置MySQL服务器字符集和排序规则:
[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character_set_server = utf8mb4 collation_server = utf8mb4_unicode_ci
2.设置数据库字符集和排序规则:
CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 排序规则名称] ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 排序规则名称] 注：数据库字符集和排序规则选项都存储数据库目录下db.opt的文本文件中。
MySQL选择数据库字符集和排序规则的原则:
- 如果同时指定了CHARACTER SET和COLLATE，则使用指定的字符集和排序规则。
- 如果只指定了CHARACTER SET，则使用指定的字符集和其默认排序规则。
- 如果只指定了COLLATE，则使用和collation相关联的字符集。
- 既未指定CHARACTER SET也未指定COLLATE，将使用服务器字符集和排序规则。
3.设置表字符集和排序规则:
CREATE TABLE 表名称 (column_list) [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 排序规则名称]] ALTER TABLE 表名称 [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 排序规则名称]
MySQL选择表字符集和排序规则的原则:
- 如果同时指定了CHARACTER SET和COLLATE，则使用指定的字符集和排序规则。
- 如果只指定了CHARACTER SET，则使用指定的字符集和其默认排序规则。
- 如果只指定了COLLATE，则使用和collation相关联的字符集。
- 既未指定CHARACTER SET也未指定COLLATE，将使用数据库字符集和排序规则。
4.设置列字符集和排序规则:
每个“字符”列(即CHAR、 VARCHAR、 TEXT类型或任何同义词的列)都有一个列字符集和排序规则。
CREATE TABLE和ALTER TABLE的列定义语法用于指定列字符集和排序规则的可选子句: `列名称` {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET 字符集名称] [COLLATE 排序规则名称]
也可用于ENUM和SET列: `列名称` {ENUM|SET} (val_list) [CHARACTER SET 字符集名称] [COLLATE 排序规则名称]
MySQL选择列字符集和排序规则的原则：
- 如果同时指定了CHARACTER SET和COLLATE，则使用指定的字符集和排序规则。
- 如果只指定了CHARACTER SET，则使用指定的字符集和其默认排序规则。
- 如果只指定了COLLATE，则使用和collation相关联的字符集。
- 既未指定CHARACTER SET也未指定COLLATE，将使用表字符集和排序规则。
5.设置字符串的字符集和排序规则:
[_字符集名称] 'string' [COLLATE 排序规则名称] “_字符集名称”表达式的正式名称是字符集导引符。它告诉解析器，后面的字符串使用“_字符集名称”指定的字符集。导引符不会像CONVERT()函数那样将字符串更改为导引符的字符集。也不会更改字符串值，尽管可能会进行填充。导引符只是一个辅助识别字符串字符集的标识。
MySQL如何确定字符串的字符集和排序规则:
- 如果同时指定了字符集导引符_charset_name和COLLATE，则使用指定的字符集导引符字符集和排序规则。
- 如果只指定了字符集导引符_charset_name，则使用指定的字符集导引符字符集和其默认排序规则。
- 如果只指定了COLLATE，则使用和collation相关联的字符集。
- 既未指定字符集导引符_charset_name也未指定COLLATE，将使用character_set_connection和collation_connection系统变量定义的字符集和排序规则。
查看当前MySQL实例各类字符集系统变量:
mysql> show global variables like '%character_set%'; +--------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | /data/app/mysql-8.0.26/share/charsets/ | +--------------------------+----------------------------------------+ 8 rows in set (0.01 sec) mysql>
当前MySQL实例各类字符集系统变量说明:
- character_set_client：客户端使用的字符集
- character_set_connection：连接数据库时使用的字符集，用于在没有字符集导引符的情况下指定的字符串的字符集。
- character_set_database：创建数据库使用的字符集
- character_set_filesystem：MySQL服务器文件系统使用的字符集，默认值为binary，不做任何转换
- character_set_results：数据库给客户端返回数据时使用的字符集
- character_set_server：MySQL服务器使用的字符集
- character_set_system：数据库服务器用于存储元数据（如表和列名字等等）的字符集。该值始终为
- utf8 character_sets_dir：字符集的安装目录
注意：乱码时不需要关心character_set_filesystem、character_set_system和character_sets_dir 这3个系统变量，它们不会影响乱码。
查看当前MySQL实例各类排序规则系统变量:
mysql> show global variables like 'collation_%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_0900_ai_ci | | collation_database | utf8mb4_0900_ai_ci | | collation_server | utf8mb4_0900_ai_ci | +----------------------+--------------------+ 3 rows in set (0.00 sec)
当前MySQL实例排序规则系统变量说明: collation_connection：连接数据库时使用的排序规则，对于比较文字字符串很重要。 collation_database：数据库默认的排序规则。由服务器设置不要手动设置此变量。 collation_server：MySQL服务器默认的排序规则
客户端从发送请求到接收结果过程中发生的字符集转换流程图:
如何保证不乱码？
- 客户端字符集参数（如果未设置用操作系统字符集）
- character_set_client
- character_set_connection
- 列字符集
- character_set_results
以上5个字符集保持一致，即避免转换成本，又保证不会产生乱码。
如何动态统一MySQL服务层的三个字符集系统变量？ 为了减少MySQL服务层无谓的字符集转换。MySQL提供了一条简便的语句：
SET NAMES 字符集名称;
这一条语句产生的效果等同于我们执行下面这3条语句：
- SET character_set_client = 字符集名称;
- SET character_set_connection = 字符集名称;
- SET character_set_results = 字符集名称;
#####################
utf8的锅：
场景 : 之前在给客户做微商城时，需要保存微信的授权信息，此时就有一个nickname字段，在设计数据表时，潜意识的将表的存储格式设置为utf8，生产上线一段时间后偶尔出现保存异常。经分析，出现异常的原因是：用户nickname中有email表情符号。utf8格式的数据表存储不下导致。
经验提示： 在设计数据表时，一定要注意该字段存储的内容，如果允许设置表情emoj，则一定不能使用utf8，而是使用utf8mb4。
alter database apple default character set = 'utf8mb4';
alter table iphone convert to character set utf8mb4;
# 字符集字符序问题： character-set-server/default-character-set：服务器字符集，默认情况下所采用的。
character-set-database：数据库字符集。
character-set-table：数据库表字符集。
# 优先级依次增加。所以一般情况下只需要设置character-set-server，而在创建数据库和表时不特别指定字符集，这样统一采用character-set-server字符集。
character-set-client：客户端的字符集。客户端默认字符集。当客户端向服务器发送请求时，请求以该字符集进行编码。
character-set-results：结果字符集。服务器向客户端返回结果或者信息时，结果以该字符集进行编码。
在客户端，如果没有定义character-set-results，则采用character-set-client字符集作为默认的字符集。所以只需要设置character-set-client字符集。 最佳方案：
server字符集=数据库字符集=表字符集=会话字符集=客户端显示字符集
MySQL在 5.5.3 之后增加了 utf8mb4 字符编码，mb4即 most bytes 4。简单说 utf8mb4 是 utf8 的超集并完全兼容utf8，能够用四个字节存储更多的字符。
而utf8 是 utf8mb3 的别名。标准的 UTF-8 字符集编码是可以用 1~4 个字节去编码21位字符，但是MySQL其实实现的utf8只是使用3个字节而已， utf8mb4才是真正意义上的 utf8。
如果数据库表字段设置的字符集不是utf8mb4，却插入类似emjoy表情的时候：
- 严格模式 下会出现 Incorrect string value: /xF0/xA1/x8B/xBE/xE5/xA2… for column 'name' 这样的错误
- 非严格模式下此后的数据会被截断
排序字符集
Refer to: Collation Naming Conventions
| Suffix | Meaning | Remark |
|---|---|---|
| _ai | Accent insensitive | |
| _as | Accent sensitive | |
| _ci | Case insensitive | 不分区大小写 |
| _cs | case-sensitive | 区分大小写 |
| _bin | Binary | 二进制存储，区分大小写 |
utf8mb4_ unicode_ ci VS utf8mb4_ general_ ci
Refer to: What's the difference between utf8_general_ci and utf8_unicode_ci
- utf8_general_ci校对速度快，但准确度稍差。
- utf8_unicode_ci准确度高，但校对速度稍慢。
数据库一般默认选择 utf8mb4_general_ci ;
如果你的应用有德语、法语或者俄语，请一定使用utf8mb4_unicode_ci
配置
vim /etc/my.cnf[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] # character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
检查目前MySQL的字符集
tj1-using-glc-db01.kscn((none)) > SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +--------------------------+--------------------+ 10 rows in set (0.01 sec) Fri Aug 28 17:51:07 2020
总结
- mysql 版本 5.5.3+
- MySQL Connector/J Java驱动5.1.13+
- /etc/my.cnf 配置中 添加配置，详见上面
- 排序字符集选用 utf8mb4_unicode_ci
- 列（字段）> 表 > 数据库
备注：
其实只要数据库支持utfbmb4(show char set)，及时MySQL配置(/etc/my.cnf)中配置的默认字符集是utf8，也可以直接指定数据库、表、字段的字符集为utf8mb4，然后在连接的时候指定字符集为utf8mb4即可。
比如设置Java的连接参数中characterEncoding=utf8mb4
查看MySQL支持的字符集列表：
tj1-using-glc-db01.kscn((none)) > show char set; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.01 sec) Fri Aug 28 17:50:06 2020
建议
数据库在设置字符集的时候，设置成utf8mb4格式！
字符集转化
###########################################################################
character_set_server collation_servercharacter_set_databasecollation_database
character_set_clientcharacter_set_results
character_set_connection <===character_set_clientcollation_connection
skip-character-set-client-handshake
client (character_set_client) -----> character_set_connection -------> mysqld ------> client(character_set_results)
存在三次编码转换过程：
1）mysql client 使用 character_set_client编码的字符------> character_set_connection 编码字符
------> mysqld ：这里需要从 character_set_connection 编码格式二进制流解码成 字符，然后使用 character_set_server/character_set_database 对字符进行再次编码，生成二进制流，存储时，就是存储再次编码的二进制流数据。
2）读取数据时，会使用 character_set_server/character_set_database 对读取到的二级制流进行 解码成 字符，然后使用 character_set_results 对字符进行二次编码，生成二进制流，发给 mysql client.
所以 使用 set names 'xxx' 命令，结合 character_set_server 参数，可以将 整个过程的 字符集设置成相同的，就不会存在编码转换的过程。
1.-
-
系统变量：
character_set_serverandcollation_server指示 server character set and collation -
系统变量：
character_set_databaseandcollation_database指示默认数据库的 character set and collation
2.-
在处理客户端和服务器之间的连接的流量时涉及附加的字符集和校对系统变量。
每个客户端都有特定于会话的连接相关字符集和校对系统变量。
这些会话系统变量值在连接时初始化，但可以在会话中更改。
- 服务器将
character_set_client系统变量作为客户端发送语句的字符集。 -
该
character_set_client系统变量不能设置为特定字符集：ucs2 utf16 utf16le utf32
3.-
- 服务器收到语句后应该将语句翻译成什么字符集？
要确定这一点，服务器使用 character_set_connection 和collation_connection 系统变量：
-
服务器将客户端发送的语句转换
character_set_client为character_set_connection。 -
collation_connection对于文字字符串的比较很重要。对于字符串与列值的比较，collation_connection无关紧要，因为列具有自己的排序规则，排序优先级更高。
4.-
- 在将查询结果发送回客户端之前，服务器应该将哪些字符集转换为结果？
character_set_results 系统变量指示在其中服务器查询结果返回到客户端的字符集。这包括结果数据，如列值，结果元数据（如列名称）和错误消息。
要告诉服务器不执行结果集或错误消息的转换，请设置 character_set_results为 NULL或binary：
5.-
- 要查看适用于当前会话的字符集和排序规则系统变量的值，请使用以下语句：
SELECT * FROM performance_schema.session_variables WHERE VARIABLE_NAME IN ( 'character_set_client', 'character_set_connection', 'character_set_results', 'collation_connection' ) ORDER BY VARIABLE_NAME;
6.-
-
C应用程序可以
mysql_options()在连接到服务器之前通过以下方式调用，根据操作系统设置使用字符集自动检测 ：mysql_options(mysql, MYSQL_SET_CHARSET_NAME, MYSQL_AUTODETECT_CHARSET_NAME); -
每个客户端都支持一个
--default-character-set选项，该选项允许用户显式指定字符集以覆盖客户端否则确定的任何默认值。
7.-
- 使用mysql客户端，要使用与默认值不同的字符集，
SET NAMES每次连接到服务器时都可以显式执行 语句（请参阅客户端程序连接字符集配置）。要更轻松地完成相同的结果，请在选项文件中指定字符集。例如，以下选项文件设置更改koi8r每次调用mysql时设置的三个与连接相关的字符集系统变量：
[mysql] default-character-set=koi8r
8.-
mysql> charset koi8r
Charset changed 该charset命令发出一个 SET NAMES语句，并且还更改mysql 在连接断开后重新连接时使用的默认字符集。
配置客户端程序时，还必须考虑它们执行的环境。请参见 第10.5节“配置应用程序字符集和排序规则”。
9.-
-
SET NAMES '一个 语句相当于这三个语句：charset_name'
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;
10.-
- SET CHARACTER SET '
charset_name' 一个语句相当于这三个语句：SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET collation_connection = @@collation_database;
11.-
-
utf8mb4MySQL 5.7和8.0之间 的默认排序规则不同（utf8mb4_general_ci对于5.7，utf8mb4_0900_ai_ci对于8.0）。 -
当8.0客户端请求一个字符集时
utf8mb4，它发送给服务器的是默认的8.0utf8mb4排序规则; 也就是说utf8mb4_0900_ai_ci。 -
utf8mb4_0900_ai_ci仅在MySQL 8.0中实现，因此5.7服务器无法识别它。 -
由于5.7服务器无法识别
utf8mb4_0900_ai_ci，因此无法满足客户端字符集请求，并回退到其默认字符集和排序规则（latin1和latin1_swedish_ci）。
在这种情况下，客户端仍然可以在连接后utf8mb4通过发出SET NAMES 'utf8mb4'语句来使用 。得到的排序规则是5.7默认utf8mb4 排序规则; 就是这样utf8mb4_general_ci。如果客户端另外需要进行整理utf8mb4_0900_ai_ci，则无法实现该目标，因为服务器无法识别该整理。客户端必须愿意使用不同的 utf8mb4排序规则，或者从MySQL 8.0或更高版本连接到服务器。
12.-
在MySQL 4.0版中， 服务器和客户端都有一个“ 全局 ”字符集，并决定服务器管理员使用哪个字符。这从MySQL 4.1版开始改变。现在发生的是“ 握手 ”，如 第10.4节“连接字符集和排序规则”中所述：
当客户端连接时，它会向服务器发送它要使用的字符集的名称。服务器使用的名称，设置
character_set_client，character_set_results和character_set_connection系统变量。实际上，服务器SET NAMES使用字符集名称执行 操作。
这样做的效果是你无法控制的客户端字符由开始设定的mysqld用 --character-set-server=utf8。但是，一些亚洲客户更喜欢MySQL 4.0的行为。为了能够保留这种行为，我们添加了一个 mysqld开关， --character-set-client-handshake可以关闭它 --skip-character-set-client-handshake。如果启动mysqld的使用 --skip-character-set-client-handshake，那么，当客户端连接时，它发送的字符集的名称，它希望使用的服务器。但是，服务器会忽略来自客户端的此请求。
举例来说，假设您最喜欢的服务器字符集是latin1（不太可能在CJK区域，但这是默认值）。进一步假设客户端使用， utf8因为这是客户端操作系统支持的内容。现在，使用latin1默认字符集启动服务器 ：
mysqld --character-set-server=latin1然后使用默认字符集启动客户端 utf8mb4：
mysql --default-character-set=utf8mb4通过查看以下输出可以看到生成的设置 SHOW VARIABLES：
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------