如何在MySQL的一次原子操作中重命名两个表 [英] How to rename two tables in one atomic operation in MySQL

问题描述

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

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起)，重命名表，TRUNCATE TABLE，UNINSSTALL PLUGIN(从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.

这篇关于如何在MySQL的一次原子操作中重命名两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！