MySQL默认的字符编码为utf8，修改为utf8mb4后可支持emoji表情。
utf8mb4解决了MySQL数据库存储emoji表情的问题。utf8mb4是utf8的超集，理论上由utf8升级到utf8mb4字符编码没有任何兼容问题。
升级utf8到utf8mb4前先备份
1、备份所有需要升级的数据库，如果是虚机最好做快照。
2、查看mysql版本是否高于5.5.3。
3、查看当前系统默认的字符集设置
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';+--------------------------+-----------------+
| 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 |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+--------------------------+-----------------+
10 rows in set (0.01sec)
mysql>
4、查看database的字符编码
#polarsnow 为库名；
mysql>show create database polarsnow;+-----------+--------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------+
| polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8*/ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00sec)
mysql>
5、查看table的字符编码
mysql> show create table ps;+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| ps | CREATE TABLE `ps` (
`name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
6、查看column的字符编码
mysql> show full columns from ps;+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)
修改：
修改database默认的字符集：
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
mysql> ALTER DATABASE polarsnow CHARACTER SET = utf8mb4 COLLATE =utf8mb4_unicode_ci;
Query OK,1 row affected (0.03sec)
mysql>show create database polarsnow;+-----------+--------------------------------------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------------------------------------+
| polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci*/ |
+-----------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql>show tables;+---------------------+
| Tables_in_polarsnow |
+---------------------+
| ps |
+---------------------+
1 row in set (0.00sec)
mysql> show create table ps;+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| ps | CREATE TABLE `ps` (
`name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql> show full columns from ps;+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00sec)
mysql> create table test_tb2 (tb2 varchar(100) );
Query OK,0 rows affected (0.21sec)
mysql>show tables;+---------------------+
| Tables_in_polarsnow |
+---------------------+
| ps |
| test_tb2 |
+---------------------+
2 rows in set (0.00sec)
mysql>show create table test_tb2;+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_tb2 |CREATE TABLE `test_tb2` (
`tb2` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql>
修改table的字符集
1、只修改表默认的字符集：
ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
2、修改表默认的字符集和所有字符列的字符集：
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> show create table ps;+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| ps | CREATE TABLE `ps` (
`name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql> show full columns from ps;+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00sec)
mysql> ALTER TABLE psCONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK,0 rows affected (0.38sec)
Records:0 Duplicates: 0 Warnings: 0mysql> show create table ps;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| ps | CREATE TABLE `ps` (
`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql> show full columns from ps;+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(100) | utf8mb4_unicode_ci | YES | | NULL | | select,insert,update,references | |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00sec)
mysql>
3、修改column默认的字符集
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
检查字段的最大长度和索引列
字段长度
由于从utf8升级到了utf8mb4，一个字符所占用的空间也由3个字节增长到4个字节，但是我们当初创建表时，设置的字段类型以及最大的长度没有改变。例如，你在utf8下设置某一字段的类型为TINYTEXT, 这中字段类型最大可以容纳255字节，三个字节一个字符的情况下可以容纳85个字符，四个字节一个字符的情况下只能容纳63个字符，如果原表中的这个字段的值有一个或多个超过了63个字符，那么转换成utf8mb4字符编码时将转换失败，你必须先将TINYTEXT更改为TEXT等更高容量的类型之后才能继续转换字符编码
索引
在InnoDB引擎中，最大的索引长度为767字节，三个字节一个字符的情况下，索引列的字符长度最大可以达到255，四个字节一个字符的情况下，索引的字符长度最大只能到191。如果你已经存在的表中的索引列的类型为VARCHAR(255)那么转换utf8mb4时同样会转换失败。你需要先将VARCHAR(255)更改为VARCHAR(191)才能继续转换字符编码
修改配置文件
修改 vim /etc/my.cnf
# 对本地的mysql客户端的配置
[client]
default-character-set =utf8mb4
# 对其他远程连接的mysql客户端的配置
[mysql]
default-character-set =utf8mb4
# 本地mysql服务的配置
[mysqld]
character-set-client-handshake =FALSE
character-set-server =utf8mb4
collation-server =utf8mb4_unicode_ci> service mysqld restart
View Code
检查修改：
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 |
+--------------------------+--------------------+
10 rows in set (0.00 sec)
View Code
修复&优化所有数据表
> mysqlcheck -u root -p --auto-repair --optimize --all-databases
参考文档：
https://docs.lvrui.io/2016/08/21/%E4%BF%AE%E6%94%B9MySQL%E7%9A%84%E5%AD%97%E7%AC%A6%E9%9B%86%E4%B8%BAutf8mb4/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
