詳解 MySQL 的三個刪除語句：DROP、TRUNCATE 、DELETE
一、前言
在數據庫中，SQL作為一種常用的數據庫編程語言，扮演著至關重要的角色。SQL不僅可以用于創建、修改和查詢數據庫，還可以通過DROP、DELETE和TRUNCATE等語句來刪除數據。這些語句是SQL語言中的最常用的命令，且它們有著不同的含義和使用場景。
本文以GaussDB數據庫為平臺，將詳細介紹SQL中DROP、TRUNCATE和DELETE等語句的含義、使用場景以及注意事項，幫助讀者更好地理解和掌握這些常用的數據庫操作命令。
二、GaussDB的 DROP & TRUNCATE & DELETE 簡述
1、簡述
- DROP語句可以刪除整個表，包括表結構和數據；
- TRUNCATE語句則可以快速地刪除表中的所有數據，但不刪除表結構。
- DELETE語句可以刪除表中的數據，不包括表結構；
2、命令比對
大類 | DROP | TRUNCATE | DELETE |
SQL類型 | DDL | DDL | DML |
刪除內容 | 刪除表的所有數據，包括表結構、索引和權限等 | 刪除表中所有數據，或指定分區的數據 | 刪除表的全部或部分（+條件）數據 |
執行速度 | 速度最快 | 速度中等 | 速度最慢 |
Tip：在GaussDB數據庫中，DROP是用于定義或修改數據庫中的對象的命令之一。對象主要包括：庫、模式、表空間、表、索引、視圖、存儲過程、函數、加密秘鑰等，本次只針對其對表的操作。
三、GaussDB的DROP TABLE命令及示例
1、功能描述
DROP TABLE的功能是用來刪除已存在的Table。
2、語法
DROP TABLE [IF EXISTS] [db_name.]table_name;說明：SQL中加[IF EXISTS] ，可以防止因表不存在而導致執行報錯。
參數：db_name：Database名稱。如果未指定，將選擇當前database。table_name：需要刪除的Table名稱。
3、示例
以下示例演示DROP命令的使用，依次執行如下SQL語句：
--刪除整個表course
DROP TABLE IF EXISTS course
--創建course表
CREATE TABLE course(cid VARCHAR(10),cname VARCHAR(10),teid VARCHAR(10));
--初始化數據
INSERT INTO course VALUES('01' , '語文' , '02');
INSERT INTO course VALUES('02' , '數學' , '01');
INSERT INTO course VALUES('03' , '英語' , '03');
--3條記錄
SELECT count(1) FROM course;
--刪除整個表
DROP TABLE IF EXISTS course
--查看結果，表不存在（表結構及數據不存在）
SELECT count(1) FROM course;1）DROP TABLE，提示表不存在
2）創建并初始化一張實驗表
圖片
3）DROP TABLE 執行成功
4）查看執行結果
四、GaussDB的TRUNCATE命令及示例
1、功能描述
從表或表分區中移除所有數據，TRUNCATE快速地從表中刪除所有行。它和在目標表上進行無條件的DELETE有同樣的效果，但由于TRUNCATE不做表掃描，因而快得多, 且使用的系統和事務日志資源少。在大表上操作效果更明顯。
TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。
2、語法
TRUNCATE [TABLE] table_name;或：
ALTER TABLE [IF EXISTS] table_name TRUNCATE PARTITION { partition_name | FOR ( partition_value [, ...] ) }參數：table_name：需要刪除數據的Table名稱。partition_name：需要刪除的分區表的分區名稱。partition_value：需要刪除的分區表的分區值。
3、示例1
以下示例演示TRUNCATE命令的使用：
--創建course表
DROP TABLE IF EXISTS course;
CREATE TABLE course(cid VARCHAR(10),cname VARCHAR(10),teid VARCHAR(10));
--初始化數據
INSERT INTO course VALUES('01' , '語文' , '02');
INSERT INTO course VALUES('02' , '數學' , '01');
INSERT INTO course VALUES('03' , '英語' , '03');
--3條記錄
SELECT count(1) FROM course;
--清空表
TRUNCATE TABLE course;
--或
TRUNCATE course;
--0條記錄
SELECT count(1) FROM course;1）創建實驗表并初始化數據
2）TRUNCATE TABLE執行成功
3）查看執行結果
4、示例2
以下示例演示TRUNCATE命令的刪除分區表數據：
--創建列表分區（LIST）
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
product_id INT,
quantity INT
) PARTITION BY LIST (customer_id) (
PARTITION p1 VALUES (100),
PARTITION p2 VALUES (200),
PARTITION p3 VALUES (300),
PARTITION p4 VALUES (400),
PARTITION p5 VALUES (500)
);
--插入測試數據
INSERT INTO orders(id,customer_id,order_date,product_id,quantity)VALUES(1001,100,date'20230822',1,10);
INSERT INTO orders(id,customer_id,order_date,product_id,quantity)VALUES(1002,100,date'20230822',2,20);
INSERT INTO orders(id,customer_id,order_date,product_id,quantity)VALUES(1003,100,date'20230822',3,30);
INSERT INTO orders(id,customer_id,order_date,product_id,quantity)VALUES(1004,200,date'20230822',4,40);
--查看分區p1、p2的數據
SELECT * FROM orders WHERE customer_id IN (100,200);
--或
--根據分區名稱查詢
SELECT * FROM orders PARTITION(p2);
--清空分區p1。
ALTER TABLE orders TRUNCATE PARTITION p1;
--或者
--清空分區p2=200。
ALTER TABLE orders TRUNCATE PARTITION for (200);
--查看分區p1、p2的數據
SELECT * FROM orders WHERE customer_id IN (100,200);1）創建實驗表并初始化
2）根據分區進行刪數據
五、GaussDB的DELETE命令及示例
1、功能描述
從指定的表里刪除滿足WHERE子句的行。如果WHERE子句不存在，將刪除表中所有行，結果只保留表結構。
2、注意事項
- 不支持DELETE語句中使用LIMIT。應使用WHERE條件明確需要更新的目標行。
- 不支持在單條SQL語句中，對多個表進行刪除。
- DELETE語句中必須有WHERE子句，避免全表掃描。
- DELETE語句中禁止不應使用ORDER BY、GROUP BY子句，避免不必要的排序。
- 如果需要清空一張表，建議使用TRUNCATE，而不是DELETE。
- TRUNCATE會創建新的物理文件，并在事務結束時將原文件物理刪除，清空磁盤空間。而DELETE會將表中數據進行標記，直到VACCUUM FULL階段才會真正清理磁盤空間。
- DELETE有主鍵或索引的表，WHERE條件應結合主鍵或索引，提高執行效率。
- DELETE 語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項。
- 如果想保留標識計數值，請改用 DELETE
3、語法
DELETE FROM table_name [WHERE condition];參數：table_name：需要刪除數據的Table名稱。condition：用于判斷哪些行需要被刪除。
4、示例
復用前面的實驗表：
1）刪除orders表中customer_id <200的所有數據：
DELETE FROM orders WHERE customer_id <200;
六、應用場景
- 需要根據一定的業務條件刪除數據時、且數據量、性能可控的情況下，可以考慮使用 DELETE。
- 需要刪除大批量數據時，同時要求速度快，效率高并且無需撤銷時，可以使用 TRUNCATE。
- 在企業級開發中，實際上都是進行邏輯刪除（將數據進行“刪除標識”處理）、而并不進行物理上的刪除。
- 在實際生產環境中，一般情況下刪除業務處理（過渡表）中的數據。
- 在實際企業開發、維護過程中，不管使用 DELETE、TRUNCATE還是DROP命令前，都要考慮數據的備份。
七、小結
在GaussDB等數據庫中，DROP、TRUNCATE和DELETE均是常用的刪除數據的命令。但在實際業務使用中，需要根據不同的需求進行準確的選擇，但無論選擇那種刪數方式，都需要考慮數據安全性——重要的事情說三遍：備份！備份！備份！
