A2-03-14.DDL-MySQL TRUNCATE TABLE
转载自：http://www.mysqltutorial.org/mysql-truncate-table/
（存储过程部分验证失败，待随后再次验证。）
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, in terms of functionality, The TRUNCATE TABLE statement is like a DELETE statement 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:
|
1
|
TRUNCATE TABLE table_name;
|
You specify the table name, which you want to remove all data, after the TRUNCATE TABLE clause.
The TABLE keyword is optional. However, you should use it to distinguish between the TRUNCATE TABLEstatement and the TRUNCATE function.
If you are using InnoDB tables, MySQL will check if there are any foreign key constraints available in the tables before deleting data. The following are cases:
- If the table has any foreign key constraints, the
TRUNCATE TABLEstatement deletes rows one by one. If the foreign key constraint hasDELETE CASCADEaction, the corresponding rows in the child tables are also deleted. - If the foreign key constraint does not specify the DELETE CASCADE action, the
TRUNCATE TABLEdeletes 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 TABLEstatement drops the table and recreates a new empty one with the same structure, which is faster and more efficient than using theDELETEstatement 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 zero if the table has an AUTO_INCREMENT column.
In addition, the TRUNCATE TABLE statement 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:
|
1
2
3
4
5
6
|
CREATE DATABASE dbdemo;
CREATE TABLE books(
id int auto_increment primary key,
title varchar(255) not null
)ENGINE=InnoDB;
|
Next, populate data for the books table by using the following stored procedure:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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 ;
|
Then, load 10,000 rows into the books table. It will take a while.
|
1
|
CALL load_book_data(10000);
|
After that, check the data in the books table:
|
1
2
|
SELECT *
FROM books;
|
Finally, use the TRUNCATE TABLE statement to see how fast it performs in comparison with the DELETEstatement.
|
1
|
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.
备注：原文中的存储过程应该修改为（但调用时出现异常，待随后确认）：
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 book_title = concat('Book title #',counter);
SET counter = counter + 1;
INSERT INTO books
Values(book_title);
END WHILE;
END$$
DELIMITER ;
