MySQL 批量删除表的实现方式

井九

发布于 2025-01-09 16:06:46

12000

代码可运行

文章被收录于专栏：四楼没电梯四楼没电梯

运行总次数：0

代码可运行

在实际应用中，有时候需要批量删除以特定前缀命名的表（如数据清理或数据处理时生成的临时表）。这里分享实现这一功能的方法和注意事项，以便避免实现过程中出现问题。

基本思路

使用 information_schema.tables 获取相关表名。
根据表名生成 DROP TABLE 语句。
通过动态 SQL 或采用流转一个一个删除。

注意事项

删除操作无法撤销：确保作操之前完全备份数据。
SQL 语句长度限制：MySQL 默认情况下，有关语句长度可能超出限制，需要调整 group_concat_max_len 参数。
确保足够权限：确保足够权限执行“SHOW TABLES”和“DROP TABLE”操作。

实现方法

1. 使用动态 SQL 批量删除表

这个方法适合对表量较少的情况：

-- 增大 GROUP_CONCAT_MAX_LEN 以防止 SQL 超长
SET SESSION group_concat_max_len = 1000000;

-- 获取表名并生成删除语句
SET @prefix = 'data_2024121';
SET @sql = (
    SELECT GROUP_CONCAT(CONCAT('DROP TABLE ', table_name) SEPARATOR '; ')
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
    AND table_name LIKE CONCAT(@prefix, '%')
);

-- 执行生成的 DROP TABLE 语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2. 使用流转逐表删除

这个方法适合对表量较多的情况，通过流转一个一个删除：

-- 先删除已存在的存储过程
DROP PROCEDURE IF EXISTS DropTablesWithPrefix;

-- 重新创建过程
DELIMITER $$

CREATE PROCEDURE DropTablesWithPrefix()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
        AND table_name LIKE 'data_2024121%';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql = CONCAT('DROP TABLE ', tbl_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 输出日志（可选）
        SELECT CONCAT('Deleted table: ', tbl_name) AS message;
    END LOOP;

    CLOSE cur;
END$$

DELIMITER ;

-- 调用过程
CALL DropTablesWithPrefix();

3. 通过脚本实现

如果想使用脚本来控制操作，例如使用 Python 进行删除：

Python 脚本示例

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)
cursor = conn.cursor()

# 获取以特定前缀命名的表名
cursor.execute("SHOW TABLES LIKE 'data_2024121%'")
tables = cursor.fetchall()

# 逐表删除
for (table_name,) in tables:
    try:
        cursor.execute(f"DROP TABLE `{table_name}`")
        print(f"Deleted table: {table_name}")
    except mysql.connector.Error as err:
        print(f"Error deleting table {table_name}: {err}")

# 提交并关闭连接
conn.commit()
cursor.close()
conn.close()