本人在用mysql保存emoji表情数据的时候遇上了如下报错:
mysql 1366: Incorrect string value: '\\xF0\\x9F\\xA5\\xB0\\xF0\\x9F for column 'msg_text' at row 1.
这是因为mysql默认的utf8只支持三个字节,但是emoji表情是4字节的,超出了mysql的utf8支持范围,修改的方法就是把charset
都换成utf8mb4。参照https://stackoverflow.com/questions/20411440/incorrect-string-value-xf0-x9f-x8e-xb6-xf0-x9f-mysql 里提到的方法,做如下修改:
SET NAMES utf8mb4; ALTER DATABASE dreams_twitter CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
在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 | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_unicode_ci
看起来是全都改好了,但是当你用代码来执行保存的时候还是会报错,这是为什么呢?
character_set_client ,character_set_connection,character_set_results这三个编码是跟你的客户端连接相关的。比如说,你在mysql的默认终端上设置好了utf8mb4,那只是对你在终端输入的sql语句有效。当你写代码连接数据库的时候,会另起一个mysql connection,这个connection的编码是完全独立的。我的代码用的是第三方封装的一个mysql driver,它里面连接mysql的语句:
fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8")
也就是说当你用这个驱动的代码去连接mysql,connection建立后的默认编码还是utf8。我用代码试了下执行SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%',果然返回的是utf8:
character_set_client:utf8
character_set_connection:utf8
character_set_database:utf8mb4
character_set_filesystem:binary
character_set_results:utf8
character_set_server:utf8mb4
character_set_system:utf8
collation_connection:utf8_general_ci
collation_database:utf8mb4_general_ci
collation_server:utf8mb4_unicode_ci
所以把驱动里的默认utf8改成utf8mb4,就可以解决这个问题