insert select带来的问题

当使用 insert…select…进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。

对于那些从oracle迁移过来的应用，需要特别的注意，因为oracle并不存在类似的问题，所以在oracle的应用中insert…select…操作非常的常见。

例如：有时候会对比较多的纪录进行统计分析，然后将统计的中间结果插入到另外一个表，这样的操作因为进行的非常少，所以可能并没有设置相应的索引。如果迁移到mysql数据库后不进行相应的调整，那么在进行这个操作期间，对需要select的表实际上是进行的全表扫描导致的所有记录的锁定，将会对应用的其他操作造成非常严重的影响。

究其主要原因，是因为 mysql 在实现复制的机制时和 oracle 是不同的，如果不进行 select 表的锁定，则可能造成从数据库在恢复期间插入结果集的不同，造成主从数据的不一致。如果不采用主从复制，关闭 binlog 并不能避免对 select 纪录的锁定，某些文档中提到可以通过设置innodb_locks_unsafe_for_binlog来避免这个现象，当这个参数设置为true的时候，将不会对select的结果集加锁，但是这样的设置将可能带来非常严重的隐患。如果使用这个binlog进行从数据库的恢复，或者进行主数据库的灾难恢复，都将可能和主数据库的执行效果不同。

解决方法：
推荐使用select…into outfile 和 load data infile 的组合来实现，这样是不会对纪录进行锁定的
具体步骤：
例如将t1的数据insert进t3
1、1、mysql> select * into outfile “/data/t1.txt” from t1;
将表文件生成到数据目录
2、mysql> create table t3 as select * from t1 where 1=2;
创建一个相同表结构的表
3、mysql> load data infile “/data/t1.txt” into table t3;
将文件加载到新创建的表

注意：
1、secure_file_priv
如果这个参数为NULL，MySQL服务会禁止导入和导出操作。这个参数在MySQL 5.7.6版本引入

如果这个参数设为一个目录名，MySQL服务只允许在这个目录中执行文件的导入和导出操作。这个目录必须存在，MySQL服务不会创建它

如果这个参数为空，这个变量没有效果

这里将导出的数据文件放到数据库的数据目录，为的是在导出数据时不产生权限错误

2、导出过程有几个参数可以修改数据文件的存储格式：
select * from Table into outfile ‘/路径/文件名’
fields terminated by ‘,’
enclosed by ‘"’
lines terminated by ‘\r\n’

● fields子句：在FIELDS子句中有三个亚子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。如果指定了FIELDS子句，则这三个亚子句中至少要指定一个。

（1）TERMINATED BY用来指定字段值之间的符号，例如，“TERMINATED BY ‘,’”指定了逗号作为两个字段值之间的标志。

（2）ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如，“ENCLOSED BY ’ " '”表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY表示所有的值都放在双引号之间。

（3）ESCAPED BY子句用来指定转义字符，例如，“ESCAPED BY '’”将“”指定为转义字符，取代“\”，如空格将表示为“*N”。

● LINES子句：在LINES子句中使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY ‘?’”表示一行以“?”作为结束标志。