如何在 MySQL 中的一次原子操作中重命名两个表

2022-01-06 00:00:00 transactions mysql database-migration

我需要在一次原子操作中重命名两个表，以便用户永远无法看到处于中间状态的数据库.

I need to rename two tables in one atomic operation so that user will never be able to see the database in its intermediate state.

我正在使用 MySQL 并注意到这种情况在 文档:

I'm using MySQL and noticed that this case is perfectly described in the documentation:

13.3.3 导致隐式提交的语句

13.3.3 Statements That Cause an Implicit Commit

本节中列出的语句(以及它们的任何同义词)隐式结束当前会话中活动的任何事务，就好像你在执行语句之前已经完成了 COMMIT

The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement

[...]

定义或修改的数据定义语言 (DDL) 语句数据库对象.ALTER DATABASE ... 升级数据目录名称，更改事件、更改程序、更改服务器、更改表、更改视图、创建数据库、创建事件、创建索引、创建程序、创建服务器、创建表、创建触发器、创建视图、删除数据库、删除事件、删除索引、删除程序、删除服务器、删除表、删除触发、删除视图、安装插件(从 MySQL 5.7.6 开始)、重命名表、截断表，卸载插件(从 MySQL 5.7.6 开始).

Data definition language (DDL) statements that define or modify database objects. ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, INSTALL PLUGIN (as of MySQL 5.7.6), RENAME TABLE, TRUNCATE TABLE, UNINSTALL PLUGIN (as of MySQL 5.7.6).

但也许有某种解决方法或类似的方法?

But maybe there's some kind of workaround or something like this?

我的情况是这样的:

  • 我在名为 current
    • 的表中有一个当前数据集
  • 我在名为 next
    • 的表中收集了一个新数据集
  • 我需要将 current 表重命名为 current_%current_date_time% 并将 next 表重命名为 current> 在一个原子操作中
  • I have a current data set in the table named current
  • I gathered a new data set in the table named next
  • I need to rename the current table to the current_%current_date_time% and the next table to the current in one atomic operation

推荐答案

好吧，简单...

RENAME TABLE current TO current_20151221, next TO current;

如手册中所述.它说这是一个原子操作.只是为了澄清这一点，隐式提交与此无关.那是一个不同的故事.这只是说，这些语句结束了一个开放的事务.

as is stated in the manual. There it says that it's an atomic operation. Just to clear this up, implicit commits have nothing to do with it. That's a different story. That just says, that those statements end an open transaction.

相关文章