Blogs

How To Truncate Table in MySQL – Ubiq BI

Posted on by admin

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

TRUNCATE vs

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.

MySQL

DELETE

  • DELETE is a Data Manipulation Language (DML) statement. Deletes one row at a time. therefore it is executed by row locking and each row

    • is locked for deletion delete operations are logged

  • you can specify the where clause in the delete statement to delete only specific rows from the

    • table

  • delete is slower than truncate because delete operations are logged mysql truncate

truncate

  • 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

TRUNCATE statements.

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

SET FOREIGN_KEY_CHECKS=1;

Further reading:

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!

admin