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

原文链接&＃xff1a;https://www.modb.pro/db/22722?cyn

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

我们假设有个业务&＃xff0c;在英语国家诞生&＃xff0c;当时其研发团队创建一个latin1字符集的数据库进行支撑。

root&＃64;database-one 13:25: [(none)]> create database DiscountStore default charset latin1;
Query OK, 1 row affected (0.01 sec)root&＃64;database-one 13:33: [(none)]> use discountstore;
Database changed
root&＃64;database-one 13:33: [discountstore]> create table orders (no int,Buyer varchar(30),Seller varchar(30),InstallationDate datetime) engine&＃61;innodb charset latin1;
Query OK, 0 rows affected (0.02 sec)root&＃64;database-one 13:41: [discountstore]> insert into orders values(666,&＃39;Steve&＃39;,&＃39;Tom&＃39;,now()&＃43;2);
Query OK, 1 row affected (0.00 sec)root&＃64;database-one 13:41: [discountstore]> insert into orders values(777,&＃39;Jeff&＃39;,&＃39;Bill&＃39;,now()&＃43;3);
Query OK, 1 row affected (0.00 sec)root&＃64;database-one 13:42: [discountstore]> select * from orders;
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
| no | Buyer | Seller | InstallationDate |
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
| 666 | Steve | Tom | 2020-03-18 13:41:26 |
| 777 | Jeff | Bill | 2020-03-18 13:42:28 |
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
2 rows in set (0.00 sec)

业务开展的很好&＃xff0c;现在要拓展到中国&＃xff0c;系统需要能够支持中文&＃xff0c;同时还得考虑继续扩展到其它国家的可能&＃xff0c;所以研发团队选utf8做为数据库新的字符集&＃xff0c;因为utf8兼容latin1&＃xff0c;所以只需修改数据库、表的默认字符集&＃xff0c;转换列的字符集即可&＃xff0c;不需要其它额外处理。

root&＃64;database-one 14:15: [discountstore]> alter database discountstore character set utf8;
Query OK, 1 row affected (0.00 sec)root&＃64;database-one 14:16: [discountstore]> show create database discountstore \G
*************************** 1. row ***************************Database: discountstore
Create Database: CREATE DATABASE &＃96;discountstore&＃96; /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)root&＃64;database-one 14:16: [discountstore]> alter table orders character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0root&＃64;database-one 14:20: [discountstore]> show create table orders \G
*************************** 1. row ***************************Table: orders
Create Table: CREATE TABLE &＃96;orders&＃96; (&＃96;no&＃96; int(11) DEFAULT NULL,&＃96;Buyer&＃96; varchar(30) CHARACTER SET latin1 DEFAULT NULL,&＃96;Seller&＃96; varchar(30) CHARACTER SET latin1 DEFAULT NULL,&＃96;InstallationDate&＃96; datetime DEFAULT NULL
) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8
1 row in set (0.00 sec)

可以看到&＃xff0c;库和表的字符集都变过来了&＃xff0c;但是列的字符集还是latin1&＃xff0c;对其也进行转换。

root&＃64;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: 0root&＃64;database-one 14:28: [discountstore]> show create table orders \G
*************************** 1. row ***************************Table: orders
Create Table: CREATE TABLE &＃96;orders&＃96; (&＃96;no&＃96; int(11) DEFAULT NULL,&＃96;Buyer&＃96; varchar(30) DEFAULT NULL,&＃96;Seller&＃96; varchar(30) DEFAULT NULL,&＃96;InstallationDate&＃96; datetime DEFAULT NULL
) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8
1 row in set (0.00 sec)root&＃64;database-one 14:32: [discountstore]> select * from orders;
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
| no | Buyer | Seller | InstallationDate |
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
| 666 | Steve | Tom | 2020-03-18 13:41:26 |
| 777 | Jeff | Bill | 2020-03-18 13:42:28 |
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
2 rows in set (0.00 sec)root&＃64;database-one 14:32: [discountstore]> insert into orders values(888,&＃39;肖杰&＃39;,&＃39;郭伟&＃39;,now()&＃43;4);
Query OK, 1 row affected (0.00 sec)root&＃64;database-one 14:32: [discountstore]> select * from orders;
&＃43;------&＃43;--------&＃43;--------&＃43;---------------------&＃43;
| no | Buyer | Seller | InstallationDate |
&＃43;------&＃43;--------&＃43;--------&＃43;---------------------&＃43;
| 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 |
&＃43;------&＃43;--------&＃43;--------&＃43;---------------------&＃43;
3 rows in set (0.00 sec)

可以看到&＃xff0c;转换完毕后&＃xff0c;原数据正常&＃xff0c;新的中文数据也可以存入了。

在上面的例子中&＃xff0c;字符集从latin1转换为utf8&＃xff0c;目标字符集是原字符集的超集&＃xff0c;完全兼容&＃xff0c;所以才能用命令转换。如果反过来&＃xff0c;业务收缩&＃xff0c;要从utf8将回latin1&＃xff0c;就要先进行数据清洗&＃xff0c;然后再修改和转换字符集&＃xff0c;否则会因存量数据导致转换失败。

root&＃64;database-one 14:43: [discountstore]> alter database discountstore character set latin1;
Query OK, 1 row affected (0.00 sec)root&＃64;database-one 14:43: [discountstore]> show create database discountstore \G
*************************** 1. row ***************************Database: discountstore
Create Database: CREATE DATABASE &＃96;discountstore&＃96; /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)root&＃64;database-one 14:44: [discountstore]> alter table orders character set latin1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root&＃64;database-one 14:44: [discountstore]> show create table orders \G
*************************** 1. row ***************************Table: orders
Create Table: CREATE TABLE &＃96;orders&＃96; (&＃96;no&＃96; int(11) DEFAULT NULL,&＃96;Buyer&＃96; varchar(30) CHARACTER SET utf8 DEFAULT NULL,&＃96;Seller&＃96; varchar(30) CHARACTER SET utf8 DEFAULT NULL,&＃96;InstallationDate&＃96; datetime DEFAULT NULL
) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;latin1
1 row in set (0.00 sec)root&＃64;database-one 14:44: [discountstore]> select * from orders;
&＃43;------&＃43;--------&＃43;--------&＃43;---------------------&＃43;
| no | Buyer | Seller | InstallationDate |
&＃43;------&＃43;--------&＃43;--------&＃43;---------------------&＃43;
| 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 |
&＃43;------&＃43;--------&＃43;--------&＃43;---------------------&＃43;
3 rows in set (0.00 sec)root&＃64;database-one 14:46: [discountstore]> alter table orders convert to character set latin1;
ERROR 1366 (HY000): Incorrect string value: &＃39;\xE8\x82\x96\xE6\x9D\xB0&＃39; for column &＃39;Buyer&＃39; at row 3
root&＃64;database-one 14:46: [discountstore]> delete from orders where no&＃61;888;
Query OK, 1 row affected (0.00 sec)root&＃64;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: 0root&＃64;database-one 14:47: [discountstore]> select * from orders;
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
| no | Buyer | Seller | InstallationDate |
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
| 666 | Steve | Tom | 2020-03-18 13:41:26 |
| 777 | Jeff | Bill | 2020-03-18 13:42:28 |
&＃43;------&＃43;-------&＃43;--------&＃43;---------------------&＃43;
2 rows in set (0.00 sec)root&＃64;database-one 14:47: [discountstore]> show create table orders \G
*************************** 1. row ***************************Table: orders
Create Table: CREATE TABLE &＃96;orders&＃96; (&＃96;no&＃96; int(11) DEFAULT NULL,&＃96;Buyer&＃96; varchar(30) DEFAULT NULL,&＃96;Seller&＃96; varchar(30) DEFAULT NULL,&＃96;InstallationDate&＃96; datetime DEFAULT NULL
) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;latin1
1 row in set (0.00 sec)

先将数据导出&＃xff0c;调整字符集&＃xff0c;再将数据导入的方法我们也测试一下。

&＃xff08;可跳转至https://www.modb.pro/db/22722?cyn查看&＃xff09;

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