Truncate all tables in MySQL database that match a name pattern

user_171136 0
Viewed 63235

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?

mysql sql
12 Answers

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.

user_33042380 
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.

user_131640570
Related