MySQL 5.5將字元集從utf8升級為utf8mb4

MySQL 5.5將字元集從utf8升級為utf8mb4
升級原因

MySQL預設的utf8隻支援三位元組字元，不支援EmojiEmoji（四位元組），如果有四位元組的字元寫入會報錯。從MySQL 5.5開始，提供了utf8mb4，支援四位元組的字元。
許多使用iphone的使用者來在填寫暱稱的時候會加入EmojiEmoji，如果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 = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_general_ciinit_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)

本文永久更新連結地址：