对表太大的MySQL ALTER TABLE无法复制 [英] MySQL ALTER TABLE on tables too large to duplicate

问题描述

在向MyISAM表添加列时，MySQL将创建>复制>删除>重命名.我有一个足够大的表，无法在服务器的剩余可用空间中重复该表.尝试添加一列将导致磁盘填充.时间在这里不是问题，表可用性也不是问题，只是磁盘空间.

When adding a column to a MyISAM table, MySQL will Create>Copy>Drop>Rename. I have a table which is sufficiently large that it cannot be duplicated in the server's remaining free space. Attempting to add a column then results in the disk filling. Time is not an issue here, neither is table availability, just disk space.

将数据复制到另一台服务器，进行截断，更改和复制的简短操作，是否可以在不创建临时表并复制所有数据的情况下将列添加到MyISAM表中 ?

Short of copying the data to another server, truncating, altering, and copying back, is there a way to add a column to a MyISAM table without it creating a temporary table and duplicating all the data?

推荐答案

您可以创建一个新的空表，手动将数据分块移动(将INSERT插入new，从旧的DELETE中删除一定数量的行)，然后删除旧表并重命名新表.本质上是MySQL的工作，但是移动数据而不是复制数据，这将使您使用较少的空间.

You could create a new, empty table, manually move the data over in chunks (INSERT into new, DELETE from old a certain number of rows), drop the old table and rename the new table. Essentially what MySQL does, but moving the data over instead of copying it, which should allow you to use less space.

这篇关于对表太大的MySQL ALTER TABLE无法复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！