How To Truncate Table in MySQL

Sometimes you may need to delete all rows in a table in MySQL database. There are multiple ways to do it using TRUNCATE TABLE, DROP TABLE and DELETE statements. In this article, we will look at how to truncate table in MySQL using MySQL TRUNCATE TABLE statement.

 

How To Truncate Table in MySQL

Here’s how to truncate table in MySQL using TRUNCATE TABLE statement. Here’s the syntax of MySQL TRUNCATE TABLE statement.

TRUNCATE [TABLE] table_name;

In the above query, you need to specify your table name to be truncated, in place of table_name.

The keyword TABLE is optional after TRUNCATE.

Bonus Read : MySQL DROP VIEW

 

MySQL TRUNCATE TABLE Examples

Let’s say you have the table orders in MySQL.

mysql> create table daily_orders(id int, order_date date,amount int);

mysql> insert into daily_orders(id, order_date, amount) 
       values(1, '2020-07-01',250),(2,'2020-07-02',320),
       (3,'2020-07-03',450);

mysql> select * from daily_orders;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-07-01 |    250 |
|    2 | 2020-07-02 |    320 |
|    3 | 2020-07-03 |    450 |
+------+------------+--------+

Here’s the SQL query to truncate table in MySQL.

mysql> truncate table daily_orders;

mysql> select * from daily_orders;
Empty set (0.00 sec)

Bonus Read : How to Create Index in MySQL

 

MySQL TRUNCATE vs DELETE

TRUNCATE TABLE is similar to DELETE statement in MySQL. However, there are a few key differences. Here’s the difference between TRUNCATE and DELETE in MySQL.

MySQL DELETE

• DELETE is a Data manipulation language (DML) statement. It deletes one row at a time. So it is executed using row lock, and each row is locked for deletion
• DELETE operations are logged
• You can specify WHERE clause in DELETE statement to delete only specific rows in your table
• DELETE is slower than truncate since delete operations are logged

MySQL TRUNCATE

• TRUNCATE is a Data Description Language (DDL) command which locks the table for deletion but not the rows.
• TRUNCATE removes all data in table. So you cannot specify WHERE clause in TRUNCATE statement.
• TRUNCATE operations are not logged and are therefore much faster than DELETE statement.

Rollback is possible in both DELETE and TRUNCATE statements.

Bonus Read : How to Create Stored Procedure in MySQL

 

How to Truncate Table With Foreign Key

If your table has foreign key constraint, then when you run TRUNCATE statement for that table you will get the following error.

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key 
constraint fails

So you need to log into MySQL and disable Foreign Key checks before truncating tables, using the following command

SET FOREIGN_KEY_CHECKS=0;

After you truncate tables, re-enable foreign key checks

SET FOREIGN_KEY_CHECKS=1;

Bonus Read : How to Create User in MySQL

 

How to Truncate All Tables in MySQL

MySQL TRUNCATE TABLE allows you to delete only one table at a time. So if you want to delete multiple tables, you need to issue separate TRUNCATE TABLE commands. Here’s an example to truncate multiple tables table1, table2, table3, …

mysql> TRUNCATE TABLE table1;
mysql> TRUNCATE TABLE table2;
mysql> TRUNCATE TABLE table3;

Bonus Read : How to Create Database in MySQL

Here’s an SQL query to generate multiple TRUNCATE TABLE statements, using the table names in your database. Replace DATABASE_NAME in the following query with your database name.

mysql> SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
     FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('sample');
+------------------------------------------------------------+
| Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') |
+------------------------------------------------------------+
| TRUNCATE TABLE sample.a;                                   |
| TRUNCATE TABLE sample.calendar;                            |
| TRUNCATE TABLE sample.categories;                          |
| TRUNCATE TABLE sample.daily_orders;                        |
| TRUNCATE TABLE sample.login;                               |
| TRUNCATE TABLE sample.meeting;                             |
| TRUNCATE TABLE sample.order_status;                        |
| TRUNCATE TABLE sample.orders;                              |
| TRUNCATE TABLE sample.orders2;                             |
| TRUNCATE TABLE sample.orders3;                             |
| TRUNCATE TABLE sample.product_sales;                       |
| TRUNCATE TABLE sample.products;                            |
| TRUNCATE TABLE sample.sales;                               |
| TRUNCATE TABLE sample.user_data;                           |
| TRUNCATE TABLE sample.users;                               |
+------------------------------------------------------------+

Use the above query result to truncate all tables in database. Hopefully, now you can truncate table in MySQL

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!