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!
