mysql 复制表数据

在 mysql 数据库的使用过程当中，复制数据是一个非常常见并且有用的操作，在数据量较小，表扫描行数少的情况下，我们可以使用 insert into ... select ... 语句来完成表数据的复制。具体的流程如下：

假如有表 t，建表语句和初始化数据如下：

create table t (
    id int not null primary key,
    c  int null,
    d  int null
);

insert into t values (0, 0, 0), (5, 5, 5), (10, 10, 10), (15, 15, 15), (20, 20, 20);

然后使用语句 create table t_temp like t 创建一个和表 t 结构相同的表 t_temp，这时我们需要将表 t 中的数据复制到表 t_temp 中，可以使用命令 insert into t_temp select * from t where id > 10;，这里的 where 条件是可选的，后面可以加其他的 sql 语句。

这种拷贝表数据的方式简单，但是存在一个很大的局限性，那就是在数据量较大的情况下，事务执行的时间较长，对于一些业务关联性强的表，可能对业务造成影响。

因此，我们可以将表中的数据存储到外部文件中，然后再导入到另一个表中，常见的方法有以下介绍的三种。

一、逻辑拷贝

1.1 mysqldump

mysqldump 是 mysql 官方的逻辑备份工具，它使用起来非常简单，功能强大，下面是一个示例：

mysqldump -u root -p --databases sakila --tables t --single-transaction --where="id >= 20" --result-file=/usr/local/sakila_t.sql

这几个常见参数的含义分别是：

• -u、-p 和登录数据库时的选项是一样的，分别表示用户名和密码
• –databases 表示指定数据库
• –tables 表示指定需要导出的表（可选）
• –single-transaction 表示使用一致性视图，导出数据的时候不加表锁
• –where 后面可以加上简单的过滤条件
• –result-file 后面加上导出的文件的存储路径

如果需要查看 mysqldump 的更多用法，直接在终端输入 mysqldump --help 即可。

导出之后，需要在目标数据库命令行中使用 source 命令执行，语句如下：

source /usr/local/sakila_t.sql

1.2 导出数据文件

另一种方式是使用 sql 语句导出为外部文件，命令是

select * from t into outfile ’/var/lib/mysql-files/sakila_t.csv‘

需要注意的是，导出的文件的存储路径受到系统参数 secure_file_priv 的控制，这个参数的取值情况有以下三种：

• 如果设置是空的，表示不限制文件存储的路径，一般这样设置是不太安全的
• 如果设置为一个具体的路径，表示生成的文件必须存放在这个目录下
• 如果设置为 NULL，表示禁止在这个 mysql 实例中执行 select ... into outfile 操作

导出为数据文件后，在 mysql 命令行执行以下命令将数据导入：

load data infile '/var/lib/mysql-files/sakila_t.csv' into table sakila.t_temp;

需要注意的是，如果数据文件中的数据列和数据表中字段不对应的话，那么会直接报错，导入失败。

使用这个命令导出，只能导出数据文件，而不能导出表结构，如果需要导出数据表的结构，可以使用如下的 mysqldump 命令：

mysqldump --databases sakila --tables t --tab=/var/lib/mysql-files -u root -p

执行这个导出命令，会在 /var/lib/mysql-files(secure_file_priv 指定的路径) 生成一个表结构 sql 文件，以及一个表数据 txt 文件。

二、物理拷贝

前面介绍的这两种方式都是逻辑复制数据的方法，在 mysql 5.6 及以后，可以通过复制表文件的方式来进行物理复制表的数据。

还是以上面的表 t 为例，假如需要将其复制一份，命名为 t_temp，具体的步骤如下：

• 执行命令 create table t_temp like t，创建一个和表 t 结构相同的表 t_temp
• 执行命令 alter table t_temp discard tablespace，这时候表 t_temp 的 ibd 文件会被删除
• 执行命令 flush table t for export，这时候在数据库的表结构文件夹中，会生成一个 t.cfg 文件。表结构文件保存在 mysql 的默认数据目录，所对应的数据库目录下（一般是 /var/lib/mysql）。需要注意的是，执行完这个命令之后，表处于只读状态，直到下面执行 unlock tables 时才释放读锁
• 复制表 t 的数据文件，执行命令 cp t.cfg t_temp.cfg 和 cp t.ibd t_temp.ibd，如果复制的文件不是属于 mysql 用户的，需要使用 chown 命令将文件的所属用户和用户组改为 mysql
• 执行命令 unlock tables ，这时候文件 t.cfg 将会被删除，并且释放表 t 的读锁
• 执行命令 alter table t_temp import tablespace，拷贝完成

下面对几种常见的拷贝表数据的方式做个简单的总结：

相较于其他的几种方式，物理复制表数据的速度是最快的，但是它的缺点是只能复制整个表的数据，不能操作部分数据，并且这种方式只支持 InnoDB 表。

而使用 mysqldump 导出数据，优点是可以使用过滤条件导出部分数据，但是其缺点是不能使用一些较为复杂的 sql 语句例如 join。

使用 select ... from t into outfile 这种方式，最大的优点是支持各种 sql ，更加的灵活，但是它不能导出表结构，导出表结构的话需要单独操作。