|
今天主要介绍下mysqldump怎么迁移编码不同的数据库,这里用一个实验来演示一下。
1. 创建测试数据库
- create database t1 default character set gbk collate gbk_chinese_ci;
- create database t2 default character set utf8 collate utf8_general_ci;
- use t1;
- create table t11(id int,name varchar(10),primary key(id)) engine=innodb default charset=utf8;
- create table t12(id int,name varchar(10),primary key(id)) engine=innodb default charset=gb2312;
- use t2;
- create table t21(id int,name varchar(10),primary key(id)) engine=innodb default charset=utf8;
- create table t22(id int,name varchar(10),primary key(id)) engine=innodb default charset=gb2312;
2. 模拟数据
- use t1;
- insert into t11 values(1,'hwb');
- insert into t11 values(2,'hwb2');
- insert into t12 values(1,'hwb');
- insert into t12 values(2,'hwb2');
-
- use t2;
- insert into t21 values(1,'hwb');
- insert into t21 values(2,'hwb2');
- insert into t22 values(1,'hwb');
- insert into t22 values(2,'hwb2');
3. 导出数据库
- mysqldump -u root -p t1 > /tmp/t1_old.sql
- mysqldump -u root -p t2 > /tmp/t2_old.sql
4. 转换编码
如果你是想转成utf-8,就在t参数后面写上。以前字符集是gb2312就在-f参数后面写上。
- iconv -t utf-8 -f gb2312 -c /tmp/t1_old.sql > /tmp/t1_new_utf8.sql
- iconv -t utf-8 -f gb2312 -c /tmp/t2_old.sql > /tmp/t2_new_utf8.sql
5. 修改 /tmp/new_utf8.sql文件的字符编码
- sed 's/CHARSET=gbk/CHARSET=utf8/g' /tmp/t1_new_utf8.sql > /tmp/t1_new_utf8_new.sql
- sed 's/CHARSET=gb2312/CHARSET=utf8/g' /tmp/t1_new_utf8_new.sql > /tmp/t1_new_utf8_new2.sql
-
- sed 's/CHARSET=gbk/CHARSET=utf8/g' /tmp/t2_new_utf8.sql > /tmp/t2_new_utf8_new.sql
- sed 's/CHARSET=gb2312/CHARSET=utf8/g' /tmp/t2_new_utf8_new.sql > /tmp/t2_new_utf8_new2.sql
6. 建新库
- create database t3 default character set utf8 collate utf8_general_ci;
- create database t4 default character set utf8 collate utf8_general_ci;
（编辑：宣城站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
