Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction?

Does this mean that the only way to have a fresh temporary table without committing the current transaction is to run (after above DDL statement):

DELETE FROM temp-table-name;

This TRUNCATE auto-commit behavior caused a rather nasty bug in my app -- the rollback didn't go all the way up because of the "TRUNCATE barrier" ;)


It is true that CREATE TEMPORARY TABLE IF NOT EXISTS.. is DDL but did you TRUNCATE TABLE is also itself DDL ??? I have mentioned this many times over the years ...

The MySQL Documentation on TRUNCATE TABLE says the following:





  • TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

  • Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”




  • The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

So, to answer your question, the way you are handling it is the only way.

  • I get that part of truncate being a DDL. The reason why I got into this is because CREATE TEMP TABLE (clearly a DDL) is an exception and does not commit, yet truncating the very TEMP table is committing. TO me, this is error prone, that's all. If they could make one work that way, they should have made the the other also work the same way. Meaning both CREATE TEMP and TRUNCATE (temp) should not commit. As a full-stack developer, not a DB expert, this one nuance can really get you. – Slawomir Aug 5 '16 at 18:57

You can use DROP TEMPORARY TABLE instead of TRUNCATE, then CREATE TEMPORARY TABLE(...) again, it will not commit the transaction but you gain the speed of TRUNCATE statement.

"CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used" Statements That Cause an Implicit Commit

  • Welcome to the DBA StackExchange. Your answer is "on the money" when it comes to using CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE. The documentation says CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as ALTER TABLE and CREATE INDEX, which do cause a commit.). This means TRUNCATE TABLE will cause implicit commit becasue it is not design to look under the hood and see that the table is temporary. Nice Work Around. +1 !!! – RolandoMySQLDBA Apr 24 at 15:27


That way that table would take part in the transaction.

Your Answer

