How MySQL empties all the tables in a database

Transferred from: http://blog.csdn.net/zhangzhizhen1988/article/details/8432146

MySQL Clear table is a very important operation, but also one of the most common operations, the following for you to detail the implementation of the MySQL empty table, I hope to be able to help you.

Method 1: Rebuild the Library and table

One. Export only table structure

Export the entire database structure (no data included)
Mysqldump-h localhost-uroot-p123456- D Database > Dump.sql

Export a single data table structure (no data included)
Mysqldump-h localhost-uroot-p123456- D database table > Dump.sql

Two. Export only table data

Export Entire Database data

Mysqldump-h localhost-uroot-p123456 -t database > Dump.sql

Three. Export Structure + data

Export the entire database structure and data
Mysqldump-h localhost-uroot-p123456 Database > Dump.sql

Export a single data table structure and data
Mysqldump-h localhost-uroot-p123456 database table > Dump.sql

Method 2: Generate an empty SQL for all tables
Mysql-n-S Information_schema-e "select CONCAT (' TRUNCATE TABLE ', table_name, '; ') From TABLES WHERE table_schema= ' eab12 ' "
The output results are as follows:
TRUNCATE TABLE authgroupbindings;
TRUNCATE TABLE authgroups;
TRUNCATE TABLE authusers;
TRUNCATE TABLE Corpbadcustominfo;
TRUNCATE TABLE Corpsmsblacklisyinfo;
TRUNCATE TABLE Corpsmsfilterinfo;
TRUNCATE TABLE Corpsmsinfo;
TRUNCATE TABLE Eabasereginfos;
TRUNCATE TABLE Eacorpblob;
TRUNCATE TABLE Eacorpinfo;
....
....
This is more perfect:
Mysql-n-S Information_schema-e "select CONCAT (' TRUNCATE TABLE ', table_name, '; ') From TABLES WHERE table_schema= ' eab12 ' | MySQL EAB12
This clears all the tables in the EAB12.
But if there is a foreign key, it is likely to error. So we need to add a-f
Mysql-n-S Information_schema-e "select CONCAT (' TRUNCATE TABLE ', table_name, '; ') From TABLES WHERE table_schema= ' eab12 ' | Mysql-f EAB12
Execute several times until the error is not.

The above is the implementation of the MySQL empty table.

How MySQL empties all the tables in a database