MySQL 5.5将字符集从utf8升级为utf8mb4

MySQL默认的utf8只支持三字节字符，不支持Emoji表情符（四字节），如果有四字节的字符写入会报错。从MySQL 5.5开始，提供了utf8mb4，支持四字节的字符。
许多使用iphone的用户来在填写昵称的时候会加入Emoji表情符，如果MySQL没有使用utf8mb4字符集，这样的昵称信息就无法写入。

一、升级前。 mysql> SHOW VARIABLES LIKE 'character_set%'; +--------------------------+--------------------------------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /home/sdkserver/local/mysql-5.5.43-linux2.6-x86_64/share/charsets/ | +--------------------------+--------------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) 二、修改配置文件。

打开MySQL的配置文件，将字符集的配置修改成如下：

[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_general_ci init_connect='SET NAMES utf8mb4' 三、升级后。 mysql> SHOW VARIABLES LIKE 'character_set%'; +--------------------------+--------------------------------------------------------------------+ | 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 | | character_sets_dir | /home/nieyong/local/mysql-5.5.43/share/charsets/ | +--------------------------+--------------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'collation%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +----------------------+--------------------+ 3 rows in set (0.00 sec)

本文永久更新链接地址：