MySQL 批量删除表的实现方式

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

基本思路

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

注意事项

1. 删除操作无法撤销：确保作操之前完全备份数据。
2. SQL 语句长度限制：MySQL 默认情况下，有关语句长度可能超出限制，需要调整 group_concat_max_len 参数。
3. 确保足够权限：确保足够权限执行“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()

总结

在使用 MySQL 批量删除表时，需要根据实际场景选择适合的方法：

• 对表量少的情况，可使用动态 SQL。
• 对表量多的情况，可使用流转或脚本执行。

最后，确保删除操作前备份数据，避免事故。

📌 开发者必备工具：
在 Tool.tushuoit.com 发现免费在线工具集！
推荐 App Store 截图生成器、应用图标生成器 和 Chrome插件-强制开启复制-护眼模式-网页乱码设置编码，让您的开发和运营工作更轻松高效。
乖猫记账,乖猫记账界面美观，聊天式记账方式新颖，文本或语音输入记账快捷方便。自动分类功能和微信绑定实用，统计分析功能有助于用户了解收支情况。