MySQL修改字符集为utf8mb4

2017-06-09 | 阅读：次

前言

utf8mb4是MySQL 5.5.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   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+
10 rows in set (0.04 sec)

查看某个 database 的字符编码

mysql> show create database video_search;
+--------------+-------------------------------------------------------------------------+
| Database     | Create Database                                                         |
+--------------+-------------------------------------------------------------------------+
| video_search | CREATE DATABASE `video_search` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+--------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看某个 table 的字符编码

mysql> show create table module_info;
+-------------+--------------------------------------------------------------------+
| Table       | Create Table                                                       |
+-------------+--------------------------------------------------------------------+
| module_info | CREATE TABLE `module_info` (
                  `module_id` int(32) NOT NULL AUTO_INCREMENT COMMENT '模块id',
                  `module_name` varchar(50) DEFAULT NULL COMMENT '模块名称',
                  `description` varchar(500) DEFAULT NULL COMMENT '模块描述',
                  PRIMARY KEY (`module_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='视频搜模块列表'       |
+-------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

查看 columns 的字符编码

mysql> show full columns from module_info;
+-------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+--------------+
| Field       | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment      |
+-------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+--------------+
| module_id   | int(32)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references | 模块id       |
| module_name | varchar(50)  | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references | 模块名称     |
| description | varchar(500) | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references | 模块描述     |
+-------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+--------------+
3 rows in set (0.00 sec)

修改字符集

修改 database 默认的字符集

mysql> ALTER DATABASE video_search CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show create database video_search;
+--------------+-----------------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                                     |
+--------------+-----------------------------------------------------------------------------------------------------+
| video_search | CREATE DATABASE `video_search` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+--------------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表

修改数据库默认字符集后，对于已经存在的表是不会生效的，还需要手动修改表的字符集

只修改表的默认字符集

mysql> ALTER TABLE module_info DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table module_info;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                               |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| module_info | CREATE TABLE `module_info` (
                  `module_id` int(32) NOT NULL AUTO_INCREMENT COMMENT '模块id',
                  `module_name` varchar(50) CHARACTER SET latin1 DEFAULT NULL COMMENT '模块名称',
                  `description` varchar(500) CHARACTER SET latin1 DEFAULT NULL COMMENT '模块描述',
                  PRIMARY KEY (`module_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='视频搜模块列表'                  |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表默认的字符集和所有字符列的字符集

mysql> ALTER TABLE module_info CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改指定 column 的字符集

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