Truncate all tables in MySQL database that match a name pattern

Question

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 1

mysql -uuser -ppass --execute="SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME LIKE 'cache%'" | sed 1d | mysql -uuser -ppass db 

..this worked for me.. replace user, pass and db with your own.

Answer 2

Late answer... But better later, than never. To avoid unnecessary copying and pasting or additional shell scripting, the most agnostic approach -- at least with respect to MySQL and MariaDB -- to truncating a set of tables in a database or matching a pattern is via a stored procedure.

The following is a script used regularly to truncate all tables in the current database. The SELECT statement can be tailored to match patterns if more precise control is required.

DROP PROCEDURE IF EXISTS truncate_tables;

DELIMITER $$
CREATE PROCEDURE truncate_tables()
BEGIN
  DECLARE tblName CHAR(64);
  DECLARE done INT DEFAULT FALSE;
  DECLARE dbTables CURSOR FOR
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = (SELECT DATABASE());
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN dbTables;
  SET FOREIGN_KEY_CHECKS = 0;

  read_loop: LOOP
    FETCH dbTables INTO tblName;
    IF done THEN
      LEAVE read_loop;
    END IF;

    PREPARE stmt FROM CONCAT('TRUNCATE ', tblName);
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP read_loop;

  CLOSE dbTables;
  SET FOREIGN_KEY_CHECKS = 1;
END
$$

CALL truncate_tables();
DROP PROCEDURE IF EXISTS truncate_tables;

This example drops the store procedure after it is used. However, if this is a regular task, then it is better to just add it once by running everything before the $$ delimiter and executing

CALL truncate_tables();

on the target database.

Answer 3

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 4

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 5

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 6

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;

Answer 7

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 8

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 9

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 10

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 11

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

Answer 12

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.