MySQL 如何TRUNCATE有外鍵限制的資料表

若一張資料表有外鍵限制(Foreign key constraints)，則TRUNCATE該資料表時會發生下錯誤：

Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint (`dbname`.`table1`, CONSTRAINT `fk_table2_table1`)

節錄官方敘述如下：

TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

解決方式為先將外鍵限制關閉，TRUNCATE後再開啟外鍵限制如下

SET FOREIGN_KEY_CHECKS = 0; -- 關閉外鍵限制
TRUNCATE TABLE dbname.table1;
SET FOREIGN_KEY_CHECKS = 1; -- 開啟外鍵限制

SHOW VARIABLES LIKE 'foreign_key_checks'; -- 顯示外鍵限制狀態

參考：

• foreign_key_checks
• How to truncate a foreign key constrained table?
• TRUNCATE TABLE Syntax
• Using FOREIGN KEY Constraints