MySQL同时重命名和创建表

Question

我需要重命名 MySQL 表，同时创建一个新的 MySQL 表。

有大量记录的关键活表。 ma​​ster_table 总是从脚本中插入记录。

需要备份主表，同时创建另一个同名的主表。

一般的SQL是这样的。

RENAME TABLE master_table TO backup_table;

Create table master_table (id,value) values ('1','5000');

是否有可能在执行上述查询期间记录丢失的数据？

有什么方法可以避免丢失记录？锁定主表等...

Answer 1

我所做的是以下内容。它不会导致停机、不会丢失数据，而且几乎是即时执行。

CREATE TABLE mytable_new LIKE mytable;

...possibly update the AUTO_INCREMENT of the new table...

RENAME TABLE mytable TO mytable_old, mytable_new TO mytable;

通过在一条语句中重命名两个表，它们将自动交换。当没有表接收写入时，任何数据都不可能写入“中间”。如果您不以原子方式执行此操作，则某些写入可能会失败。

RENAME TABLE 实际上是瞬时的，无论表有多大。您不必等待数据被复制。

如果表有一个自增主键，我想确保新表的起始 id 值大于旧表中的当前 id。在交换表名之前执行此操作。

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='mydatabase' AND TABLE_NAME='mytable';

我想为该值添加一些舒适的余量。您要确保插入到旧表中的 id 值不会超过您从 INFORMATION_SCHEMA 中查询的值。

更改新表以将此新值用于其下一个自动增量:

ALTER TABLE mytable_new AUTO_INCREMENT=<increased value>;

然后立即执行RENAME TABLE 来交换它们。一旦将新行插入到新的空表中，它将使用以增加的自动增量值开头的 id 值，如果您及时执行这些步骤，该值仍应大于插入旧表中的最后一个 id。