MySQL - EXISTS 运算符

EXISTS 运算符是一个布尔运算符，用于 WHERE 子句中，以检查子查询中是否存在任何行。如果子查询返回一行或多行，则返回 TRUE，否则返回 FALSE。一个关键的性能优势是，它一旦找到第一个匹配行，就会停止处理子查询。

语法

SELECT ...
FROM table1
WHERE [NOT] EXISTS (SELECT 1 FROM table2 WHERE condition);

注意：在子查询中使用 SELECT 1 或 SELECT * 是一种常见约定。实际选择的列无关紧要，因为 EXISTS 只检查行的存在，而不是其内容。

设置：示例表

让我们为示例创建两个表：customers 和 orders。

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

INSERT INTO customers VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO orders VALUES
(101, 1, '2023-10-26'),
(102, 3, '2023-10-25'),
(103, 1, '2023-10-27');

将 EXISTS 与 SELECT 结合使用

一个常见用例是查找一个表中在另一个表中具有相应匹配的行。让我们查找所有至少下过一个订单的客户。

示例

SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

输出

+---------------+
| customer_name |
+---------------+
| Alice         |
| Charlie       |
+---------------+

与 INNER JOIN 的比较

您可以使用 INNER JOIN 达到相同的效果。但是，如果客户有多个订单，JOIN 可能会产生重复行，需要使用 DISTINCT。EXISTS 自然地避免了这一点。

-- 使用 INNER JOIN，需要 DISTINCT 以避免重复
SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

使用 NOT EXISTS

NOT EXISTS 运算符非常适合查找一个表中在另一个表中没有匹配的行。让我们查找所有从未下过订单的客户。

示例

SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

输出

+---------------+
| customer_name |
+---------------+
| Bob           |
+---------------+

与 LEFT JOIN … IS NULL 的比较

这是一种经典的模式，也可以通过 LEFT JOIN 解决。两者都有效，现代 MySQL 优化器通常足够智能，可以类似地处理它们。但是，NOT EXISTS 在意图上可能更具可读性和明确性。

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

将 EXISTS 与 UPDATE 和 DELETE 结合使用

EXISTS 对于条件 UPDATE 或 DELETE 语句也非常有用。

示例：DELETE

让我们删除所有已下订单的客户（这是一个为了演示而设计的例子）。

DELETE FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

这将从 customers 表中删除 Alice 和 Charlie，只留下 Bob。

在客户端程序中使用 EXISTS

从应用程序检查记录是否存在时，使用 EXISTS 查询是非常高效的。

示例（使用 mysql2 的 Node.js）

const mysql = require('mysql2/promise');

async function checkCustomerExists(connection, customerId) {
  try {
    const sql = 'SELECT EXISTS(SELECT 1 FROM customers WHERE customer_id = ?) AS `exists`';
    const [rows] = await connection.execute(sql, [customerId]);

    // 结果是一个整数：1 表示存在，0 表示不存在。
    return rows[0].exists === 1;

  } catch (error) {
    console.error('Failed to check customer existence:', error);
    return false;
  }
}

async function main() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_password',
      database: 'testdb'
    });

    const customerIdToCheck = 2; // Bob
    const exists = await checkCustomerExists(connection, customerIdToCheck);
    console.log(`Does customer with ID ${customerIdToCheck} exist? ${exists}`); // true

    const nonExistentId = 99;
    const exists2 = await checkCustomerExists(connection, nonExistentId);
    console.log(`Does customer with ID ${nonExistentId} exist? ${exists2}`); // false

  } catch (err) {
    console.error('An error occurred:', err);
  } finally {
    if (connection) connection.end();
  }
}

main();