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 TABLE statement allows you to delete all data in a table. Therefore it is like a DELETE statement without a WHERE clause. However in some cases, the MySQL TRUNCATE TABLE statement is more efficient than the DELETE statement.

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 DELETE statement especially for big tables.

If you are using other storage 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 TABLE does not use the DELETE statement therefore the DELETE triggers associated with the table will not be invoked.

MySQL TRUNCATE TABLE examples

First, create a new table named books for 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 < num DO   SET title = concat('Book title #',counter);   SET counter = counter + 1;   INSERT INTO books   Values(book_title); END WHILE; END$$ DELIMITER ;

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 DELETE statement.

TRUNCATE TABLE books;

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

原文链接：http://outofmemory.cn/mysql/mysql-truncate-table