如何将一张表的数据插入到另一张表当中
# 如何将一张表的数据插入到另一张表当中
场景：当前需要从t1表当中取出数据，插入到t2表当中。
# 方法一、insert...select批量插入
如果对对源表t1的扫描行数和加锁范围都比较小的情况下，可以使用批量插入的方式。在MySQL8.0版本下，已经支持使用临时表处理批量插入。
insert into t2 select * from t1 where a>33;
💣存在的问题：如果当前表t1是一个高并发业务表，会把t1锁住，从而影响业务响应时间。
# 方法二、mysqldump方法
使用mysqldump命令将数据导出成一组insert语句：
mysqldump -h$host -P$port -u$user --add-locks --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
- –single-transaction：导出数据时不需要对t1表加锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT开启事务，并创建快照读视图。
- –add-locks：设置为0，表示输出的文件结果里，不增加”Lock tables t write“，即插入数据时不会锁表然后再写。
- –no-create-info：表示不需要导出表结构
- –set-gtid-purged=off：不需要输出t1表里与GTID相关的信息
- –result-file：指定输出文件的路径，client表示输出在客户端机器上
最终得到.sql文件里的insert into语句，value就会包括多条t1表的记录。使用source客户端命令，执行文件内所有的SQL语句。
# 方法三、导出CSV文件
# 数据导出
MySQL提供以下语法将查询结果导出到服务端本地目录当中：
select * from db1.t wher a>900 into outfile '/server_tmp/t.csv';
注意以下几个点：
- 文件结果保存在MySQL服务端。
- 指定的文件生成位置(/server_tmp)必须受到参数secure_file_priv的限制:
- 设置为empty：表示不限制文件生成的位置
- 设置为一个表示路径的字符串：要求生成的文件只能放在这个指定的目录或者他的子目录
- 设置为NULL：表示禁止在这个MySQL实例上执行上面的csv导出语句
- 导出命令不会覆盖文件，因此导出之前需要确认在目标文件目录下是否存在t.csv文件。否则会报错
- 这条命令生成的文本文件中，如果一行数据中某个字段包含换行符，那么生成的这一行文本也会进行换行(从而出现一行数据导出了两行文本)。解决方法是加上\转义字符。
- 导出命令不会生成表结构文件，因此导出之前还需要使用其它工具获取表结构定义。
另外可以使用mysqldump工具-tab参数，直接同时导出表结构定义和csv数据文件：
mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv
# 数据加载
最后执行以下load data命令将数据导入目标表当中，考虑到主备复制的问题(statement格式下备库机器找不到csv文件，同步终止)，整个流程如下：
主库t2执行以下语句，完成数据导入：
load data infile '/server_tmp/t.csv' into table db2.t;1主库会将csv文件内容直接写到binlog文件当中。同时往binlog中写入如下语句(指定备库文件地址)：
load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE `db2`.`t`;1备库的binlog同步应用线程(客户端)收到binlog后：
- 将csv文件读出并写入到''/tmp/SQL_LOAD_MB-1-0'(执行同步应用线程的客户端的机器上)
- 执行binlog中的load data语句
# 方法四、物理拷贝
一个InnoDB表除了包含表结构定义.frm文件和表数据.ibd文件，还需要在数据字典中注册，系统才能够接受和识别。
因此单纯将两个文件拷贝到目标文件目录下，并不能真正实现表数据的拷贝。MySQL5.6引入可传输空间的方法：
- 执行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；两个命令复制源表的cfg文件和ibd数据。
- 执行unlock tables，此时t.cfg文件会被删除，并且释放读锁。
- 执行alter table r impory tablespace，将r.ibd文件作为表r新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。
✨如果出现误删表的情况，最快恢复的方法：①利用备份恢复误删之前的数据。②然后再将临时库中的表拷贝到生产库。
# 总结
💣mysqldump：可以通过–where添加可过滤字段，从而可以只导出部分数据。缺点在于不能使用包含join的复杂条件where查询。
💣导出csv：支持导出任意数据，并且支持所有SQL写法。缺点在于只能导出一张表的数据，并且表结构需要另外的语句单独备份。
💣物理拷贝：速度最快。缺点在于①必须是全表拷贝，不能进行筛选备份部分数据。②必须到t1源表的服务器上拷贝数据，执行对应语句和cfg文件。③源表和目标表都是使用InnoDB