Atomic Transactional Replacement of a Table in MySQL

Even with AUTOCOMMIT off a DROP TABLE or CREATE TABLE statement will cause an implicit commit in MySQL.

So if you drop your table of (say) aggregated data and then create a new one even if you’re theoretically in a transaction there will be time when clients of the database see no table and time when they see an empty table.

The solution is to use RENAME TABLE.

CREATE TABLE replacement_table (...) AS SELECT ... FROM ...;
CREATE TABLE IF NOT EXISTS current_table (id INT); -- Just in case this is the first run and the table doesn't exist yet so RENAME TABLE doesn't fail.
RENAME TABLE current_table TO old_table, replacement_table TO current_table;

No client of the database will ever see a database that doesn’t contain an existing and populated current_table.