{"id":2202,"date":"2013-05-09T23:11:29","date_gmt":"2013-05-10T07:11:29","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2202"},"modified":"2023-10-07T04:45:56","modified_gmt":"2023-10-07T11:45:56","slug":"mysql-truncate-table","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-truncate-table\/","title":{"rendered":"MySQL TRUNCATE TABLE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL TRUNCATE TABLE<\/code> statement to delete all data in a table.<\/p>\n\n\n\n

Introduction to the MySQL TRUNCATE TABLE statement<\/h2>\n\n\n\n

In MySQL, the TRUNCATE TABLE<\/code> statement allows you to delete all rows from a table.<\/p>\n\n\n\n

Here’s the basic syntax of the TRUNCATE TABLE<\/code> statement:<\/p>\n\n\n

TRUNCATE<\/span> [TABLE<\/span>] table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the table from which you want to delete all rows after the TRUNCATE TABLE<\/code> keywords.<\/p>\n\n\n\n
The TABLE<\/code> keyword is optional. However, it is a good practice to use the TABLE<\/code> keyword to distinguish between the TRUNCATE TABLE<\/code> statement and the TRUNCATE()<\/a><\/code> function.<\/p>\n\n\n\n
If there is any FOREIGN KEY<\/a><\/code> constraints from other tables that reference the table that you truncate, the TRUNCATE TABLE<\/code> statement will fail.<\/p>\n\n\n\n
Since a truncate operation causes an implicit commit, it cannot be rolled back. Also, the TRUNCATE TABLE<\/code> statement does not fire DELETE<\/code> triggers associated with the table that is being truncated.<\/p>\n\n\n\n
The TRUNCATE TABLE<\/code> statement resets the value in the AUTO_INCREMENT column <\/a>to its initial value if the table has an AUTO_INCREMENT<\/code> column.<\/p>\n\n\n\n
Unlike a DELETE<\/code> statement, the number of rows affected by the TRUNCATE TABLE<\/code> statement is 0, which should be interpreted as no information.<\/p>\n\n\n\n
Functionally, the TRUNCATE TABLE<\/code> statement is like a DELETE<\/a><\/code> statement without a WHERE<\/a><\/code> clause that deletes all rows from a table: <\/p>\n\n\n
DELETE FROM table_name;<\/code><\/span><\/pre>\n\n\n
Or a sequence of DROP TABLE<\/a><\/code> and CREATE TABLE<\/a><\/code> statements:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> table_name;\n\nCREATE<\/span> TABLE<\/span> table_name (\n  \/* ... *\/<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, the TRUNCATE TABLE<\/code> statement is more efficient than the DELETE<\/code> statement because it drops and recreates the table instead of deleting rows one by one.<\/p>\n\n\n\n
MySQL TRUNCATE TABLE example<\/h2>\n\n\n\n
Let’s take an example of using the TRUNCATE TABLE<\/code> statement.<\/p>\n\n\n\n
First, create a new table<\/a> named books<\/code> for the demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> books (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    title VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n)  ENGINE<\/span>=INNODB<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
DELIMITER $$\nCREATE<\/span> PROCEDURE<\/span> load_book_data(IN<\/span> num<\/span> INT<\/span>(4<\/span>))\nBEGIN<\/span>\n\tDECLARE<\/span> counter INT<\/span>(4<\/span>) DEFAULT<\/span> 0<\/span>;\n\tDECLARE<\/span> book_title VARCHAR<\/span>(255<\/span>) DEFAULT<\/span> ''<\/span>;\n\n\tWHILE counter < num DO<\/span>\n\t  SET<\/span> book_title = CONCAT<\/span>('Book title #'<\/span>,counter);\n\t  SET<\/span> counter = counter + 1<\/span>;\n\n\t  INSERT<\/span> INTO<\/span> books(title)\n\t  VALUES<\/span>(book_title);\n\tEND<\/span> WHILE<\/span>;\nEND<\/span>$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, load 10,000 rows into the books<\/code> table. It will take a while.<\/p>\n\n\n
CALL<\/span> load_book_data(10000<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, check the data in the books<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> books;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, use the TRUNCATE TABLE<\/code> statement to delete all rows from the books<\/code> table:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> books;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you can compare the performance of the TRUNCATE TABLE<\/code> with the DELETE<\/code> statement.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the TRUNCATE TABLE<\/code> statement to delete all rows from a table efficiently.<\/li>\n\n\n\n
  • The TRUNCATE TABLE<\/code> statement resets the AUTO_INCREMENT<\/code> counter.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Next, insert dummy data into the books<\/code> table by using the following stored procedure<\/a>:<\/p>\n\n\n