Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
mysql: truncate all tables in one command line
mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done
Copy link

this is awesome,thanks,

Copy link

dipenpatel235 commented Aug 30, 2016

Using password Below command helpful.

mysql -Nse 'show tables' DATABASE -uMYUSER -pMYPASSWORD | while read table; do mysql -e "truncate table $table" DATABASE -uMYUSER -pMYPASSWORD ; done

Copy link

That happens if there are tables with foreign keys references to the table you are trying to drop/truncate.

Before truncating tables All you need to do is:

Truncate your tables and change it back to


mysql -Nse 'show tables' DB_NAME -uMYSQL_USER -pMYSQL_PASSWORD | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table $table;SET FOREIGN_KEY_CHECKS=1;" DB_NAME -uMYSQL_USER -pMYSQL_PASSWORD ; done

Copy link

'while' is not recognized as an internal or external command,
operable program or batch file.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment