MySQL utf8mb4字符集:支持emoji表情符号

Share on:

最近参与的app项目中,ios开发人员反映上传ios自带emoji表情字符,api会出现异常。

上网查询下才发现mysql要用utf8mb4,不能用utf8。

涉及无线相关的 MySQL 数据库建议都提前采用 utf8mb4 字符集。

utf8mb4和utf8到底有什么区别呢?原来以往的mysql的utf8一个字符最多3字节,而utf8mb4则扩展到一个字符最多能有4字节,所以能支持更多的字符集。

首先先查看下mysql当前版本所支持的字符集

show charset;

阿里云的RDS是支持utf8mb4的,这样就不用升级了,网上说5.5.3以上才支持,自己没有核实过。

1.修改database,table,column字符集mysql语句如下

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)

2.修改my.cnf

[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'

3.重启mysql,检查字符集

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_unicode_ci |  
    | collation_server         | utf8mb4_unicode_ci |  
    +--------------------------+--------------------+  
     rows in set (0.00 sec)

外文资料:https://mathiasbynens.be/notes/mysql-utf8mb4

闽ICP备12003472号-7