How to swap two table names in MySQL_MySQL

Foreword

I encountered a problem in practice earlier. In a scenario similar to pt-osc, two table names need to be swapped. How can we ensure that everything is foolproof?

Analysis

Some people may think, isn’t it easy to change the table names? Just RENAME each other.

However, what we want is to complete the table name swap at the same time. If the table names are swapped one after another, some data writing may fail. What should we do?

Solved

In fact, it is not difficult. You can find the method from the MySQL manual, that is: Lock 2 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;

See, it’s actually very simple. Add table-level write locks to both tables at the same time, and then use ALTER syntax to rename them.

The above is all about how to swap the two table names in MySQL. I hope this article will be helpful to everyone in the use of MySQL.