Sometimes you may need to delete all rows from a table in the MySQL database. There are several ways to do this using the TRUNCATE TABLE, DROP TABLE, and DELETE statements. In this article, we will see how to truncate the table in MySQL using the MySQL TRUNCATE TABLE statement.
How to truncate the table in MySQL Here’s how
to truncate the table in MySQL
using the TRUNCATE TABLE statement. This is the syntax of the MySQL TRUNCATE TABLE statement.
TRUNCATE [TABLE] table_name;
In the previous query, you specify the name of the table to truncate, rather than table_name.
The TABLE keyword is optional after TRUNCATE.
Further reading: MySQL DROP VIEW
MySQL TRUNCATE TABLE Examples Let’s say you
have the table commands in MySQL. MySQL
> create table daily_orders(id int, order_date date,quantity 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 is the SQL query to truncate the table in MySQL.
mysql> truncate table daily_orders; mysql> select * from daily_orders; Empty set (0.00 sec)
Bonus Read : How to create an index
in MySQL MySQL
DELETE TRUNCATE TABLE is similar to the DELETE statement in MySQL
. However, there are some key differences. Here is the difference between TRUNCATE and DELETE in MySQL.
- DELETE is a Data Manipulation Language (DML) statement. Deletes one row at a time. therefore it is executed by row locking and each row
- you can specify the where clause in the delete statement to delete only specific rows from the
- delete is slower than truncate because delete operations are logged mysql truncate
is locked for deletion delete operations are logged
- is a Data Description Language (DDL) command that locks the table for deletion, but not the rows.
- TRUNCATE deletes all data from the table. Therefore, you cannot specify the WHERE clause in the TRUNCATE statement.
- TRUNCATE operations are not logged and are therefore much faster than the DELETE statement.
Reversal is possible in the DELETE and
Further reading: How to create a stored procedure in MySQL
How to truncate
the table with foreign key
If your table has a foreign key constraint, when you execute the TRUNCATE statement for that table, you will get the following error.
ERROR 1217 (23000): Unable to delete or update a parent row: A foreign key constraint fails
Therefore, you must log in to 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
How to create a user in MySQL
How to truncate all tables in MySQL
MySQL TRUNCATE TABLE allows you to delete only one table at a time. Therefore, if you want to delete multiple tables, you must issue separate TRUNCATE TABLE commands. Here is an example to truncate multiple tables table1, table2, table3, …
mysql> TRUNCATION TABLE 1; mysql> TRUNCATE TABLE table2; mysql> TRUNCATE TABLE table3;
Further reading: How to create
a database in MySQL
Here is an SQL query to generate multiple TRUNCATE TABLE statements, using the table names in your database. Replace DATABASE_NAME in the following query with the name of the database.
mysql> SELECT Concat(‘TRUNCATE TABLE ‘,table_schema,’.’,TABLE_NAME, ‘;’) OF INFORMATION_SCHEMA. TABLES where table_schema in (‘sample’); +-+ | Concat(‘TRUNCATE TABLE ‘,table_schema,’.’,TABLE_NAME, ‘;’) | +-+ | TRUNCATION TABLE sample.a; | | TRUNCATION TABLE sample.calendar; | | TRUNCATION TABLE sample.categories; | | TRUNATED TABLE sample.daily_orders; | | TRUNCATION TABLE sample.login; | | TRUNCATION TABLE sample.meeting; | | TRUNATED TABLE sample.order_status; | | TRUNCATION TABLE sample.orders; | | TRUNCATION TABLE sample.orders2; | | TRUNCATION TABLE samples.orders3; | | TRUNATED TABLE sample.product_sales; | | TRUNCATION TABLE samples.products; | | TRUNCATION TABLE sample.sales; | | TRUNATED TABLE sample.user_data; | | TRUNCATE TABLE sample.users; | +-+
Use the result of the previous query to truncate all tables in the database. Hopefully, you can now truncate the table in MySQL
Ubiq makes it easy to visualize data in minutes and monitor dashboards in real time. Try it today!