mysql 的联接方案也要改成 utf8mb4
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | For each database:ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;For each table:ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;For each column:ALTER TABLE table_name CHANGE column_name column_name data_type CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;utf8是utf8mb4的子集，所以直接转换理论上不会有问题。当然也可以使用dump转换编码。批量生成脚本：use information_schema;SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql FROM `TABLES` where table_schema like "yourDbName" group by table_schema;SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `TABLES` where table_schema like "yourDbName" group by table_schema, table_name;SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('varchar');SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('text','tinytext','mediumtext','longtext');ERROR 1071 (42000) 问题解决出现这种报错主要有两种情况：ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytesERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes一个是length 大于3072 bytes，一个是大于1000 bytes。mysql5.7中支持index key最大的长度是 767 bytes，在开启了innodb_large_prefix这个参数之后，max len 限制是3072 bytes。在5.7之前这个参数没有默认开启，5.7之后默认是开启的。8.0之后去掉了这个参数，默认就支持3072个字节。所以在转换字符集过程中，如果一个列上有索引，由于之前的utf8的编码是3个bytes，utf8mb4是4个bytes。转换之后key的值可能会超过767或则3072，这个时候就是出现类似的报错。如果是MyISAM的引擎，是直接不能超过1000 bytes这个限制的。这个时候的解决办法是如果是MyISAM的引擎，改成innodb引擎。如果改成innodb还不行，只能缩小字段的大小。常用命令set names utf8mb4;相当于设置character_set_clientcharacter_set_connectioncharacter_set_results三个值为utf8mb4.总结不得不说，mysql这个3个byte的utf8是个巨坑，没有按照国际的标准来设计，不过之后肯定会改成utf8mb4为默认字符集。 |
参考了：https://developer.aliyun.com/article/674741
https://blog.csdn.net/galoiszhou/article/details/118359174
