将MySQL表VARCHAR列转VARBINARY时遇“表已满”错误求解决

这个问题我之前帮不少开发者排查过——明明磁盘剩余空间足够，却还是触发了表满的错误，核心原因通常和临时表的存储限制有关，而不是你业务数据所在的磁盘分区。下面给你几个针对性的解决方案，按优先级排序：

1. 检查临时表目录（tmpdir）的可用空间 #

MySQL执行ALTER TABLE时会创建临时表来处理数据，这个临时表默认存在tmpdir指定的目录下，而这个目录可能不在你认为的“充足磁盘”分区上。

• 先查看当前tmpdir的位置：

  SHOW VARIABLES LIKE 'tmpdir';
  

• 然后用操作系统命令检查该目录所在分区的剩余空间（比如Linux下用df -h /path/to/tmpdir）。如果这个分区空间不足，比如默认的/tmp是个小分区，就会触发错误。

解决方法：

• 临时调整：执行SET GLOBAL tmpdir = '/path/to/your/big/disk/dir';（确保目标目录有MySQL读写权限），然后重新执行ALTER语句。
• 永久生效：修改MySQL配置文件（my.cnf/my.ini），添加或修改tmpdir = /path/to/big/disk/dir，重启MySQL服务。

2. 调整InnoDB临时表相关配置 #

如果你的表是InnoDB引擎，ALTER操作可能会用到InnoDB的临时表空间，默认的ibtmp1文件可能存在大小限制：

• 查看当前InnoDB临时表配置：

  SHOW VARIABLES LIKE 'innodb_temp_data_file_path';
  

默认配置通常是ibtmp1:12M:autoextend，如果这个文件所在分区（一般和数据目录同分区）虽然总空间够，但ibtmp1因为某些原因无法自动扩展（比如文件系统限制），也会出问题。

解决方法：

• 修改配置文件，设置更宽松的临时表空间限制，比如：

  innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:10G
  

  （根据你的磁盘空间调整max值），然后重启MySQL。
• 另外，调大在线ALTER的日志上限：

  SET GLOBAL innodb_online_alter_log_max_size = 1073741824; -- 设置为1GB
  

这个参数控制ALTER过程中临时日志的大小，避免因日志溢出导致临时表空间不足。

3. 分批处理，避免一次性全表修改 #

3300万条记录的全表ALTER对临时空间要求极高，你可以拆分步骤，分批完成字段转换：

1. 先添加一个新的VARBINARY(32)字段：

   ALTER TABLE mytable ADD COLUMN myfield_new VARBINARY(32);
   

2. 分批将旧字段的值转换后写入新字段（假设你的表有自增主键id）：

   -- 每次处理10000条，循环执行直到所有记录处理完成
   UPDATE mytable 
   SET myfield_new = CAST(myfield AS VARBINARY(32)) 
   WHERE id BETWEEN 1 AND 10000;
   

   可以用脚本（比如Python/Shell）自动循环这个操作，每次更新不同的ID范围。
3. 最后替换原字段：

   ALTER TABLE mytable DROP COLUMN myfield;
   ALTER TABLE mytable CHANGE COLUMN myfield_new myfield VARBINARY(32);
   

这种方法每次操作的临时空间需求极小，几乎不会触发表满错误。

4. 检查文件系统inode是否耗尽 #

这个情况比较少见，但也可能发生：磁盘空间足够，但文件系统的inode（文件索引节点）用完了，导致无法创建新的临时文件。

• 用df -i命令检查对应分区的inode使用情况，如果IUse%接近100%，就需要清理该分区的小文件，或者调整文件系统的inode配置。

5. 使用在线表结构修改工具 #

如果上述方法都麻烦，可以用Percona Toolkit中的pt-online-schema-change工具，它能在线修改表结构，不需要锁表，并且会自动分批迁移数据，避免临时空间不足的问题：

pt-online-schema-change --alter "MODIFY myfield VARBINARY(32)" D=your_database,t=mytable --execute

注意：使用前要确保工具权限足够，并且备份好数据。

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