MySQL 字符集错误解决:err=Error 3988: Conversion from collation utf8mb4_unicode_ci into utf8_general_ci impo

某个字段内容包含了表情, 插入 MySQL 时提示错误:

err=Error 3988: Conversion from collation utf8mb4_unicode_ci into utf8_general_ci impossible for parameter

解决:

1. 修改 MySQL 字符集

我使用的是docker

1. 修改 mysql.cnf 映射到 docker 里的 /etc/mysql/mysql.cnf:

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

2. 启动命令参数添加:

 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

重启容器后, 进入mysql 查看:

mysql> 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     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
| collation_connection     | utf8mb4_unicode_ci             |
| collation_database       | utf8mb4_unicode_ci             |
| collation_server         | utf8mb4_unicode_ci             |
+--------------------------+--------------------------------+
11 rows in set (0.17 sec)

2. 修改 table 的字符集

1. 修改具体的 table 的字符集:

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. 修改具体的字段的字符集:

CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci