MySQL中UNION子查询加ORDER BY触发Error 1114：表已满

错误本质 #

在UNION的子查询中添加ORDER BY时，MySQL会为每个子查询的排序结果创建临时表，再合并这些临时表。临时表默认会写入磁盘（当内存不足以承载时），如果临时表存储的磁盘分区空间不足，或者临时表的配置限制触发，就会抛出"表已满"错误——哪怕你只是执行查询，因为临时表的创建也需要占用磁盘空间。

核心注意事项 #

• 子查询内的ORDER BY完全无效：UNION操作会重新合并、去重结果，子查询里的排序会被完全覆盖，属于多余操作。如果需要对最终结果排序，应该在外层查询添加ORDER BY，示例：

  select fnl_res.id, fnl_res.business_name, fnl_res.owner_name, fnl_res.date 
  from (
      select b_infos.id, b_infos.business_name, b_infos.owner_name, orders.date 
      from b_infos inner join orders on b_infos.id = orders.businessId
      UNION
      select b_infos.id, b_infos.business_name, b_infos.owner_name, expenses_logs.date 
      from b_infos inner join expenses_logs on b_infos.id = expenses_logs.businessId
  ) as fnl_res
  ORDER BY fnl_res.date;
  

• 检查临时表存储磁盘空间：查看MySQL的tmpdir参数，确认临时表存储的磁盘分区是否有足够剩余空间。如果空间不足，清理分区冗余文件，或者修改tmpdir指向空间充足的磁盘。
• 调整临时表内存阈值：修改tmp_table_size和max_heap_table_size参数，让小结果集的临时表优先在内存中处理，减少磁盘占用。注意不要设置过大，避免内存耗尽。
• 用UNION ALL替代UNION（若允许重复）：UNION会执行去重操作，需要额外的排序和临时表开销；如果业务允许结果重复，改用UNION ALL可以大幅降低临时表的资源消耗，避免触发空间不足问题。
• 优化关联字段索引：确保b_infos.id、orders.businessId、expenses_logs.businessId这些关联字段都创建了索引，减少子查询的结果集大小，间接降低临时表的空间占用。

内容的提问来源于stack exchange，提问作者Nullified