?用拼接語句總是會出問題
-- 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; 
)
