mysql truncate if_MySQL TRUNCATE TABLE语句介绍

Summary: in this tutorial, you will learn how to use the MySQL TRUNCATE TABLE statement to delete all data in a table.

Introduction to MySQL TRUNCATE TABLE statement

The MySQL TRUNCATE TABLEstatement allows you to delete all data in a table. Therefore it is like a DELETEstatement without a WHERE clause. However in some cases, the MySQL TRUNCATE TABLE statement is more efficient than the DELETEstatement.

The syntax of the MySQL TRUNCATE TABLE statement is as follows:TRUNCATE TABLE table_name

You specify table name after the TRUNCATE TABLE clause to delete all data in the table.

If you are using InnoDB tables, MySQL will check if there is any foreign key constraint available in the tables before deleting data.If the table has any foreign key constraint, the TRUNCATE TABLE statement deletes rows one by one. If the foreign key constraint has DELETE CASCADE action, the corresponding rows in the child tables are also deleted.

If the foreign key constraint does not specify the DELETE CASCADE action, the TRUNCATE TABLE deletes rows one by one, and it will stop and issue an error when it encounters a row that is referenced by a row in a child table.

If the table does not have any foreign key constraint, the TRUNCATE TABLE statement drops the table and recreates a new empty one with the same definition, which is faster and more efficient than using the DELETEstatement especially for big tables.

If you are using otherstorage engines, the TRUNCATE TABLE statement just drops and recreates a new table.

Notice that the TRUNCATE TABLE statement resets auto increment value to 0 if the table has an AUTO_INCREMENTcolumn.

In addition, the TRUNCATE TABLEdoes not use the DELETEstatement therefore the DELETE triggers associated with the table will not be invoked.

MySQL TRUNCATE TABLE examples

First, create a new table named booksfor the demonstration:CREATE DATABASE dbdemo; CREATE TABLE books( id int auto_increment primary key,         title varchar(255) not null )ENGINE=InnoDB;

Second, populate data for the books table by using the following stored procedure:DELIMITER $$ CREATE PROCEDURE load_book_data(IN num int(4)) BEGIN DECLARE counter int(4) default 0; DECLARE book_title varchar(255) default ''; WHILE counter 

Third, load 10000 rows into the books table. It will take a while.CALL load_book_data(10000)

Fourth, use the TRUNCATE TABLE statement to see how fast it performs in comparison with the DELETEstatement.TRUNCATE TABLE books;

In this tutorial, we have shown you how to use the MySQL TRUNCATE TABLEstatement to delete all data from tables efficiently especially for large tables.

打开App，阅读手记