In this tutorial, you will learn how to use TRUNCATE command in MySQL to truncate all the tables in a given database.
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.
MySQL Truncate Table Command
The syntax for the
TRUNCATE command in MySQL is:
TRUNCATE [TABLE] table_name;
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
NOTE: A Table truncation is an implicit commit!!
An implicit commit occurs automatically when certain statements are executed, such as the
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:
MySQL TRUNCATE Table Example
Let us start by creating a database:
create database development;
Next, switch to the created database as:
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:
Finally, select the data from the table:
select * from programming_languages;
Finally, truncate the table as:
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.
You discovered how to perform a table truncation operation in MySQL in this post using the TRUNCATE TABLE statement.
Few points to note:
TRUNCATEstatement 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.
TRUNCATEstatement is not always as efficient as it seems. For small tables with a few hundred records, using
TRUNCATEmay be faster than using the
DELETEstatement 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.
- Using the
TRUNCATEstatement 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.
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.