sql - 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.