MySQL uses the truncate command to quickly clear all tables in a database

1. Execute the select statement first to generate all truncate statements

Statement format:

select CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') from INFORMATION_SCHEMA.TABLES where table_schema in ('数据库1','数据库2');

Take the database named dbname as an example and execute the select statement:

mysql> select CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') from INFORMATION_SCHEMA.TABLES where table_schema in ('dbname'); 
+------------------------------------------------------------+ 
| CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') | 
+------------------------------------------------------------+ 
| truncate TABLE dbname.ABOUTUSINFO; | 
| truncate TABLE dbname.ABUTMENT; | 
| truncate TABLE dbname.ABUTMENTFILE; | 
| truncate TABLE dbname.ACHVORG; | 
| truncate TABLE dbname.WORKFLOWNODE; | 
| truncate TABLE dbname.ZONESERVICE; | 
| truncate TABLE dbname.ZONESERVICEFILE; | 
+------------------------------------------------------------+ 
7 rows in set 

mysql> 

2. Replace the “|” before and after each truncate statement with a blank character

Use a text editor (such as Notepad++) to replace the "|" before and after each truncate statement with a blank character to facilitate copying and executing multiple statements at once.

Before replacement:

After replacement:

3. Copy the truncate statement to the mysql command line and execute it

Copy the truncate statement to the mysql command line for execution. You can copy multiple statements at a time for execution.

mysql> truncate TABLE dbname.ZONESERVICE;  
Query OK, 0 rows affected 
mysql> 

This will clear all tables in the database. It's simple~

Comparison between truncate, drop, and delete

As mentioned above, truncate is very similar to delete and drop. In fact, there are still significant differences between these three. The following is a brief comparison of the similarities and differences between the three.

• truncate and drop are DDL statements and cannot be rolled back after execution; delete is a DML statement and can be rolled back.
• truncate can only be applied to tables; delete and drop can be applied to tables, views, etc.
• Truncate will clear all rows in the table, but the table structure and its constraints, indexes, etc. remain unchanged; drop will delete the table structure and the constraints, indexes, etc. on which it depends.
• Truncate resets the table's auto-increment value; delete does not.
• Truncate does not activate delete triggers associated with the table; delete does.
• After truncate, the space occupied by the table and index will be restored to the initial size; the delete operation will not reduce the space occupied by the table or index, and the drop statement will release all the space occupied by the table.

This is the end of this article about how to use MySQL truncate command to quickly clear all tables in a database. For more information about MySQL truncate to clear database tables, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!