将mysql列从varchar转换为varbinary会出现“表已满”错误

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

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

ERROR 1114(HY000):表'#sql-7a72_8'已满 .

这是我使用的查询:

alter table mytable modify myfield VARBINARY(32);

我的驱动器上有足够的空间(超过12 GB,与我的整个数据库大小相同),所以我确定它不是磁盘空间问题 .
我怎么解决这个问题?

回答(1)

3 years ago

首先找出 tmpdir OR 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语句将如下所示:

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实例 . 尝试更改第二个测试实例 . 通过这种方式,您可以确保所做的任何更改都与您期望的完全一致 .