insert into...select需要注意的问题

平常习惯生产数据的时候，直接用insert into ...select这种语法，结果今天需要的数据量稍微一大就出错了，错误信息如下：
mysql> insert into t1 select null, b, c from t1;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_689f_0.MYI'; try to repair it


对这个错误信息，第一感觉很是奇怪，我这个表t1明明是innodb类型的，怎么会与myisam表扯上关系。于是查看了一下手册，发现了原因。对于insert into ...select 这种语法，如果源表和目的表为同一个表，那么它的处理流程是：

1.将源表select的结果放入一个临时表

可以通过show processlist来查看

mysql> show processlist;
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------+
| 1 | root | localhost | test | Query | 170 | Copying to tmp table on disk | insert into t1 select null, b, c from t1 |
| 2 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------+

2.然后将临时表的内容插入到目的表
然后在手册上查看了一下临时表temporary table，没有对表的类型进行强制要求，但是我的参数
mysql> show variables like '%default%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
| default_week_format | 0 |
+------------------------+--------+
mysql为什么它创建的表为myisam类型呢？好了，这里先不谈这个问题。继续重点，表面上看去是/tmp/#sql_689f_0.MYI这个问题损坏了，但实际上是由于/tmp/的空间不够用了(在mysql里面经常遇到什么can't之类的错误，很多时候是由于没有权限或是没有磁盘空间了)，确定它是由于没有磁盘空间是通过以下步骤来完成的：
1.mysql> insert into t1 select null, b, c from t1;
2.在上一步报错之前 df -h 可以查看到/tmp的占用率很快增长到100%

通过改变TMPDIR参数(指定到一个可用空间更大的目录)解决这个问题。
另外，为什么创建的临时表类型为myisam，我觉得可能是从性能上考虑的，因为这里创建的临时表就两个操作，insert进去、select出来没有其他的要求，用myisam表完全可以解决。

但是还遗留下了一个问题：每次生成的临时表的大小是源表的2倍。比如说t现在大小是1G，那么执行insert into t1 select null, b, c from t1;这时去查看生成的临时表的大小是2G。 没想明白原因。

多谢网友kerlion(itpub id)提醒，找到答案了：

由于源表里面有varchar类型的字段。而在创建临时表的时候，mysql默人将这些字段全部转换成char类型，我想它这么多的原因是为性能考虑的吧，典型的用空间换时间做法。

我也测试过源表没有varchar类型，那么将不会有上面的现象发生。

的确是有变长字段引起的，不过并没有我想的那么复杂。因为mysql中创建的临时表默认是memory类型，当达到一定规模以后就自动转换成myisam表，而memory表的字段类型只能是定长的，即使你创建表的时候是变长，但它也会将它作为定长用。

这才是正确的答案。