mysql 1114_ERROR 1114 (HY000): The table 'adv_date_tmp' is full（Mysql临时表应用）

场景：需要对现在数据库的数据进行批量的进行is_del=1的操作，但是遇到一个问题，在执行sql的时候发现sql不能在查询特定表的时候再嵌套查询来做update的操作，经过讨论，后续我们想到用临时表的方案来解决这个问题。

开始进行数据测试，进行单用户的update操作，一切都比较顺利没有任何问题，操作的也是我们想操作的数据，接下来进行批量的数据操作，sql语句运行一会出现如下错误：

ERROR 1114 (HY000): The table 'adv_date_tmp' is full

报错为临时表已满，原因是没有调整mysql的默认临时表大小导致的报错。

tmp_table_size

如果内存内的临时表超过该值，MySQL自动将它转换为硬盘上的MyISAM表。如果你执行许多高级GROUP BY查询并且有大量内存，则可以增加tmp_table_size的值。

max_heap_table_size

该变量设置MEMORY (HEAP)表可以增长到的最大空间大小。该变量用来计算MEMORY表的MAX_ROWS值。在已有的MEMORY表上设置该变量没有效果，除非用CREATE TABLE或TRUNCATE TABLE等语句重新创建表。

默认情况下的临时表大小不能满足特定场景的需求，这个时候就需要将临时表的大小进行动态的调整，一般我们推荐进行set session动态进行会话调整(可以不重启服务器进行调整)，不过也可以通过调整my.cnf的配置来进行永久的调整(需要重启服务，不推荐这种操作)。

查询mysql的默认配置临时表的大小

mysql> show variables like '%tmp_table%';+----------------+----------+

| Variable_name | Value |

+----------------+----------+

| max_tmp_tables | 32 |

| tmp_table_size | 16777216 |

+----------------+----------+

2 rows in set (0.00sec)

mysql> show variables like '%max_heap%';+---------------------+----------+

| Variable_name | Value |

+---------------------+----------+

| max_heap_table_size | 16777216 |

+---------------------+----------+

1 row in set (0.00 sec)

在线调整临时表大小

mysql> set session tmp_table_size=1024*1024*1024;

Query OK,0 rows affected (0.00sec)

mysql> set session max_heap_table_size=1024*1024*1024;

Query OK,0 rows affected (0.00sec)

mysql> show variables like '%tmp_table%';+----------------+------------+

| Variable_name | Value |

+----------------+------------+

| max_tmp_tables | 32 |

| tmp_table_size | 1073741824 |

+----------------+------------+

2 rows in set (0.00sec)

mysql> show variables like '%max_heap%';+---------------------+------------+

| Variable_name | Value |

+---------------------+------------+

| max_heap_table_size | 1073741824 |

+---------------------+------------+

1 row in set (0.00 sec)

进行sql语句操作

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table ENGINE = MEMORY SELECT adf.opt_id FROM `test`.`test.count` a INNER JOIN `test`.`test.prise` e ON a.prise_id=e.prise_id AND e.is_del=0 AND e.domain IN('test1','test2','test3','test4','test5','test6','test7','test8','test9','test10','test11','test12','test13','test14','test15','test16','test17','test18','test19') INNER JOIN `online`.`FILTER` adf ON adf.user_id=a.user_id AND adf.is_del=0 AND adf.is_global=0 AND adf.`type`='fixed' AND adf.`name` IN ('今天', '昨天', '最近7天', '最近30天', '最近90天', '最近一年') LEFT JOIN `online`.`OPT_ID_DELETE` oid ON adf.opt_id=oid.opt_id WHERE a.is_del=0 AND a.username='admin' AND oid.opt_id is NULL;

Query OK,101976 rows affected (2 min 3.82sec)

Records:101976 Duplicates: 0 Warnings: 0

更新表数据

mysql> UPDATE `online`.`FILTER` SET is_del=1 WHERE opt_id IN( SELECT opt_id FROMtmp_table);

mysql> drop table tmp_table;

Ok，整个操作完成。