mysql clears the data of the specified table under the specified database and retains the table structure

background¶

For some purposes, it is necessary to frequently delete all data of a specified series of table names, and the index number starts from 1.

Solution¶

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `table1`;
TRUNCATE `table2`;
TRUNCATE `table3`;
TRUNCATE `table4`;
TRUNCATE `table5`;
TRUNCATE `table6`;
TRUNCATE `table7`;
SET FOREIGN_KEY_CHECKS=1;

Just change table1, table2...table7 to the table name

Delete and truncate¶

There are two ways to delete all data in a MySQL database table.

TRUNCATE TABLE tablename¶

This will quickly delete all data in the table. In MySQL, the table has actually been deleted and recreated, thus improving the query speed. The number of deleted rows of the returned MyISAM table is zero; for INNODB, it returns the actual number of deleted rows.

TRUNCATE TABLE deletes all rows in the table, but the table structure and its columns, constraints, indexes, etc. remain unchanged. The count value used for the new row identification is reset to the seed of the column.

If you want to keep the mark count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP TABLE statement.

DELETE FROM tablename¶

This will also delete all data in the table, but it is not as fast as using the "TRUNCATE TABLE" method. In MySQL>= 4.0, the number of rows deleted is returned; in MySQL 3.23, the number returned is always zero.

Reasons for slow delete deletion:

The DELETE statement deletes one row at a time, and records one entry for each row deleted in the transaction log.

TRUNCATE TABLE deletes data by releasing data pages used to store table data, and only records the release of pages in the transaction log.