Truncate all tables in MySQL database that match a name pattern

Truncate all tables in MySQL database that match a name pattern

I need to clear all my inventory tables.

I've tried this:

SELECT 'TRUNCATE TABLE ' + TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME LIKE 'inventory%'  

But I get this error:

Truncated incorrect DOUBLE value: 'TRUNCATE TABLE ' Error Code 1292  

if this is the correct way, then what am I doing wrong?

Answer by Lukasz Lysik for Truncate all tables in MySQL database that match a name pattern

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

You should then take every row of the result set and call it as the SQL statement. You can do it by coping and pasting the separated results to the command window.

But much better solution is to write some program which will make the query above and loop through the results and make every query.

Use PHP for that or any other scripting language. Many examples even here on SO.

Answer by ???u for Truncate all tables in MySQL database that match a name pattern

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.

Answer by bichonfrise74 for 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  

Answer by Matthias for Truncate all tables in MySQL database that match a name pattern

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

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. ;)

Answer by Elias for Truncate all tables in MySQL database that match a name pattern

Here is a little improvement on the above SQL statement.

SELECT DISTINCT concat("TRUNCATE TABLE ", TABLE_NAME, ";")   FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_NAME LIKE "cache%"  

Notice the DISTINCT command in the beginning. This will make the SELECT result show only once the the tables that match your LIKE criteria.

Answer by falperez for Truncate all tables in MySQL database that match a name pattern

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;'  

Answer by chim for Truncate all tables in MySQL database that match a name pattern

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,

1. You can run this sql anywhere regardless of the currently selected database.
2. 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)

Answer by AshwinP for Truncate all tables in MySQL database that match a name pattern

Try this statement, It will give you single line of all tables and you can copy that statements and run to execute all:

SELECT DISTINCT REPLACE(GROUP_CONCAT("TRUNCATE TABLE ", TABLE_NAME, ";"), ',', '')  FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_NAME LIKE "cache%";  

Answer by Dave Cohen for Truncate all tables in MySQL database that match a name pattern

SELECT REPLACE(GROUP_CONCAT("TRUNCATE TABLE ", TABLE_NAME, ";"), ',', '')   FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE "cache%";  

This is based on AshwinP's answer. I had to remove DISTINCT and add WHERE TABLE_SCHEMA=DATABASE() for it to work for me.

The command does not truncate tables, but returns a one-liner that will. Copy and paste the one-liner into the mysql client.

Answer by Pavel Zendulo for Truncate all tables in MySQL database that match a name pattern

very nice example of generating and executing statements USING concat I just read minute ago:

http://www.mysqltutorial.org/mysql-drop-table

-- set table schema and pattern matching for tables    SET @schema = 'classicmodels';  SET @pattern = 'test%';    -- build dynamic sql (DROP TABLE tbl1, tbl2...;)    SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')  INTO @droplike FROM information_schema.tables WHERE @schema = database()  AND table_name LIKE @pattern;    -- display the dynamic sql statement    SELECT @droplike;      -- execute dynamic sql    PREPARE stmt FROM @dropcmd;  EXECUTE stmt;  DEALLOCATE PREPARE stmt;  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72