insert into table select * from table使用tmp

insert into t1 select * from t2;

现象：以上SQL执行过程中如果数据量大会导致，/tmp空间增长,/tmp空间耗尽。

 

mysql 解释

When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table. However, it remains true that you cannot use INSERT INTO t ... SELECT ... FROM t when t is a TEMPORARY table, because TEMPORARY tables cannot be referred to twice in the same statement (see Section C.5.7.2, “TEMPORARY Table Problems”).

现象

Created_tmp_tables持续增加，/tmp目录空间占用持续增加。

mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 2     |
| Created_tmp_files       | 7     |
| Created_tmp_tables      | 143   |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 2     |
| Created_tmp_files       | 7     |
| Created_tmp_tables      | 158   |