| 特性 | TRUNCATE TABLE | DELETE FROM (無 WHERE) |
|---|---|---|
| 執行速度 | 極快 (元數據操作，重建表) | 慢 (逐行刪除，記錄 Binlog) |
| 事務性 | 隱式提交，不可回滾 | 可回滾 (需在事務中) |
| 自增計數器 | 重置為 1 (或初始值) | 保持不變 |
| 觸發器 | 不觸發 ON DELETE | 觸發每行 ON DELETE |
| 外鍵約束 | 被引用表不可 TRUNCATE | 受外鍵限制 (RESTRICT/CASCADE) |
| 權限需求 | DROP + CREATE 權限 | DELETE 權限 |
| Binlog 記錄 | 記錄為 DDL (STATEMENT 格式) | 記錄每行刪除 (ROW 格式) |
| 適用場景 | 清空全表、重置測試數據、日志輪轉 | 需保留自增值、需觸發器、需回滾 |
-- 清空表
TRUNCATE TABLE `user_log`;
-- 等效於 (但更快)
DROP TABLE `user_log`;
CREATE TABLE `user_log` LIKE `user_log`; -- 結構克隆DELETE FROM `table_name` WHERE condition [ORDER BY ...] [LIMIT row_count];-- 1. 單條件刪除
DELETE FROM `order` WHERE status = 0 AND created_at < '2023-01-01';
-- 2. 多表關聯刪除 (刪除無用戶的訂單)
DELETE o FROM `order` o
LEFT JOIN `user` u ON o.user_id = u.id
WHERE u.id IS NULL;
-- 3. 刪除重複數據 (保留最小 id)
DELETE t1 FROM `user` t1
INNER JOIN `user` t2
WHERE t1.email = t2.email AND t1.id > t2.id;
-- 4. 分批刪除大量數據 (避免長事務鎖表)
-- 存儲過程示例
DELIMITER //
CREATE PROCEDURE batch_delete_old_logs()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE batch_size INT DEFAULT 1000;
REPEAT
DELETE FROM `access_log`
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT batch_size;
SET done = ROW_COUNT();
-- 可選：SLEEP(0.1) 降低主從延遲
UNTIL done < batch_size END REPEAT;
END //
DELIMITER ;-- 增加列
ALTER TABLE `user` ADD COLUMN `phone` VARCHAR(16) NOT NULL DEFAULT '' AFTER `email`;
ALTER TABLE `user` ADD COLUMN `nickname` VARCHAR(32) NOT NULL DEFAULT '' FIRST;
-- 修改列定義 (類型/長度/默認值/註釋/非空)
ALTER TABLE `user` MODIFY COLUMN `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態';
-- 重命名列 (需完整重新指定定義)
ALTER TABLE `user` CHANGE COLUMN `phone` `mobile` VARCHAR(16) NOT NULL DEFAULT '';
-- 刪除列
ALTER TABLE `user` DROP COLUMN `mobile`;-- 增加普通索引
ALTER TABLE `user` ADD INDEX `idx_status` (`status`);
-- 增加唯一索引
ALTER TABLE `user` ADD UNIQUE KEY `uk_mobile` (`mobile`);
-- 增加主鍵 (表無主鍵時)
ALTER TABLE `user` ADD PRIMARY KEY (`id`);
-- 增加聯合索引
ALTER TABLE `order` ADD INDEX `idx_user_status` (`user_id`, `status`);
-- 刪除索引
ALTER TABLE `user` DROP INDEX `idx_status`;-- 增加外鍵
ALTER TABLE `order`
ADD CONSTRAINT `fk_order_user`
FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
ON UPDATE CASCADE ON DELETE RESTRICT;
-- 刪除外鍵
ALTER TABLE `order` DROP FOREIGN KEY `fk_order_user`;-- 轉換引擎 (重建表，鎖表)
ALTER TABLE `user` ENGINE = InnoDB;
-- 修改自增起始值
ALTER TABLE `user` AUTO_INCREMENT = 10000;
-- 修改表字符集 (僅影響新列)
ALTER TABLE `user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改表及所有列字符集 (重建表)
ALTER TABLE `user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改表註釋
ALTER TABLE `user` COMMENT = '用戶核心信息表';-- 單表重命名
RENAME TABLE `user` TO `user_v1`;
-- 多表原子交換 (常用於發布回滾)
RENAME TABLE `user` TO `user_old`, `user_new` TO `user`;
-- 跨庫移動
RENAME TABLE `db1`.`user` TO `db2`.`user`;-- 1. 更新統計信息 (優化器決策依據)
ANALYZE TABLE `user`;
-- 2. 整理碎片、回收空間 (重建表，InnoDB 等同於 ALTER TABLE ... ENGINE=InnoDB)
OPTIMIZE TABLE `user`;
-- 3. 檢查表損壞
CHECK TABLE `user` [QUICK | FAST | MEDIUM | EXTENDED | CHANGED];
-- 4. 修復表 (主要針對 MyISAM，InnoDB 自動崩潰恢復)
REPAIR TABLE `user` [QUICK | EXTENDED | USE_FRM];-- 導出 (需 FILE 權限、secure_file_priv 目錄)
SELECT id, username, email
FROM `user`
WHERE status = 1
INTO OUTFILE '/var/lib/mysql-files/user_export.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
-- 導入
LOAD DATA INFILE '/var/lib/mysql-files/user_export.csv'
INTO TABLE `user`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, username, email); -- 可指定列映射順序# 安裝 Percona Toolkit
yum install -y percona-toolkit
# 無鎖增加列
pt-online-schema-change \
--alter "ADD COLUMN phone VARCHAR(16) NOT NULL DEFAULT ''" \
D=app_db,t=user \
--execute \
--max-load "Threads_running=100" \
--critical-load "Threads_running=200" \
--chunk-size 1000 \
--progress time,30-- 僅授予必要權限
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_rw'@'10.0.%';
GRANT ALTER, CREATE, INDEX ON app_db.* TO 'dev_ops'@'10.0.%'; -- 變更結構權限分離
GRANT TRUNCATE ON app_db.user_log TO 'cron_job'@'10.0.%'; -- 僅清空日志表權限掌握上述操作的語法細節、性能特徵與適用邊界，能讓你在日常開發、線上變更、故障恢復中游刃有餘，避免「一條 DELETE 搞掛主庫」的慘劇。
