引言
utf8编码可能是2个字节,3个字节,4个字节的字符,但是mysql的utf8编码只支持3个字节的数据
,而app端emoji表情是4个字节的字符,如果直接更新到库会报如下异常:
### Cause: java.sql.SQLException:
Incorrect string value: '\xF0\x9F\x90\xB0' for column 'name' at row 1
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366];
Incorrect string value: '\xF0\x9F\x90\xB0' for column 'name' at row 1;
nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xB0' for column 'name' at row 1
utf8mb4是utf8的超集,兼容utf8,并且能存储4字节的表情字符。utf8mb4是utf8的超集,兼容utf8,并且能存储4字节的表情字符。
更新数据库编码utf8mb4
1 mysql版本
utf8mb4 的最低 MySQL 版本支持版本为 5.5.3+,若不是,请升级到较新版本。
2. MySQL驱动
5.1.34 可用,最低不能低于 5.1.13
3. 修改MySQL配置文件
修改 MySQL 配置文件 /etc/my.cnf(windows为my.ini)
找到后请在以下三部分里添加如下内容:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
4. 重启数据库,
systemctl restart mysql
或
/etc/init.d/mysql restart
检查变量
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8
character_sets_dir /usr/local/mysql/share/charsets/
collation_connection utf8mb4_general_ci
collation_database utf8mb4_unicode_ci
collation_server utf8_general_ci
6 将数据库和已经建好的表也转换成utf8mb4
更改数据库编码:
ALTER DATABASE database1 CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`;
更改表编码:
ALTER TABLE `TABLE_NAME` CONVERT TO CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`;
如有必要,还可以更改列的编码;
至此结束;如有疑问,或者错误的地方欢迎指正,在这里抛砖引玉了,感谢博主给予的启发https://www.jianshu.com/p/f7d7609de6b0