[MySQLFAQ] series-how to reconcile two table names

A student asked me, in a scenario similar to pt-osc, how can we ensure that the two table names are correct? The analysis estimated that other students laughed. Isn't table name correct easy? RENAME each other. 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?

A student asked me, in a scenario similar to pt-osc, how can we ensure that the two table names are correct? The analysis estimated that other students laughed. Isn't table name correct easy? RENAME each other. 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?

A student asked me, in a scenario similar to pt-osc, how can we ensure that the two table names are correct? The analysis estimated that other students laughed. Isn't table name correct easy? RENAME each other.

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?

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.

There are a lot of nonsense. Thank you for your patience :)