mysql数据库修改表字符集_MySQL之对数据库库表的字符集的更改-CSDN博客

本文链接：https://blog.csdn.net/weixin_31165655/article/details/113424056

数据字符集修改步骤：

对于已有的数据库想修改字符集不能直接通过 "alter database character set *"或 "alter table tablename character set *"，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。已经有记录的字符集的调整，必须先将数据导出，经过修改字符集后重新导入后才可完成。修改数据库默认编码： alter database [your db name] charset [your character setting]

下面模拟将latin1字符集的数据修改成GBK字符集的实际过程。

1、导出表结构 [root@master ~]# /opt/mysql/bin/mysqldump --default-character-set=latin1 -uroot -p -d dbname > alltable.sql

2、编辑alltable.sql将latin1改成GBK

3、确保数据库不再更新，导出所有数据 [root@master ~]# /opt/mysql/bin/mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 dbname > alldata.sql

参数说明： --quick：用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行，并输出前CACHE到内存中。

--no-create-info：不创建CREATE TABLE语句。

--extended-insert：使用包括几个VALUES列表的多行INSERT语法，这样你更小，IO也小，导入数据时会非常快。

--default-character-set=latin1：按照原有字符集导出数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码。

4、打开alldata.sql将set names latin1修改成set names gbk;

5、删库和建库： create database dbname default charset gbk;

6、创建表，执行alltable.sql mysql -uroot -p dbname < alltable.sql

7、导入数据 mysql -uroot -p dbname < alldata.sql 注意：选择目标字符集时，要注意最好大于等于源字符集(字库更大)，否则，可能会丢失不被支持的数据。

具体操作如下：

更改前的数据：[root@master mysql]# mysql -uroot -p123456 -e "show create database oldboy\G;show create table oldboy.student\G;set names latin1;select * fromoldboy.student;\G"

Warning: Using a passwordonthe command line interface can be insecure.*************************** 1. row ***************************

Database: oldboyCreate Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET latin1*/

*************************** 1. row ***************************

Table: studentCreate Table: CREATE TABLE`student` (

`id`int(4) NOT NULLAUTO_INCREMENT,

`name`char(20) NOT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1+----+-----------+

| id | name |

+----+-----------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

| 6 | ??? |

| 7 | 老男孩 |

+----+-----------+

导出表结构：[root@master mysql]# /opt/mysql/bin/mysqldump --default-character-set=latin1 -uroot -p -d oldboy > zhulh/alltable.sql

查看导出内容：[root@master mysql]# grep -E -v "#|\/|^$|--" zhulh/alltable.sql

DROP TABLE IF EXISTS`error_log`;CREATE TABLE`error_log` (

`error_message`char(80) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;DROP TABLE IF EXISTS`student`;CREATE TABLE`student` (

`id`int(4) NOT NULLAUTO_INCREMENT,

`name`char(20) NOT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;DROP TABLE IF EXISTS`t2`;CREATE TABLE`t2` (

`s1`int(11) NOT NULL,PRIMARY KEY(`s1`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;DROP TABLE IF EXISTS`t3`;CREATE TABLE`t3` (

`s1`int(11) DEFAULT NULL,KEY`s1` (`s1`),CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES`t2` (`s1`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

修改latin1为utf8：[root@master mysql]# sed -i "s/latin1/utf8/" zhulh/alltable.sql[root@master mysql]# grep -E -v "#|\/|^$|--" zhulh/alltable.sql

DROP TABLE IF EXISTS`error_log`;CREATE TABLE`error_log` (

`error_message`char(80) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS`student`;CREATE TABLE`student` (

`id`int(4) NOT NULLAUTO_INCREMENT,

`name`char(20) NOT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS`t2`;CREATE TABLE`t2` (

`s1`int(11) NOT NULL,PRIMARY KEY(`s1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS`t3`;CREATE TABLE`t3` (

`s1`int(11) DEFAULT NULL,KEY`s1` (`s1`),CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES`t2` (`s1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

确保数据库不再更新，导出所有数据[root@master mysql]# /opt/mysql/bin/mysqldump --default-character-set=latin1 --quick --no-create-info --extended-insert -uroot -p -B oldboy > zhulh/alldata.sql

修改latin1为utf8：(将SET NAMES latin1 修改成SET NAMES utf8)[root@master mysql]# sed -i "s/latin1/utf8/" zhulh/alldata.sql

删除老库：

root@mysql5.6 01:09:24->drop databaseoldboy;

Query OK,4 rows affected (0.20sec)

新建新库：

root@mysql5.6 01:12:53->create database oldboy defaultcharset utf8;

Query OK,1 row affected (0.00sec)

导入数据：[root@master mysql]# mysql -uroot -p123456 oldboy < zhulh/alltable.sql

Warning: Using a passwordonthe command line interface can be insecure.[root@master mysql]# mysql -uroot -p123456 oldboy < zhulh/alldata.sql

Warning: Using a passwordonthe command line interface can be insecure.[root@master mysql]# mysql -uroot -p123456 -e "select * fromoldboy.student;"

Warning: Using a passwordonthe command line interface can be insecure.+----+-----------+

| id | name |