How to Truncate All MySQL Tables

1 year ago
Olivia Parker
1 minute

To empty all tables in the database, you can use the following command:

  1. Firstly, connect to the database.
mysql -u 用户名 -p 数据库名
  1. Then use the following command to truncate all tables:
SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT(table_name) INTO @tables
  FROM information_schema.tables
  WHERE table_schema = DATABASE();
SELECT IFNULL(@tables,'dummy') INTO @tables;

SET @tables = CONCAT('TRUNCATE TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

This will clear all tables in the database. Please note that this will delete all data in the tables, but will not delete the table structures. If you need to delete the table structures, you can use the DROP TABLE command.

bannerAds