MySQL常用排序规则utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin

一、MySQL常用排序规则简介

UTF-8是使用1~4个字节，一种变长的编码格式，字符编码。mb4即 most bytes 4，使用4个字节来表示完整的UTF-8。

mysql的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

总结：MySQL的utf8是utfmb3，只有三个字节，节省空间但不能表达全部的UTF-8。所以推荐使用utf8mb4。

MySQL常用排序规则utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin
ci即case insensitive，不区分大小写。
utf8mb4_unicode_ci
是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。
utf8mb4_general_ci
是一个遗留的 校对规则，不支持扩展，它仅能够在字符之间进行逐个比较。utf8_general_ci校对规则进行的比较速度很快，但是与使用 utf8mb4_unicode_ci的校对规则相比，比较正确性较差。
utf8mb4_bin
将字符串每个字符用二进制数据编译存储，区分大小写，而且可以存二进制的内容。

参考：

Mysql的utf8与utf8mb4区别，utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci区别

记一次排序规则utf8_genera_ci与utf8mb4_bin的区别引发的数据丢失问题

二、修改字符集及排序规则

1.修改表字符集及排序规则

SELECT
	CONCAT( 'ALTER TABLE ', TABLE_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;' ) 
FROM
	information_schema.`TABLES` 
WHERE
	TABLE_SCHEMA = 'cas';

示例SQL

ALTER TABLE `cas`.`user_info` 
DEFAULT CHARACTER SET = utf8mb4, COLLATE = utf8mb4_bin;

2.修改字段字符集及排序规则

SELECT
	CONCAT(
		'ALTER TABLE `',
		table_name,
		'` MODIFY `',
		column_name,
		'` ',
		DATA_TYPE,
		'(',
		CHARACTER_MAXIMUM_LENGTH,
		') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
		( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
		';' 
) 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = 'cas' 
	AND table_name = 'user_info' 
	AND DATA_TYPE = 'varchar' 
	AND ( CHARACTER_SET_NAME != 'utf8mb4' OR COLLATION_NAME != 'utf8mb4_bin' );

注意：以上生成DDL语句的SQL，会导致字段注释和默认值丢失，请自行优化，正确的SQL如下。

示例SQL

ALTER TABLE `cas`.`user_info` 
CHANGE COLUMN `username` `username` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NOT NULL DEFAULT '00' COMMENT '用户名' ;

参考：
MySQL批量修改数据表的字符集及字段字符集