MySQL字符集转换思路
参考资料:http://blog.chinaunix.net/uid-25266990-id-3344584.html
以latin1转换到utf8为例
以原来的字符集为latin1为例,升级成为utf8的字符集。
原来的表: tab_lww1 (default charset=latin1),新表:new_tab_lww (default charset=utf8)。
MySQL>create database db_lww default charset=latin1;
Query OK, 1 row affected (0.01 sec)
MySQL>show create database db_lww;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| db_lww | CREATE DATABASE `db_lww` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL>use db_lww;
Database changed
MySQL>create table tab_lww (id int primary key,name varchar(15) not null) default charset=latin1;
MySQL>show create table tab_lww\G; *************************** 1. row ***************************
Table: tab_lww
Create Table: CREATE TABLE `tab_lww` (
`id` int(11) NOT NULL,
`name` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
MySQL>set names latin1;
MySQL>\s
--------------
mysql Ver 14.14 Distrib 5.6.16, for Linux (x86_64) using EditLine wrapper
Connection id: 34
Current database: db_lww
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.16-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
MySQL>insert into tab_lww values(1,'北京市');
Query OK, 1 row affected (0.04 sec)
MySQL>insert into tab_lww values(1,'天津市');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MySQL>insert into tab_lww values(2,'天津市');
Query OK, 1 row affected (0.03 sec)
MySQL>select * from tab_lww;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 北京市 |
| 2 | 天津市 |
+----+-----------+
2 rows in set (0.00 sec)
原来的表: tab_lww1 (default charset=latin1),新表:new_tab_lww (default charset=utf8)。
MySQL>create database db_lww default charset=latin1;
Query OK, 1 row affected (0.01 sec)
MySQL>show create database db_lww;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| db_lww | CREATE DATABASE `db_lww` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL>use db_lww;
Database changed
MySQL>create table tab_lww (id int primary key,name varchar(15) not null) default charset=latin1;
MySQL>show create table tab_lww\G; *************************** 1. row ***************************
Table: tab_lww
Create Table: CREATE TABLE `tab_lww` (
`id` int(11) NOT NULL,
`name` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
MySQL>set names latin1;
MySQL>\s
--------------
mysql Ver 14.14 Distrib 5.6.16, for Linux (x86_64) using EditLine wrapper
Connection id: 34
Current database: db_lww
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.16-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
MySQL>insert into tab_lww values(1,'北京市');
Query OK, 1 row affected (0.04 sec)
MySQL>insert into tab_lww values(1,'天津市');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MySQL>insert into tab_lww values(2,'天津市');
Query OK, 1 row affected (0.03 sec)
MySQL>select * from tab_lww;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 北京市 |
| 2 | 天津市 |
+----+-----------+
2 rows in set (0.00 sec)
步骤一:导出表结构
[mysql@my1 tmp]$ mysqldump -uroot -p --default-character-set=utf8 -d db_lww tab_lww >/tmp/create_tab_lww_schema.sql这里的utf8就是目标字符集; -d 表示只导出来表结构。
步骤二:修改刚才导出来的文件
[mysql@my1 tmp]$ sed -i s/CHARSET=latin1/CHARSET=utf8/g `grep -rl "CHARSET=latin1" create_tab_lww_schema.sql`步骤三:确保不再更新
[mysql@my1 tmp]$ mysqldump -uroot -p --quick --extended-insert --no-create-info --default-character-set=latin1 db_lww tab_lww >data_tab_lww.sql可选参数:
--quick: 该选项用于转储大的表。 它强制 mysqldump 从服务器一次一行地检索表中的行而不是 检索所有行,并在输出前将它缓存到内存中。
--extended-insert: 使用包括几个 values 列表的多行insert语法,这样使转储文件更小,重载文件时可以加速插入。
--no-create-info: 不写重新创建每个转储表的create table 语句。
--default-character-set=latin1: 按照原有的字符集导出所有数据,这样导出的文件中,所有中文都是可见的,不会保存成乱码。不添加该参数以默认字符导出。
步骤四:打开data_tab_lww.sql文件,修改set names latin1为set names latin1.
[mysql@my1 tmp]$ sed -i s/latin1/utf8/g `grep -rl "latin1" data_tab_lww.sql`步骤五:创建新的数据库
MySQL>create database new_db_lww default charset=utf8;Query OK, 1 row affected (0.00 sec)
MySQL>use new_db_lww;
Database changed
MySQL>exit
Bye
步骤六:执行导入操作
[mysql@my1 tmp]$ mysql -uroot -p --default-character-set=utf8 new_db_lww < /tmp/create_tab_lww_schema.sqlEnter password:
[mysql@my1 tmp]$ mysql -uroot -p --default-character-set=utf8 new_db_lww < /tmp/data_tab_lww.sql
Enter password:
[mysql@my1 tmp]$
步骤七,验证数据
MySQL>use new_db_lww;Database changed
MySQL>select * from tab_lww;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 北京市 |
| 2 | 天津市 |
+----+-----------+
2 rows in set (0.00 sec)
MySQL>
MySQL>show create database new_db_lww;
+------------+---------------------------------------------------------------------+
| Database | Create Database |
+------------+---------------------------------------------------------------------+
| new_db_lww | CREATE DATABASE `new_db_lww` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL>show create table tab_lww\G;
*************************** 1. row ***************************
Table: tab_lww
Create Table: CREATE TABLE `tab_lww` (
`id` int(11) NOT NULL,
`name` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
MySQL>
可以正常显示数据。