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

本是古典何须时尚 2023-01-11 13:12 164阅读 0赞

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

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

\+------+-------+--------+---------------------+

\+------+-------+--------+---------------------+

| 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;

\+------+-------+--------+---------------------+

\+------+-------+--------+---------------------+

| 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;

\+------+--------+--------+---------------------+

\+------+--------+--------+---------------------+

| 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;

\+------+--------+--------+---------------------+

\+------+--------+--------+---------------------+

| 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;

\+------+-------+--------+---------------------+

\+------+-------+--------+---------------------+

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

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

特别注意：

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

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL查看和修改数据库字符集的方法

相关 mysql修改字符集

相关 mysql修改有数据的数据库的字符集_MySQL修改有存量数据的数据库字符集实战

相关 mysql查看、修改数据库字符集

相关 mysql修改有数据的数据库的字符集_数据库字符集修改

相关 MySQL 修改字符集

相关修改及查看mysql数据库的字符集

相关关于mysql字符集的修改

相关 mysql修改字符集

相关【MySQL】MySQL如何查看和修改数据库字符集