mysql - 将 mysql 列从 varchar 转换为 varbinary 出现 "table is full"错误

Question

我有一个包含大量记录(3300 万条)的表，并且有一个 VARCHAR(255) 字段，我想将其转换为 VARBINARY(32)。

每次我尝试这样做时，都会收到以下错误:

ERROR 1114 (HY000): The table '#sql-7a72_8' is full.

这是我使用的查询:

alter table mytable modify myfield VARBINARY(32); 

我的驱动器上有足够的空间(超过 12 GB，与整个数据库的大小相同)，因此我确信这不是磁盘空间问题。
我该如何解决这个问题？

Answer 1

首先找出 tmpdir 或 innodb_tmpdir 变量的值是什么，查看哪一个取决于您使用的引擎:

InnoDB:

SHOW VARIABLES LIKE 'innodb_tmpdir';

MyISAM:

SHOW VARIABLES LIKE 'tmpdir';

然后检查以验证该路径是否有足够的空间可供使用。例如，您有一个包含 2GB 数据的表，并且 innodb_tmpdir 配置为 /tmp。如果/tmp 卷配置为 512MB，MySQL 可能没有足够的空间来使用，因为它使用 tmpdir 来临时排序文件。

我建议不要在这里采用您的方法，因为您可能会导致数据丢失或损坏。相反，我建议向表中添加 VARBINARY 列，并使用显式转换为 BINARY 的数据填充它。然后删除旧列并将新列重命名为旧列的名称。它看起来像这样:

ALTER TABLE mytable ADD COLUMN myfield2 VARBINARY(32) AFTER myfield;

由于您希望将列截断为最多 32 个字符，因此填充 myfield2 的更新语句将如下所示:

UPDATE mytable SET myfield2 = CAST(LEFT(myfield, 32) AS BINARY);

一旦您对结果感到满意，您可以删除旧列:

ALTER TABLE mytable DROP COLUMN myfield;

然后重命名新列:

ALTER TABLE mytable RENAME COLUMN myfield2 TO myfield;

我还强烈建议进行备份并验证备份是否良好。首先将备份恢复到另一个 MySQL 实例。尝试在第二个测试实例上进行更改。这样您就可以确保您所做的任何更改都将完全符合您的预期。