MySQL FAQ Series-How to swap two table names

MySQL FAQ Series-How to swap two table names

problem

A classmate asked me, in a scenario similar to pt-osc, the two table names need to be swapped, how can I be sure of everything?

analysis

It is estimated that the other students laughed. Isn’t it easy to rename the table names? Rename each other.

However, what we want is to complete the table name swap at the same time. If it is reversed one after another, it may cause some data write failures. What should we do?

Reply

In fact, it is not difficult. You can find a method from the MySQL manual, that is: lock two tables at the same time, do not allow writing, and then swap the table names.

We usually only lock one table, so what should we do to lock two tables at the same time, you can use the following method:

LOCK TABLES t1 WRITE, t2 WRITE;
ALTER TABLE t1 RENAME TO t3;
ALTER TABLE t2 RENAME TO t1;
ALTER TABLE t3 RENAME TO t2;
UNLOCK TABLES;

As you can see, it's actually very simple. Add table-level write locks to the two tables at the same time, and then use the ALTER syntax to rename them.

A lot of nonsense, thank you for your patience to finish reading:)

Reference: https://cloud.tencent.com/developer/article/1005039 MySQL FAQ series-How to swap two table names-Cloud + Community-Tencent Cloud