MySQL插入emoji表情,出现错误 1 java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' at row
可以对4字节的字符进行编码存储,然后取出来的时候,再进行解码。但是这样做会使得任何使用该字符的地方都要进行编码与解码。
utf8mb4编码是utf8编码的超集,兼容utf8,并且能存储4字节的表情字符。 采用utf8mb4编码的好处是:存储与获取数据的时候,不用再考虑表情字符的编码与解码问题。
MySQL的版本 utf8mb4的最低mysql版本支持版本为5.5.3+,若不是,请升级到较新版本。
MySQL驱动 5.1.34可用,最低不能低于5.1.13
修改MySQL配置文件 修改mysql配置文件my.cnf(Windows为my.ini)my.cnf
一般在 /etc/mysql/my.cnf
位置
部分Linux系统的 my.cnf
文件打开后是这样的
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/
那么这个配置文件的路径应该改成文件内容下面的地址
1 /etc/mysql/conf.d/mysql.cnf
找到后请在以下三部分里添加或修改如下内容
1 2 3 4 5 6 7 8 9 10 11 [client] default-character-set=utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' [mysql] default-character-set=utf8mb4
重启数据库,检查变量 Windows终端指令
1 2 net stop mysql57 net start mysql57
Linux终端指令
1 2 3 service mysql stop service mysql start service mysql status
数据库SQL语句
1 SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
修改前输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | gbk_chinese_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+
修改后输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 +--------------------------+--------------------+ | 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 | utf8 | | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +--------------------------+--------------------+