Skip to content

Instantly share code, notes, and snippets.

Embed
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
@bigbrainz

This comment has been minimized.

Copy link

bigbrainz commented Sep 10, 2014

this is awesome,thanks,

@dipenpatel235

This comment has been minimized.

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

@ruslan-gennadievich

This comment has been minimized.

Copy link

ruslan-gennadievich commented Oct 12, 2017

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:

SET FOREIGN_KEY_CHECKS=0;
Truncate your tables and change it back to

SET FOREIGN_KEY_CHECKS=1;

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

@lineboy1011

This comment has been minimized.

Copy link

lineboy1011 commented Feb 1, 2019

'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
You can’t perform that action at this time.