How to swap table names in cast that both tables already created?

Question

Let's suppose that we have two tables new and current (both are innodb). We should atomically truncate current table contents and fill with new content from new table.

Possible ways:

• swap table names RENAME TABLE, but it can't help because both tables are already created
• start transaction; truncate current table; insert into current select from new; commit transaction - it is ok, but is it possible to make all this actions in transcational way?
• create on fly third table and use it like a bridge

What is the best practice?

UPD: I suppose this will work perfectly: RENAME TABLE current TO xxx, new TO current; xxx TO new

Answer 1

You'll need to rename the tables using a single MySQL statement so it's performed as a single mysql atomic operation, else you can cause errors for programs trying to access one of the tables while swapping them between the various names.

But it also sounds like you need the "new" table to be empty to accept new records at the end of the operation.

CREATE TABLE new_empty LIKE new;
RENAME TABLE current TO old, new to current, new_empty TO new;
DROP TABLE old;

This is much more efficient than doing a "truncate current" and an "insert into current select * from new" because you don't copy any records, it only creates a new table structure (with its indexes), renames three files and drops the old table (by deleting the two files from the database directory).

https://dev.mysql.com/doc/refman/5.7/en/rename-table.html