MySQL修改字符集为utf8mb4以支持 emoji 表情符号

系统接入微信公众号,需要存储用户昵称,但是发现,有些用户的昵称存储报错;
查询之后,发现用户昵称中包含emoji表情,而数据库的编码为utf8;
因此需要对相关的数据库、表、字段等的编码进行修改,以达到保存emoji表情的目的。

要存储 emoji 表情,需要客户端、到 RDS MySQL 实例的连接、RDS 实例内部 3 个方面统一使用或者支持 utf8mb4 字符集。

注:关于 utf8mb4 字符集,请参考 MySQL 官方文档(http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8mb4.html)
客户端需要保证输出的字符串的字符集为 utf8mb4。
以常见的 JDBC 连接为例:
对于 JDBC 连接,需要使用 MySQL Connector/J 5.1.13(含)以上的版本。
JDBC 的连接串中,建议不配置 characterEncoding 选项。

注:关于 MySQL Connector/J 5.1.13,请参考 MySQL 官方 Release Notes(http://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-13.html)
1、MySQL支持 emoji 表情的最低版本为5.5.3;
2、设置MySQL的字符集为utf8mb4;
【扩展】utf8mb4和utf8到底有什么区别呢?MySQL的utf8一个字符最多3字节,而utf8mb4则扩展到一个字符最多能有4字节,所以能支持更多的字符集。

(一)从应用程序层面解决:
a、修改 my.cnf或者mysql.ini

[client]
  default-character-set = utf8mb4
[mysql]
  default-character-set = utf8mb4
[mysqld]
  character-set-server = utf8mb4
  collation-server = utf8mb4_unicode_ci

b、设置数据库全局参数、数据库、表、字段等字符集:
Step 1. 设置MySQL全局参数为utf8mb4

character_set_server=utf8mb4

Step 2. 设置库的字符集为 utf8mb4

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Step 3. 设置表的字符集为 utf8mb4

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Step 4. 设置字段的字符集为 utf8mb4

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

c、如果是Java程序,mysql-connection.jar版本高于5.1.13,否则仍然不能试用utf8mb4;

d、重启 MySQL Server、检查字符集;

查看服务器字符集设置

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
Variable_nameValue
character_set_clientutf8mb4
character_set_connectionutf8mb4
character_set_databaseutf8mb4
character_set_filesystembinary
character_set_resultsutf8mb4
character_set_serverutf8mb4
character_set_systemutf8
collation_connectionutf8mb4_unicode_ci
collation_databaseutf8mb4_unicode_ci
collation_serverutf8mb4_unicode_ci

查看数据库字符集

mysql> select * from SCHEMATA;
mysql> SHOW VARIABLES WHERE variable_name LIKE 'char%' OR variable_name LIKE 'collation%';

查看表字符集

mysql> select TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION from information_schema.TABLES;

查看列字符集

mysql> select * from information_schema.COLUMNS;

(二)从应用程序层面解决:
1、在获得数据之后往数据库存之前先进行编码:URLEncoder.encode(nickName, "utf-8");
2、当从数据库中取出准备显示的时候进行解码:URLDecoder.decode(nickname, "utf-8");

(三)另外,字符集改动之后,还需要处理 MySQL index 767 个字节的限制问题。

执行SQL时,有时会报错:ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

报错原因在于:

MySQL Innodb 的索引长度限制为767字节,

utf8编码最多占3个字节,对于varchar(255)这样的数据库结构,所以255*3=765,没有超出767的限制。

而对于utf8mb4编码,一个字最多可以占用4个字节,那255*4就会超出767的字符限制了。

Last modification:January 10th, 2018 at 07:34 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment