Truncate all tables in MySQL database that match a name pattern

If you are using command line, you might want to try something like this.

mysql -u [user] -p[password] -e 'use [database]; show tables' | perl -lane 'print "truncate table $F[0]" if /^inventory/i' > [database].sql

Use concat:

SELECT concat('TRUNCATE TABLE `', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

This will of course only generate SQL which you need to copy and run yourself.

SELECT 'SET FOREIGN_KEY_CHECKS = 0;'
UNION
SELECT DISTINCT concat( "TRUNCATE TABLE ", TABLE_NAME, ";" )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydbname'
UNION
SELECT 'SET FOREIGN_KEY_CHECKS = 1;'

I know it's an older post already but maybe the following example is helpful for someone who needs to truncate multiple tables from linux command line or from within a shell script:

mysql -p<secret> --execute="SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<database>' AND FIND_IN_SET(TABLE_NAME,'your_table_a,your_table_b,your_table_c')" | sed 1d | mysql -p<secret> <database>

Given that you need to replace the strings in brackets with your own values it was working for me. Also replace the 'your_table_a,your_table_b,your_table_c' with a comma-separated list of your tables. ;)