MySQL深入37-如何快速复制一张表
# MySQL深入37-如何快速复制一张表
insert … select 语句可以实现复制一张表，但这个情况只是针对可以控制源表的扫描行数和加锁范围很小情况下。为了避免对源表加读锁，更稳妥的方案是更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。
先创建一个表 db1.t，并插入 1000 行数据，同时创建一个相同结构的表 db2.t。
create database db1;
use db1;
create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create database db2;
create table db2.t like db1.t
现在我们的需求是把 db1.t 里面 a>900 的数据行导出来，插入到 db2.t 中。
# mysqldump方法
使用mysqldump命令将数据导出成一组INSERT语句：
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
通过这条命令，生成的临时文件如下：
可以看到，一条INSERT语句里面会包含多个value对，这是为了后续用这个文件写入数据的时候，执行速度更快。
如果希望生成的文件中一条 INSERT 语句只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数–skip-extended-insert。
然后，通过下面这条命令，将这些INSERT语句放到db2库里面去执行：
mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
mysql 客户端执行这个命令的流程是这样的：
- 打开文件，默认以分号为结尾读取一条条的 SQL 语句；
- 将 SQL 语句发送到服务端执行。
# 导出CSV文件
另一种方法是直接将结果导出成.csv 文件。MySQL 提供了下面的语法，用来将查询结果导出到服务端本地目录：
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
得到.csv 导出文件后，你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中。
load data infile '/server_tmp/t.csv' into table db2.t;
该条语句的执行流程如下：
打开文件 /server_tmp/t.csv，以制表符 (\t) 作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；
启动事务。
判断每一行的字段数与表 db2.t 是否相同：
- 若不相同，则直接报错，事务回滚；
- 若相同，则构造成一行，调用 InnoDB 引擎接口，写入到表中。
重复步骤 3，直到 /server_tmp/t.csv 整个文件读入完成，提交事务。
# 物理拷贝方法
直接把 db1.t 表的.frm 文件和.ibd 文件拷贝到 db2 目录下，是否可行呢？
因为，一个 InnoDB 表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有 db2.t 这个表，系统是不会识别和接受它们的。所以上述的方案是不太行的。
在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。
- 执行 create table r like t，创建一个相同表结构的空表；
- 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
- 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
- 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
- 执行 unlock tables，这时候 t.cfg 文件会被删除；
- 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。
# 小结
物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。但是这种方法的使用也存在一定的局限性。
- 必须是全表拷贝，不能只拷贝部分数据；
- 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
- 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。
用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。