用拼接语句总是会出问题
-- 1. 禁用外键约束（防止级联删除失败）[1]()
SET SESSION FOREIGN_KEY_CHECKS = 0; -- 2. 生成并执行删除语句（替换 your_database_name）
SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_NAME, '`;') -- 预览语句[2]()
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'free_working_dev';-- 3. 正式执行删除（单语句批量操作）[6]()
SET GROUP_CONCAT_MAX_LEN = 1000000; -- 防止超长语句截断
SET @sql = (SELECT GROUP_CONCAT('DROP TABLE IF EXISTS `', TABLE_NAME, '` SEPARATOR "; "') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'free_working_dev'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;-- 4. 恢复外键检查
SET SESSION FOREIGN_KEY_CHECKS = 1; 结果，由于表太多，超过了拼接长度，会得到类似下面的报错
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR "; ",DROP TABLE IF EXISTS `City` SEPARATOR "; ",DROP TABLE IF EXISTS `' at line 1所以，一个一劳永逸的办法，临时创建一个存储过程，绕过这个坑，调用完这个存储过程，再把它删掉
-- 创建存储过程
DELIMITER // CREATE PROCEDURE drop_tables_in_schema()
BEGIN -- 声明变量 DECLARE done INT DEFAULT 0; DECLARE table_name_var VARCHAR(255); -- 声明游标 DECLARE table_cursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'free_working_dev'; -- 声明异常处理 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 打开游标 OPEN table_cursor; -- 循环处理 read_loop: LOOP -- 获取表名 FETCH table_cursor INTO table_name_var; -- 判断是否结束 IF done THEN LEAVE read_loop; END IF; -- 生成并执行 DROP TABLE 语句 SET @sql = CONCAT('DROP TABLE ', table_name_var); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; -- 关闭游标 CLOSE table_cursor;
END // DELIMITER ; -- 调用存储过程
CALL drop_tables_in_schema(); -- 删除存储过程（可选）
DROP PROCEDURE IF EXISTS drop_tables_in_schema; 
)
