How can I reconcile two MySQL table names?
Preface
I encountered a problem in the previous practice. In a scenario similar to pt-osc, we need to correct the two table names. How can we ensure that the two tables are safe?
Analysis
Some may think that it is not easy to RENAME a table name.
However, what we want is to complete table name reconciliation at the same time. If it is a successive pair, some data may fail to be written. What should we do?
Solution
In fact, it is not difficult to find a method from the MySQL manual, that is:Lock two tables at the same time, do not allow writing, and then tune the table name.
We usually only lock one table, So how should we lock two tables at the same time? The following method can be used:
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 is actually very simple. You can add a table-level write lock to the two tables at the same time, and then rename the table using the ALTER syntax.
The preceding section describes how to reconcile the names of two MySQL tables. I hope this article will help you with MySQL usage.