MariaDB Truncate Table

Summary: in this tutorial, you will learn how to use the MariaDB truncate table statement to delete all rows from a table.

Overview of the MariaDB truncate table statement

The MariaDB truncate table statement deletes all rows from a table.

Logically, a truncate table statement is equivalent to a delete statement without a where clause that deletes all rows from a table.

But the truncate table statement is much more efficient than the delete in some situations because the truncate table statement drops and recreates the table instead of removing rows one by one. This reduces the overhead of writing to logs.

The following illustrates the syntax of the truncate table statement:

truncate [table] table_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the table from which you want to delete all rows after the truncate table keywords.

Note that the table keyword is optional. However, you should always use the table keyword to avoid the unwanted confusion between the truncate table statement and the truncate() function.

The truncate table statement will fail if the truncating table has a foreign key constraint.

Since the truncate table statement causes an implicit commit, it cannot be rolled back.

If a table has an auto_increment column, the truncate table statement will reset the starting value of this column to 1.

Unlike a delete statement, the number of affected rows by a truncate table statement is always zero, which should be understood as no information. In addition, the truncate table statement does not fire the delete triggers associated with the truncated table.

MariaDB truncate table statement example

First, create a new table called items for the testing purpose:

create table items (
    id int auto_increment primary key,
    name varchar(255) not null
);
Code language: SQL (Structured Query Language) (sql)

Next, create a stored procedure that inserts dummy data to the items table:

delimiter $$

create procedure load_items(in row_count int)
begin
    declare counter int default 0;
    declare str varchar(255) default '';

    while counter < row_count do
       set str = concat('Item #',counter);
       set counter = counter + 1;

       insert into items(name)
       values(str);
    end while;
end$$

delimiter ;
Code language: SQL (Structured Query Language) (sql)

Then, insert 10,000 rows into the items table by calling the load_items stored procedure:

call load_items(10000);
Code language: SQL (Structured Query Language) (sql)

After that, view the data from the items table:

select * from items;
Code language: SQL (Structured Query Language) (sql)

Finally, use the truncate table statement to delete all rows from the items table:

truncate table items;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to delete all rows from a table faster and more efficient by using the MariaDB truncate table statement.