摘要：MySQL修改有存量数据的数据库字符集实战
我们已经知道数据库或者表，可以在创建后再修改字符集。但是修改字符集不会将已有的数据按新字符集重新进行处理。那么已有存量数据的数据库改如何调整字符集呢？可以使用命令进行转换，也可以像早期先将数据导出，调整字符集，再将数据导入。
我们假设有个业务，在英语国家诞生，当时其研发团队创建一个latin1字符集的数据库进行支撑。
root@database-one 13:25: [(none)]> create database DiscountStore default charset latin1; Query OK, 1 row affected (0.01 sec) root@database-one 13:33: [(none)]> use discountstore; Database changed root@database-one 13:33: [discountstore]> create table orders (no int,Buyer varchar(30),Seller varchar(30),InstallationDate datetime) engine=innodb charset latin1; Query OK, 0 rows affected (0.02 sec) root@database-one 13:41: [discountstore]> insert into orders values(666,'Steve','Tom',now()+2); Query OK, 1 row affected (0.00 sec) root@database-one 13:41: [discountstore]> insert into orders values(777,'Jeff','Bill',now()+3); Query OK, 1 row affected (0.00 sec) root@database-one 13:42: [discountstore]> select * from orders; +------+-------+--------+---------------------+ | no | Buyer | Seller | InstallationDate | +------+-------+--------+---------------------+ | 666 | Steve | Tom | 2020-03-18 13:41:26 | | 777 | Jeff | Bill | 2020-03-18 13:42:28 | +------+-------+--------+---------------------+ 2 rows in set (0.00 sec)
业务开展的很好，现在要拓展到中国，系统需要能够支持中文，同时还得考虑继续扩展到其它国家的可能，所以研发团队选utf8做为数据库新的字符集，因为utf8兼容latin1，所以只需修改数据库、表的默认字符集，转换列的字符集即可，不需要其它额外处理。
root@database-one 14:15: [discountstore]> alter database discountstore character set utf8;
Query OK, 1 row affected (0.00 sec)
root@database-one 14:16: [discountstore]> show create database discountstore \G
*************************** 1. row ***************************
Database: discountstore
Create Database: CREATE DATABASE `discountstore` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
root@database-one 14:16: [discountstore]> alter table orders character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@database-one 14:20: [discountstore]> show create table orders \G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`no` int(11) DEFAULT NULL,
`Buyer` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
`Seller` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
`InstallationDate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到，库和表的字符集都变过来了，但是列的字符集还是latin1，对其也进行转换。
root@database-one 14:25: [discountstore]> alter table orders convert to character set utf8;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@database-one 14:28: [discountstore]> show create table orders \G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`no` int(11) DEFAULT NULL,
`Buyer` varchar(30) DEFAULT NULL,
`Seller` varchar(30) DEFAULT NULL,
`InstallationDate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@database-one 14:32: [discountstore]> select * from orders;
+------+-------+--------+---------------------+
| no | Buyer | Seller | InstallationDate |
+------+-------+--------+---------------------+
| 666 | Steve | Tom | 2020-03-18 13:41:26 |
| 777 | Jeff | Bill | 2020-03-18 13:42:28 |
+------+-------+--------+---------------------+
2 rows in set (0.00 sec)
root@database-one 14:32: [discountstore]> insert into orders values(888,'肖杰','郭伟',now()+4);
Query OK, 1 row affected (0.00 sec)
root@database-one 14:32: [discountstore]> select * from orders;
+------+--------+--------+---------------------+
| no | Buyer | Seller | InstallationDate |
+------+--------+--------+---------------------+
| 666 | Steve | Tom | 2020-03-18 13:41:26 |
| 777 | Jeff | Bill | 2020-03-18 13:42:28 |
| 888 | 肖杰 | 郭伟 | 2020-03-18 14:32:40 |
+------+--------+--------+---------------------+
3 rows in set (0.00 sec)
可以看到，转换完毕后，原数据正常，新的中文数据也可以存入了。
在上面的例子中，字符集从latin1转换为utf8，目标字符集是原字符集的超集，完全兼容，所以才能用命令转换。如果反过来，业务收缩，要从utf8将回latin1，就要先进行数据清洗，然后再修改和转换字符集，否则会因存量数据导致转换失败。
root@database-one 14:43: [discountstore]> alter database discountstore character set latin1;
Query OK, 1 row affected (0.00 sec)
root@database-one 14:43: [discountstore]> show create database discountstore \G
*************************** 1. row ***************************
Database: discountstore
Create Database: CREATE DATABASE `discountstore` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
root@database-one 14:44: [discountstore]> alter table orders character set latin1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@database-one 14:44: [discountstore]> show create table orders \G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`no` int(11) DEFAULT NULL,
`Buyer` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`Seller` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`InstallationDate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
root@database-one 14:44: [discountstore]> select * from orders;
+------+--------+--------+---------------------+
| no | Buyer | Seller | InstallationDate |
+------+--------+--------+---------------------+
| 666 | Steve | Tom | 2020-03-18 13:41:26 |
| 777 | Jeff | Bill | 2020-03-18 13:42:28 |
| 888 | 肖杰 | 郭伟 | 2020-03-18 14:32:40 |
+------+--------+--------+---------------------+
3 rows in set (0.00 sec)
root@database-one 14:46: [discountstore]> alter table orders convert to character set latin1;
ERROR 1366 (HY000): Incorrect string value: '\xE8\x82\x96\xE6\x9D\xB0' for column 'Buyer' at row 3
root@database-one 14:46: [discountstore]> delete from orders where no=888;
Query OK, 1 row affected (0.00 sec)
root@database-one 14:47: [discountstore]> alter table orders convert to character set latin1;
Query OK, 2 rows affected (0.24 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@database-one 14:47: [discountstore]> select * from orders;
+------+-------+--------+---------------------+
| no | Buyer | Seller | InstallationDate |
+------+-------+--------+---------------------+
| 666 | Steve | Tom | 2020-03-18 13:41:26 |
| 777 | Jeff | Bill | 2020-03-18 13:42:28 |
+------+-------+--------+---------------------+
2 rows in set (0.00 sec)
root@database-one 14:47: [discountstore]> show create table orders \G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`no` int(11) DEFAULT NULL,
`Buyer` varchar(30) DEFAULT NULL,
`Seller` varchar(30) DEFAULT NULL,
`InstallationDate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
先将数据导出，调整字符集，再将数据导入的方法我们也测试一下。
导出表结构
[root@database-one ~]# mysqldump -uroot -p -h127.0.0.1 -P3306 --default-character-set=utf8 -d discountstore >createscript.sql Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@database-one ~]# ls -ltr createscript* -rw-r--r-- 1 root root 1976 3月 18 15:31 createscript.sql [root@database-one ~]# cat createscript.sql -- MySQL dump 10.13 Distrib 5.7.16, for Linux (x86_64) -- -- Host: 127.0.0.1 Database: discountstore -- ------------------------------------------------------ -- Server version 5.7.16-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; -- -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED='f63ba80d-75f0-11e9-a04a-000c297c4700:1-104782240'; -- -- Table structure for table `orders` -- DROP TABLE IF EXISTS `orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orders` ( `no` int(11) DEFAULT NULL, `Buyer` varchar(30) DEFAULT NULL, `Seller` varchar(30) DEFAULT NULL, `InstallationDate` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-03-18 15:31:42
修改createscript.sql中建表语句字符集为utf8
当表数据静止时，导出所有记录。
[root@database-one ~]# mysqldump -uroot -p -h127.0.0.1 -P3306 --quick --no-create-info --extended-insert --default-character-set=latin1 discountstore >data.sql Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@database-one ~]# ls -lt data.sql -rw-r--r-- 1 root root 1819 3月 18 15:40 data.sql
将data.sql中SET NAMES命令中的latin1修改为utf8
用新字符集创建新数据库
root@database-one 15:46: [(none)]> create database discountstorenew default charset utf8;
Query OK, 1 row affected (0.01 sec)
root@database-one 15:47: [(none)]> show create database discountstorenew \G
*************************** 1. row ***************************
Database: discountstorenew
Create Database: CREATE DATABASE `discountstorenew` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
用createscript.sql创建表
root@database-one 15:48: [(none)]> use discountstorenew;
Database changed
root@database-one 15:48: [discountstorenew]> source /root/createscript.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
root@database-one 15:48: [discountstorenew]> show tables;
+----------------------------+
| Tables_in_discountstorenew |
+----------------------------+
| orders |
+----------------------------+
1 row in set (0.00 sec)
root@database-one 15:49: [discountstorenew]> show create table orders \G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`no` int(11) DEFAULT NULL,
`Buyer` varchar(30) DEFAULT NULL,
`Seller` varchar(30) DEFAULT NULL,
`InstallationDate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@database-one 15:49: [discountstorenew]> select * from orders;
Empty set (0.00 sec)
用data.sql导入数据
root@database-one 15:50: [discountstorenew]> source /root/data.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) root@database-one 15:50: [discountstorenew]> select * from orders; +------+-------+--------+---------------------+ | no | Buyer | Seller | InstallationDate | +------+-------+--------+---------------------+ | 666 | Steve | Tom | 2020-03-18 13:41:26 | | 777 | Jeff | Bill | 2020-03-18 13:42:28 | +------+-------+--------+---------------------+ 2 rows in set (0.00 sec) root@database-one 15:51: [discountstorenew]> insert into orders values(888,'肖杰','郭伟',now()+4); Query OK, 1 row affected (0.00 sec) root@database-one 15:51: [discountstorenew]> select * from orders; +------+--------+--------+---------------------+ | no | Buyer | Seller | InstallationDate | +------+--------+--------+---------------------+ | 666 | Steve | Tom | 2020-03-18 13:41:26 | | 777 | Jeff | Bill | 2020-03-18 13:42:28 | | 888 | 肖杰 | 郭伟 | 2020-03-18 15:51:50 | +------+--------+--------+---------------------+ 3 rows in set (0.00 sec)
特别注意：
选择目标字符集的时候，最好是源字符集的超集，否则目标字符集中不支持的字符会变成乱码。
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。