MySQL :: MySQL 9.0 參考手冊 :: 15.2.2 DELETE 語法

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / DELETE 語法

15.2.2 DELETE 語法

DELETE 是一個 DML 語法，用於從資料表中移除列。

DELETE 語法可以 WITH 子句開頭，以定義可在 DELETE 中存取的通用表格運算式。請參閱第 15.2.20 節，「WITH (通用表格運算式)」。

單一表格語法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

DELETE 語法會從 tbl_name 中刪除列，並傳回已刪除的列數。若要檢查已刪除的列數，請呼叫ROW_COUNT() 函數，如第 14.15 節，「資訊函數」所述。

主要子句

選用的 WHERE 子句中的條件會識別要刪除的資料列。如果沒有 WHERE 子句，則會刪除所有資料列。

where_condition 是一個運算式，對於每個要刪除的資料列，其評估結果為 true。其指定方式如同第 15.2.13 節，「SELECT 陳述式」中所述。

如果指定了 ORDER BY 子句，則會按照指定的順序刪除資料列。LIMIT 子句會限制可以刪除的資料列數量。這些子句適用於單一資料表刪除，但不適用於多資料表刪除。

多資料表語法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

權限

您需要對資料表擁有 DELETE 權限，才能從該資料表刪除資料列。您只需要對任何僅讀取的欄位擁有 SELECT 權限，例如在 WHERE 子句中指定的欄位。

效能

當您不需要知道刪除的資料列數時，TRUNCATE TABLE 陳述式比沒有 WHERE 子句的 DELETE 陳述式更快地清空資料表。與 DELETE 不同，TRUNCATE TABLE 不能在交易內使用，也不能在您鎖定資料表時使用。請參閱第 15.1.37 節，「TRUNCATE TABLE 陳述式」和第 15.3.6 節，「LOCK TABLES 和 UNLOCK TABLES 陳述式」。

刪除操作的速度也可能受到第 10.2.5.3 節，「最佳化 DELETE 陳述式」中討論的因素影響。

為確保給定的 DELETE 陳述式不會花費太多時間，MySQL 特有的 DELETE 的 LIMIT row_count 子句會指定要刪除的最大資料列數。如果刪除的資料列數大於限制，請重複執行 DELETE 陳述式，直到受影響的資料列數小於 LIMIT 值。

子查詢

您無法在子查詢中從資料表刪除資料，並從同一個資料表選取資料。

分割資料表支援

DELETE 支援使用 PARTITION 子句明確選擇分割區，該子句採用以逗號分隔的一個或多個分割區或子分割區（或兩者）的名稱清單，以從中選取要刪除的資料列。清單中未包含的分割區會被忽略。假設有一個名為 p0 的分割區的分割資料表 t，執行陳述式 DELETE FROM t PARTITION (p0) 對資料表的效果與執行 ALTER TABLE t TRUNCATE PARTITION (p0) 相同；在這兩種情況下，都會刪除分割區 p0 中的所有資料列。

PARTITION 可以與 WHERE 條件一起使用，在這種情況下，只會針對清單中的分割區中的資料列測試條件。例如，DELETE FROM t PARTITION (p0) WHERE c < 5 只會從分割區 p0 中刪除條件 c < 5 為 true 的資料列；任何其他分割區中的資料列都不會被檢查，因此不受 DELETE 的影響。

PARTITION 子句也可以在多資料表 DELETE 陳述式中使用。您可以在 FROM 選項中命名的每個資料表中使用最多一個此類選項。

如需更多資訊和範例，請參閱第 26.5 節，「分割區選取」。

自動遞增欄位

如果您刪除包含 AUTO_INCREMENT 欄位最大值的資料列，則該值不會在 MyISAM 或 InnoDB 資料表中重複使用。如果您在autocommit 模式中使用 DELETE FROM tbl_name (沒有 WHERE 子句) 刪除資料表中的所有資料列，則除了 InnoDB 和 MyISAM 之外的所有儲存引擎都會重新開始序列。對於 InnoDB 資料表，此行為有一些例外情況，如第 17.6.1.6 節，「InnoDB 中的 AUTO_INCREMENT 處理」中所述。

對於 MyISAM 資料表，您可以在多欄索引鍵中指定 AUTO_INCREMENT 次要欄位。在這種情況下，即使對於 MyISAM 資料表，也會重複使用從序列頂部刪除的值。請參閱第 5.6.9 節，「使用 AUTO_INCREMENT」。

修飾詞

DELETE 陳述式支援下列修飾詞

如果您指定 LOW_PRIORITY 修飾詞，伺服器會延遲執行 DELETE，直到沒有其他用戶端正在讀取該資料表。這只會影響僅使用資料表層級鎖定的儲存引擎（例如 MyISAM、MEMORY 和 MERGE）。
對於 MyISAM 資料表，如果您使用 QUICK 修飾詞，則儲存引擎不會在刪除期間合併索引葉片，這可能會加快某些類型的刪除操作。
IGNORE 修飾詞會導致 MySQL 忽略刪除資料列過程中可忽略的錯誤。（在剖析階段遇到的錯誤會以通常的方式處理。）由於使用 IGNORE 而被忽略的錯誤會以警告的形式傳回。如需更多資訊，請參閱「IGNORE 對陳述式執行的影響」。

刪除順序

如果 DELETE 陳述式包含 ORDER BY 子句，則會按照子句指定的順序刪除資料列。這主要是在與 LIMIT 結合時很有用。例如，以下陳述式會尋找符合 WHERE 子句的資料列，依 timestamp_column 對其進行排序，然後刪除第一個（最舊的）資料列

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

ORDER BY 也有助於以避免參考完整性違規所需的順序刪除資料列。

InnoDB 資料表

如果要從大型資料表中刪除許多資料列，您可能會超過 InnoDB 資料表的鎖定資料表大小。為了避免此問題，或只是為了盡量減少資料表保持鎖定的時間，以下策略（完全不使用 DELETE）可能會有所幫助

將不要刪除的資料列選取到與原始資料表結構相同的空資料表中
```
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
```
使用 RENAME TABLE 以原子方式將原始資料表移開，並將副本重新命名為原始名稱
```
RENAME TABLE t TO t_old, t_copy TO t;
```
刪除原始資料表
```
DROP TABLE t_old;
```

在執行 RENAME TABLE 時，沒有其他工作階段可以存取相關資料表，因此重新命名操作不會遇到並行問題。請參閱第 15.1.36 節，「RENAME TABLE 陳述式」。

MyISAM 資料表

在 MyISAM 資料表中，已刪除的資料列會保留在連結清單中，後續的 INSERT 操作會重複使用舊的資料列位置。若要回收未使用的空間並縮小檔案大小，請使用 OPTIMIZE TABLE 陳述式或 myisamchk 公用程式來重新組織資料表。OPTIMIZE TABLE 更容易使用，但 myisamchk 更快。請參閱第 15.7.3.4 節，「OPTIMIZE TABLE 陳述式」和第 6.6.4 節，「myisamchk — MyISAM 資料表維護公用程式」。

QUICK 修飾詞會影響是否為刪除操作合併索引葉片。DELETE QUICK 對於已刪除資料列的索引值被稍後插入的資料列的類似索引值取代的應用程式最有用。在這種情況下，會重複使用刪除值留下的空洞。

當刪除的值導致索引區塊中，新插入值會再次出現的索引值範圍內未填滿時，DELETE QUICK 沒有用處。在這種情況下，使用 QUICK 可能會導致索引中存在未回收的浪費空間。以下是這種情況的範例

建立一個包含已編製索引的 AUTO_INCREMENT 欄位的資料表。
將許多資料列插入資料表中。每次插入都會產生一個新增到索引高位的索引值。
使用 DELETE QUICK 刪除欄位範圍低位的資料列區塊。

在這個情境中，與已刪除索引值相關的索引區塊會變得未填滿，但因為使用了 QUICK 而不會與其他索引區塊合併。當新的插入發生時，它們會保持未填滿的狀態，因為新列在已刪除的範圍內沒有索引值。此外，即使您稍後使用不帶 QUICK 的 DELETE，它們仍會保持未填滿，除非某些已刪除的索引值剛好位於未填滿區塊內或相鄰的索引區塊中。若要在這些情況下回收未使用的索引空間，請使用 OPTIMIZE TABLE。

如果您要從資料表中刪除許多列，使用 DELETE QUICK 後接著 OPTIMIZE TABLE 可能會更快。這會重建索引，而不是執行許多索引區塊合併操作。

多資料表刪除

您可以在 DELETE 陳述式中指定多個資料表，以根據 WHERE 子句中的條件，從一個或多個資料表刪除列。您不能在多資料表 DELETE 中使用 ORDER BY 或 LIMIT。table_references 子句會列出聯結中涉及的資料表，如第 15.2.13.2 節「JOIN 子句」中所述。

對於第一個多資料表語法，只會刪除 FROM 子句之前列出的資料表中符合條件的列。對於第二個多資料表語法，只會刪除 FROM 子句（在 USING 子句之前）中列出的資料表中符合條件的列。其效果是，您可以同時從多個資料表刪除列，並擁有僅用於搜尋的其他資料表。

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

或者

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

這些陳述式在搜尋要刪除的列時會使用所有三個資料表，但只會從資料表 t1 和 t2 中刪除符合條件的列。

前面的範例使用 INNER JOIN，但是多資料表 DELETE 陳述式可以使用 SELECT 陳述式中允許的其他聯結類型，例如 LEFT JOIN。例如，要刪除 t1 中存在但在 t2 中沒有匹配項的列，請使用 LEFT JOIN

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

為了與 Access 相容，語法允許在每個 tbl_name 後面加上 .*。

如果您使用涉及 InnoDB 資料表且有外部索引鍵約束的多資料表 DELETE 陳述式，MySQL 最佳化工具可能會以與其父/子關係不同的順序處理資料表。在這種情況下，陳述式會失敗並回滾。相反地，您應該從單一資料表刪除，並依賴 InnoDB 提供的 ON DELETE 功能，以導致其他資料表相應地被修改。

注意

如果您為資料表宣告別名，則在引用資料表時必須使用別名

DELETE t1 FROM test AS t1, test2 WHERE ...

多資料表 DELETE 中的資料表別名應僅在陳述式的 table_references 部分宣告。在其他地方，允許使用別名引用，但不允許宣告別名。

正確

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

不正確

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;

DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

單資料表 DELETE 陳述式也支援資料表別名。

上一個首頁上一層下一個