MySQL - DELETE JOIN

MySQL：使用 JOIN 删除记录

何时使用 `DELETE` 与 `JOIN`

标准的 DELETE 语句只对单个表操作。然而，您经常需要根据另一个相关表中的条件，从一个表中删除行。这时，DELETE...JOIN 语法就变得非常强大了。例如，您可能希望删除过去一年内未下订单的所有客户。

此操作允许您在单个原子语句中执行跨多个表的条件删除，这比先获取 ID 再运行单独的 DELETE 命令更高效、更安全。

使用 `JOIN` 进行 `DELETE` 的语法

基本语法涉及指定要从哪个（或哪些）表删除，然后是连接表的 FROM 和 JOIN 子句。

DELETE table1_alias, table2_alias
FROM table1 AS table1_alias
[INNER | LEFT | RIGHT] JOIN table2 AS table2_alias
ON table1_alias.join_column = table2_alias.join_column
WHERE condition;

至关重要的是，您必须在 DELETE 关键字之后立即列出要从中删除记录的表（或其别名）。如果您只想从 table1 中删除，您可以写 DELETE table1_alias FROM ...。

示例设置：客户和订单

让我们设置两个表：CUSTOMERS 和 ORDERS。

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    STATUS VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE ORDERS(
    OID INT PRIMARY KEY AUTO_INCREMENT,
    ORDER_DATE DATE NOT NULL,
    CUSTOMER_ID INT,
    AMOUNT DECIMAL(10, 2),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID) ON DELETE SET NULL
);

INSERT INTO CUSTOMERS (NAME, STATUS) VALUES ('Ramesh', 'active'), ('Khilan', 'inactive'), ('Kaushik', 'active'), ('Muffy', 'active');

INSERT INTO ORDERS (ORDER_DATE, CUSTOMER_ID, AMOUNT) VALUES
('2023-10-08', 3, 3000.00),
('2022-01-15', 2, 1500.00),
('2023-11-20', 3, 1560.00),
('2021-05-20', 4, 2060.00);

在这种情况下，客户 ‘Ramesh’（ID 1）没有订单，而客户 ‘Khilan’（ID 2）被标记为不活跃。

使用 `INNER JOIN` 进行 DELETE

让我们删除所有被标记为“不活跃”的客户及其关联的订单记录。我们在 DELETE 子句中同时指定两个别名（c，o），以从两个表中删除行。

DELETE c, o
FROM CUSTOMERS AS c
INNER JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID
WHERE c.STATUS = 'inactive';

此查询将从 CUSTOMERS 表中删除 ‘Khilan’ 的记录，并从 ORDERS 表中删除其对应的订单。

使用 `LEFT JOIN` 进行 DELETE

LEFT JOIN 非常适合查找一个表中没有对应记录的记录。让我们删除从未下过订单的客户。

DELETE c
FROM CUSTOMERS AS c
LEFT JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID
WHERE o.OID IS NULL;

在这种情况下，LEFT JOIN 将找到所有客户。对于没有订单的客户，ORDERS 表（别名为 o）中的所有列都将为 NULL。WHERE o.OID IS NULL 条件标识出这些客户。该查询将从 CUSTOMERS 表中删除 ‘Ramesh’（ID 1）。

最佳实践：先用 SELECT 验证

这是最重要的规则。 在运行破坏性的 DELETE...JOIN 查询之前，请务必先将其作为 SELECT * 运行，以确切查看将受影响的行。这可以防止灾难性的错误。

要验证之前的 LEFT JOIN 示例，您可以运行：

SELECT c.*, o.*
FROM CUSTOMERS AS c
LEFT JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID
WHERE o.OID IS NULL;

仔细检查输出。如果它显示了您打算删除的行，您就可以放心地将 SELECT c.*, o.* 替换为 DELETE c。

替代方案：`USING` 子句

MySQL 还支持 DELETE...USING 语法，一些开发者认为它更具可读性。它将要删除的表与用于连接逻辑的表分开。

示例：使用 `USING` 删除不活跃客户

-- 注意：要删除的表在 USING 之前列出
DELETE FROM CUSTOMERS
USING CUSTOMERS
INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE CUSTOMERS.STATUS = 'inactive';

此查询将只从 CUSTOMERS 表中删除。要从两个表中删除，您需要列出它们：DELETE FROM CUSTOMERS, ORDERS USING ...

在客户端应用程序中实现删除连接

当从应用程序执行 DELETE...JOIN 时，使用预处理语句来安全地传递状态或 ID 等值。

Python（使用 mysql-connector-python）

import mysql.connector
from mysql.connector import Error

def delete_inactive_customers(status_to_delete):
    # 注意：这里是从两个表中删除。
    query = """DELETE c, o
               FROM CUSTOMERS AS c
               INNER JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID
               WHERE c.STATUS = %s"""

    try:
        with mysql.connector.connect(/*...db config...*/, database='TUTORIALS') as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (status_to_delete,))
                conn.commit() # 重要：提交事务
                print(f"{cursor.rowcount} rows were deleted.")

    except Error as e:
        print(f"Error: {e}")

# 调用函数删除所有“不活跃”客户及其订单
delete_inactive_customers('inactive')

MySQL - DELETE JOIN

MySQL：使用 JOIN 删除记录

何时使用 DELETE 与 JOIN

使用 JOIN 进行 DELETE 的语法