MySQL修改有存量数据的数据库字符集实战

原创巩飞 2020-03-18

4237

我们已经知道数据库或者表，可以在创建后再修改字符集。但是修改字符集不会将已有的数据按新字符集重新进行处理。那么已有存量数据的数据库改如何调整字符集呢？可以使用命令进行转换，也可以像早期先将数据导出，调整字符集，再将数据导入。

我们假设有个业务，在英语国家诞生，当时其研发团队创建一个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
图片.png

当表数据静止时，导出所有记录。

[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
图片.png

用新字符集创建新数据库

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)
复制

特别注意：
选择目标字符集的时候，最好是源字符集的超集，否则目标字符集中不支持的字符会变成乱码。

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

MySQL循序渐进（共42篇）

从搬砖到开车。

MySQL修改有存量数据的数据库字符集实战

文章被以下合辑收录

评论