Delete data from all tables in MYSQL

Question

I have 100 tables, 40,000 rows in each table. I want to go into MySQL and delete all rows from all tables.

...in 1 statement, if possible?

I want to keep the database and tables.

Answer 1

Easiest method to truncate all tables while retaining schema.

mysqldump -d -uuser -ppass --add-drop-table databasename > databasename.sql  mysql -uuser -ppass databasename < databasename.sql 

Not sure if it will retain stored procedures as they are not in use where I work, but I use this regularly to reset databases.

The -d switch on mysqldump means "don't dump data."

The --add-drop-table prepends a DROP TABLE statement to every CREATE TABLE in the dump.

Answer 2

The most robust way to clear all tables in a database was submitted by kostanos in How to remove all MySQL tables from the command-line without DROP database permissions?

Since the code below clears all tables in the current database you might want to select a different database before you proceed.

USE DATABASE_YOU_WANT_TO_CLEAR; 

Following snippet will remove data from the tables even in presence of foreign key constraints. You might also want to double check the list of tables before actual cleanup, just in case if you forgot to select proper database.

-- save current foreign key settings and disable foreign key checks SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET GROUP_CONCAT_MAX_LEN=32768;  -- you never know how people name their tables SET @tables = NULL; SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables   FROM information_schema.tables   WHERE table_schema = (SELECT DATABASE());  -- operates on the current DB SELECT IFNULL(@tables,'dummy') INTO @tables;  -- avoid error if there are no tables  -- At this point you might want to double check the list of tables -- to be cleared.  Run SELECT @tables; to do so.  SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables); PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;