MySQL 查看表結構與清空表數據命令大全：從基礎到批量自動化

一、查看表結構的多種方式

1. 基礎命令

-- 進入庫後
SHOW TABLES;                    -- 列出所有表
DESC table_name;                -- 結構簡覽
DESCRIBE table_name;            -- 等同 DESC
SHOW COLUMNS FROM table_name;   -- 詳細輸出
SHOW CREATE TABLE table_name;   -- 完整建表語句

2. 進階：information_schema 元數據查詢

USE information_schema;
SELECT * FROM COLUMNS WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='table_name';

適用場景：跨庫對比結構、自動化腳本提取欄位信息。

3. 查看警告信息

-- 執行可能產生警告的操作後
SHOW WARNINGS;
-- 輸出示例：
-- Level: Warning, Code: 1265, Message: Data truncated for column 'name' at row 3

二、清空表數據：TRUNCATE vs DELETE 對比

1. 單表清空

TRUNCATE TABLE table_name;
-- 或
DELETE FROM table_name;

2. 批量清空全庫表（生產環境慎用！）

方法一：生成 SQL 再執行

# 生成 TRUNCATE 語句
mysql -N -s information_schema -e "
SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM TABLES WHERE TABLE_SCHEMA='db_name';" > truncate.sql

# 執行（-f 強制繼續忽略外鍵錯誤）
mysql -f db_name < truncate.sql

方法一條龍管道（推薦）

mysql -N -s information_schema -e "
SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM TABLES WHERE TABLE_SCHEMA='db_name'" | mysql -f db_name

關鍵參數：

• -N：不輸出列名
• -s：靜默模式（無表格框）
• -f：強制執行（外鍵錯誤不中斷）

方法三：外鍵依賴處理（多次執行直到成功）

for i in {1..5}; do
  mysql -N -s information_schema -e "
  SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME, ';')
  FROM TABLES WHERE TABLE_SCHEMA='db_name'" | mysql -f db_name
  [ $? -eq 0 ] && break
  sleep 1
done

三、生產環境清空表最佳實踐

1. 備份優先：mysqldump -d db_name > struct_backup.sql 保留結構
2. 停寫入：應用層切換維護模式或刪除寫入路由
3. 禁用外鍵：SET FOREIGN_KEY_CHECKS=0; 清空後 SET FOREIGN_KEY_CHECKS=1;
4. 重置自增：ALTER TABLE t AUTO_INCREMENT=1;（TRUNCATE 自動完成）
5. 驗證：SELECT COUNT(*) FROM t; 確認為 0

四、常見坑點

• TRUNCATE 非事務安全：誤執行無法回滾，務必雙人複核
• 外鍵循環依賴：單次執行失敗，需多輪執行或按拓撲序刪除
• DELETE FROM t WHERE 1=1 不重置自增值：後續插入 ID 從斷點繼續
• 大表 DELETE：產生大量 Binlog、Undo Log，導致主從延遲，分批刪除或用 TRUNCATE

掌握 DESC/SHOW COLUMNS 查結構，TRUNCATE 清數據，information_schema 做自動化，是 MySQL 運維的三板斧。生產環境操作前請三思：有備份嗎？有回滾預案嗎？確認是 TRUNCATE 還是 DELETE？