合并MYSQL超级大表时遇到的问题

今天需要将30个mysql表数据合并进行分析。每个表的结构相同，数据量在200万左右，数据表为MYISAM格式的，数据文件大小约是800M。我是用 insert into ... select 进行合并的，总是合并至第6个表时提示有以下错误： ERROR 1114 (HY000): The table xxxxx is full

第一反应是查看磁盘空间，还有40G剩余空间，应该够用啊。只能求助于Google了，果然也有人遇到过此问题，解决方法是加大max_heap_table_size和tmp_table_size的设定。看来离胜利不远了:) 可是我都加大到1G了，依然提示错误，郁闷啊。还是研究一下文档吧。

文档中第13.5.3. SET Syntax BIG_TABLES = {0 | 1}

If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error The table tbl_name is full does not occur for SELECT operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). Normally, you should never need to set this variable, because in-memory tables are automatically converted to disk-based tables as required. (Note: This variable was formerly named SQL_BIG_TABLES.)

执行 set @@big_tables = 1; 还是提示：ERROR 1114 (HY000): The table xxxxx is full

被逼上绝路了。

换个存储格式试试，ALTER TABLE xxxx ENGINE = innodb。

居然成功了。