如何批量清空表名前缀相同的多张数据表内的数据

执行所有操作前请务必提前备份全量数据，避免误操作导致不可逆的数据丢失

SQL原生语法不支持直接通过表名过滤批量执行删除/清空操作，需要查询系统表生成对应执行语句，以下是主流数据库的实现方案：

MySQL 实现 #

• 方式一：手动生成执行语句
  先运行以下查询生成所有清空表的命令，确认语句无误后复制批量执行即可：

SELECT CONCAT('TRUNCATE TABLE ', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema = '替换为你的数据库名' 
AND table_name LIKE 'Test_%';

如果需要支持事务回滚，可将语句中的TRUNCATE TABLE替换为DELETE FROM。

• 方式二：存储过程自动执行
  如果需要一键执行，可创建存储过程实现：

DELIMITER //
CREATE PROCEDURE BatchClearTestTables()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tableName VARCHAR(255);
    -- 声明游标遍历匹配的表名
    DECLARE cur CURSOR FOR 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = '替换为你的数据库名' 
        AND table_name LIKE 'Test_%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 拼接并执行SQL
        SET @execSql = CONCAT('TRUNCATE TABLE `', tableName, '`');
        PREPARE stmt FROM @execSql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程执行清空
CALL BatchClearTestTables();

注意：TRUNCATE属于DDL操作，执行速度远快于DELETE，会重置表的自增ID，但无法回滚，执行前请确认数据无需恢复。

PostgreSQL 实现 #

• 方式一：生成手动执行语句

SELECT 'TRUNCATE TABLE ' || quote_ident(tablename) || ';' 
FROM pg_tables 
WHERE schemaname = '替换为你的模式名' 
AND tablename LIKE 'Test_%';

• 方式二：DO语句自动执行

DO $$
DECLARE
    table_rec RECORD;
BEGIN
    FOR table_rec IN 
        SELECT tablename FROM pg_tables 
        WHERE schemaname = '替换为你的模式名' 
        AND tablename LIKE 'Test_%' 
    LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(table_rec.tablename);
    END LOOP;
END $$;

SQL Server 实现 #

• 方式一：生成手动执行语句

SELECT 'TRUNCATE TABLE ' + QUOTENAME(name) + ';' 
FROM sys.tables 
WHERE schema_id = SCHEMA_ID('替换为你的模式名') 
AND name LIKE 'Test_%';

• 方式二：动态SQL自动执行

DECLARE @execSql NVARCHAR(MAX) = '';
SELECT @execSql += 'TRUNCATE TABLE ' + QUOTENAME(name) + ';' 
FROM sys.tables 
WHERE schema_id = SCHEMA_ID('替换为你的模式名') 
AND name LIKE 'Test_%';
EXEC sp_executesql @execSql;

内容的提问来源于stack exchange，提问作者Alexander