MySQL - WHERE 子句

MySQL - 使用 WHERE 子句过滤数据

WHERE 子句的目的

WHERE 子句是 SQL 最基本的组成部分之一。它用于过滤 SELECT、UPDATE 或 DELETE 语句返回的记录。如果没有 WHERE 子句，这些语句将应用于表中的所有行。通过在 WHERE 子句中指定条件（conditions），您可以精确地定位要检索或修改的数据。

语法

SELECT column_list
FROM table_name
WHERE condition(s);

语法和运算符

WHERE 子句使用各种运算符来构建条件：

运算符	描述	示例
=	等于	age = 30
<> 或 !=	不等于	country <> 'USA'
>	大于	price > 99.99
<	小于	stock < 10
>=	大于或等于	score >= 90
<=	小于或等于	discount <= 0.5
BETWEEN	检查值是否在某个范围内（包含边界）	age BETWEEN 18 AND 65
IN	检查值是否在值列表中	status IN ('pending', 'shipped')
LIKE	简单模式匹配	name LIKE 'J%'
IS NULL	检查是否为 NULL 值	shipping_date IS NULL
IS NOT NULL	检查是否为非 NULL 值	email IS NOT NULL

示例设置

我们使用一个 customers 表作为示例。

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    city VARCHAR(50),
    signup_date DATE NOT NULL,
    tier VARCHAR(20) DEFAULT 'Standard'
);

INSERT INTO customers (name, age, city, signup_date, tier) VALUES
('Alice', 32, 'New York', '2022-01-15', 'Gold'),
('Bob', 25, 'Los Angeles', '2022-03-22', 'Silver'),
('Charlie', 45, 'New York', '2021-11-30', 'Gold'),
('Diana', 28, 'Chicago', '2023-01-10', 'Standard'),
('Edward', NULL, 'Chicago', '2023-02-20', 'Standard');

使用基本和高级运算符进行过滤

示例 1：使用比较运算符

获取年龄大于 30 的客户。

SELECT name, age, city FROM customers WHERE age > 30;

示例 2：使用 BETWEEN

获取在 2022 年第一季度注册的客户。

SELECT name, signup_date FROM customers
WHERE signup_date BETWEEN '2022-01-01' AND '2022-03-31';

示例 3：使用 IN

获取所有 ‘Gold’ 和 ‘Silver’ 等级的客户。

SELECT name, tier FROM customers WHERE tier IN ('Gold', 'Silver');

示例 4：使用 IS NULL

查找未记录年龄的客户。

SELECT name, city FROM customers WHERE age IS NULL;

组合条件

您可以使用 AND（所有条件必须为真）和 OR（至少一个条件必须为真）组合条件来创建复杂的过滤器。AND 具有比 OR 更高的优先级，因此请使用括号 () 来强制执行特定的求值顺序。

示例 5：使用 AND 和 OR

获取所有来自 ‘New York’ 的 ‘Gold’ 等级客户，或所有来自 ‘Chicago’ 的客户。

SELECT name, city, tier FROM customers
WHERE (tier = 'Gold' AND city = 'New York') OR (city = 'Chicago');

安全性：防止 SQL 注入

这是本章中最重要的概念。 永远不要通过将用户输入直接拼接（concatenating）到查询字符串（query string）中来构建 SQL 查询。这会造成一个严重的安全漏洞，称为 SQL 注入（SQL Injection）。始终使用参数化查询（parameterized queries）（也称为预处理语句，prepared statements）。

客户端应用程序（例如 Node.js、Python、Java、PHP）会通过将查询模板和用户提供的值分开发送给数据库驱动程序来处理，然后驱动程序会安全地将它们组合起来。

**易受攻击（请勿这样做）：**
`const query = "SELECT * FROM users WHERE username = '" + userInput + "';";`

**安全（使用参数化）：**
`const query = "SELECT * FROM users WHERE username = ?;";`
`db.execute(query, [userInput]);`

性能：编写 SARGable 的 WHERE 子句

为了在大型表上获得快速的查询性能，您的 WHERE 子句条件应该是 SARGable（Search ARGument Able，可利用索引搜索）。这意味着该条件可以使用索引进行评估。导致查询非 SARGable 的最常见错误是对索引列应用函数。

**非 SARGable（慢）：** 无法使用 `signup_date` 上的索引。
`WHERE YEAR(signup_date) = 2022;`

**SARGable（快）：** 可以使用 `signup_date` 上的索引。
`WHERE signup_date >= '2022-01-01' AND signup_date < '2023-01-01';`

通过将条件转换为原始列值与计算值进行比较，您可以让数据库引擎执行高效的索引查找（index seek），而不是缓慢的全表扫描（table scan）。