MySQL Truncate All Tables

Table truncation refers to the process of deleting all of the records from a table without deleting the table itself. Since table truncation does not remove the table schema, it is often faster and more efficient than dropping and re-creating the table, especially for large tables with a lot of data.

In this tutorial, you will learn how to use TRUNCATE command in MySQL to truncate all the tables in a given database.

MySQL Truncate Table Command

The syntax for the TRUNCATE command in MySQL is:

TRUNCATE [TABLE] table_name;

Where, table_name is the name of the table that you want to truncate. Although the TABLE keyword is optional, it is good to specify it to avoid confusion with the TRUNCATE() function.

NOTE: A Table truncation is an implicit commit!!

An implicit commit occurs automatically when certain statements are executed, such as the TRUNCATE or DROP statements. These statements cannot be rolled back, so they cause an automatic commit of the current transaction. Any changes made by the current transaction are saved to the database and cannot be undone.

Although it may appear as if a TRUNCATE command is just a DELETE command without a WHERE clause, it will not register to any DELETE triggers in that database. Similarly, the statement will return that the number of affected rows is 0, unlike a delete clause.

NOTE: Ensure to remove all the FOREIGN KEYS constraints from the table you wish to truncate. If the foreign keys are not removed, the command will fail.

In some cases, you can disable foreign key checking with the command:

SET FOREIGN_KEY_CHECKS=0;

MySQL TRUNCATE Table Example

Let us start by creating a database:

create database development;

Next, switch to the created database as:

use development;

Create a test table:

CREATE TABLE programming_languages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    year_released INT UNSIGNED NOT NULL,
    creator VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL
);

Add sample data:

INSERT INTO programming_languages (name, year_released, creator, type)
VALUES
    ('C', 1972, 'Dennis Ritchie', 'procedural'),
    ('Java', 1995, 'James Gosling', 'object-oriented'),
    ('Python', 1991, 'Guido van Rossum', 'interpreted'),
    ('C++', 1985, 'Bjarne Stroustrup', 'object-oriented'),
    ('JavaScript', 1995, 'Brendan Eich', 'interpreted'),
    ('C#', 2000, 'Microsoft', 'object-oriented'),
    ('PHP', 1994, 'Rasmus Lerdorf', 'interpreted'),
    ('Ruby', 1995, 'Yukihiro Matsumoto', 'interpreted'),
    ('Swift', 2014, 'Apple', 'object-oriented'),
    ('Go', 2009, 'Google', 'compiled');

Finally, select the data from the table:

select * from programming_languages;

Result:

id|name      |year_released|creator           |type           |
--+----------+-------------+------------------+---------------+
 1|C         |         1972|Dennis Ritchie    |procedural     |
 2|Java      |         1995|James Gosling     |object-oriented|
 3|Python    |         1991|Guido van Rossum  |interpreted    |
 4|C++       |         1985|Bjarne Stroustrup |object-oriented|
 5|JavaScript|         1995|Brendan Eich      |interpreted    |
 6|C#         |         2000|Microsoft         |object-oriented|
 7|PHP       |         1994|Rasmus Lerdorf    |interpreted    |
 8|Ruby      |         1995|Yukihiro Matsumoto|interpreted    |
 9|Swift     |         2014|Apple             |object-oriented|
10|Go        |         2009|Google            |compiled       |

Finally, truncate the table as:

TRUNCATE programming_languages

The command will remove all the data from the programming_languages table while preserving the table structure and properties.

To remove all other tables in the database, you can repeat the TRUNCATE command while replacing the target table.

Conclusion

You discovered how to perform a table truncation operation in MySQL in this post using the TRUNCATE TABLE statement.

Few points to note:

1. The TRUNCATE statement cannot be rolled back, so once you have truncated a table, you cannot undo the operation. This means that if you accidentally truncate the wrong table or realize that you need to keep the data in the table, you will not be able to recover the deleted records.
2. The TRUNCATE statement is not always as efficient as it seems. For small tables with a few hundred records, using TRUNCATE may be faster than using the DELETE statement to delete individual records. However, the DELETE statement can sometimes be faster for large tables with millions of records because it can be more efficient to delete records in smaller batches.
3. Using the TRUNCATE statement can cause problems if you have foreign key constraints on the table. If a table has foreign keys that reference other tables, and you truncate the table, it will violate the foreign key constraints and prevent you from inserting new records into the other tables. To avoid this problem, you will need to disable the foreign key constraints before truncating the table and then enable them again afterward.

Therefore, while TRUNCATE can be a valuable and efficient way to delete all the records from a table, it is essential to consider these potential disadvantages before using them in your database.