现象
schema、table、colum都设置了utf8mb4,但是程序往数据库写入的数据为乱码。
通过下面sql查看数据库的字符集设置:
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
变量名 | 值 |
---|---|
character_set_client | utf8mb4 |
character_set_connection | utf8mb4 |
character_set_database | utf8mb4 |
character_set_filesystem | binary |
character_set_results | utf8mb4 |
character_set_server | latin1 |
character_set_system | utf8 |
collation_connection | utf8mb4_general_ci |
collation_database | utf8mb4_bin |
collation_server | latin1_swedish_ci |
可见character_set_server和collation_server都是latin1。
尝试修改mysql服务的字符集
- 修改自己的配置文件1
添加:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
修改:
[mysqld]
#character-set-server= latin1
character-set-server=utf8mb4
- 重启mysql服务
service mysql restart
Starting MySQL.The server quit without updating PID file (/var/lib/mysql/spm-testsvr-05.novalocal.pid). [FAILED]
解决启动不了的问题
查看err日志,发现没有错误描述。
直接执行 mysql
mysql
/usr/sbin/mysqld: Character set ‘utf8mb4’ is not a compiled character set and is not specified in the ‘/usr/share/mysql/charsets/Index.xml’ file
vim -R 查看 /usr/share/mysql/charsets/Index.xml 发现确实没有utf8mb4
修改字符集列表
- 参照文章2修改xml
修改后服务可以启动,但是客户端连接时提示:
unknown character set ‘utf8’
- 尝试继续修改xml
先改回原来的配置,执行 SHOW COLLATION;
SHOW COLLATION;
Collation | Charset | Id |
---|---|---|
utf8mb4_general_ci | utf8mb4 | 45 |
utf8mb4_bin | utf8mb4 | 46 |
根据上方的结果,并参照utf8的设置,复制一份,修改为:
<charset name="utf8mb4">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8mb4_general_ci" id="45">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8mb4_bin" id="46">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>
- 启动mysql