ERROR 1114 (HY000): The table ‘XXXX‘ is full（Mysql临时表应用）

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

mysql> show variables like ‘%tmp_table%’;
±---------------±---------+
| Variable_name | Value |
±---------------±---------+
| max_tmp_tables | 32 |
| tmp_table_size | 16777216 |
±---------------±---------+
2 rows in set (0.00 sec)

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=102410241024;
Query OK, 0 rows affected (0.00 sec)

mysql> set session max_heap_table_size=102410241024;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘%tmp_table%’;
±---------------±-----------+
| Variable_name | Value |
±---------------±-----------+
| max_tmp_tables | 32 |
| tmp_table_size | 1073741824 |
±---------------±-----------+
2 rows in set (0.00 sec)

mysql> show variables like ‘%max_heap%’;
±--------------------±-----------+
| Variable_name | Value |
±--------------------±-----------+
| max_heap_table_size | 1073741824 |
±--------------------±-----------+
1 row in set (0.00 sec)