If you have more than one database on your server, you may want to specify the db as well.
e.g. to clear Drupal cache tables
SELECT DISTINCT CONCAT(
"TRUNCATE TABLE ",
TABLE_SCHEMA,
".",
TABLE_NAME,
";" )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE "cache_%"
AND TABLE_SCHEMA = 'dbname';
This results in sql like...
TRUNCATE TABLE dbname.cache_admin_menu;
TRUNCATE TABLE dbname.cache_block;
etc.
Giving two advantages,
- You can run this sql anywhere regardless of the currently selected
database.
- You'll be sure that you're truncating the tables on the
correct database.
See @falperez answer if foreign key checks get in the way of your mass truncate, (although of course they won't for drupal cache clearing)