show VARIABLES like '%char%';
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
因为“character_set_server utf8”会导致不能存储表情字条,需要修改mysql配置文件。
mysql安装在docker
docker exec -it aeaca0e14f45 bash
vim /etc/mysql/mysql.conf.d/mysqld.cnf 或者 echo "character-set-server=utf8mb4" >> /etc/mysql/mysql.conf.d/mysqld.cnf
exit
退出mysql容器,重启mysql
docker restart aeaca0e14f45
修改数据库字符集
ALTER DATABASE 数据库名称 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
自动生成表修改字符集SQL
ALTER DATABASE mc_order DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SELECT
CONCAT(
'ALTER TABLE ',
TABLE_NAME,
' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
)
FROM
information_schema.`TABLES`
WHERE
TABLE_SCHEMA = '数据库名称';
执行上面生的SQL。
注意表里的text,可能会被修改为mediumtext,如果和mybatis不能映射注意统一。