Conversion from collation utf8_general_ci into utf8mb4_unicode_ci impossible for parameter

 mysql 的联接方案也要改成 utf8mb4

 

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 bytes
ERROR 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_client
character_set_connection
character_set_results
三个值为utf8mb4.
总结
不得不说，mysql这个3个byte的utf8是个巨坑，没有按照国际的标准来设计，不过之后肯定会改成utf8mb4为默认字符集。

　　

 

 

 

 

 

 

参考了：https://developer.aliyun.com/article/674741

https://blog.csdn.net/galoiszhou/article/details/118359174