我有一个包含大量记录(3300万)的表,我有一个VARCHAR(255)字段,我想将其转换为VARBINARY(32) .
每次我尝试这样做我都会收到以下错误:
ERROR 1114(HY000):表'#sql-7a72_8'已满 .
这是我使用的查询:
alter table mytable modify myfield VARBINARY(32);
我的驱动器上有足够的空间(超过12 GB,与我的整个数据库大小相同),所以我确定它不是磁盘空间问题 .我怎么解决这个问题?
首先找出 tmpdir OR innodb_tmpdir 变量的值是什么,要查看哪一个取决于您使用的是哪个引擎:
tmpdir
innodb_tmpdir
InnoDB的:
SHOW VARIABLES LIKE 'innodb_tmpdir';
MyISAM数据:
SHOW VARIABLES LIKE 'tmpdir';
然后检查以确认此路径有足够的空间可以工作 . 例如,你有一个包含2GB数据的表,innodb_tmpdir被配置为 /tmp . 如果/ tmp卷配置为512MB,则MySQL可能没有足够的空间来处理,因为它使用tmpdir作为临时排序文件 .
/tmp
我建议不要在这里使用您的方法,因为您可能会导致数据丢失或损坏 . 相反,我建议在表中添加一个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实例 . 尝试更改第二个测试实例 . 通过这种方式,您可以确保所做的任何更改都与您期望的完全一致 .
1 回答
首先找出
tmpdir
ORinnodb_tmpdir
变量的值是什么,要查看哪一个取决于您使用的是哪个引擎:InnoDB的:
MyISAM数据:
然后检查以确认此路径有足够的空间可以工作 . 例如,你有一个包含2GB数据的表,innodb_tmpdir被配置为
/tmp
. 如果/ tmp卷配置为512MB,则MySQL可能没有足够的空间来处理,因为它使用tmpdir作为临时排序文件 .我建议不要在这里使用您的方法,因为您可能会导致数据丢失或损坏 . 相反,我建议在表中添加一个VARBINARY列,用显式转换为BINARY的数据填充它 . 然后删除旧列并将新列重命名为旧列的名称 . 它看起来像这样:
由于您希望将列截断为最多32个字符,因此填充myfield2的update语句将如下所示:
对结果感到满意后,您可以删除旧列:
然后重命名新列:
我还强烈建议备份和验证备份是好的 . 首先将备份还原到另一个MySQL实例 . 尝试更改第二个测试实例 . 通过这种方式,您可以确保所做的任何更改都与您期望的完全一致 .