MySQL WHERE子句

概述

WHERE子句根据指定的条件过滤记录。它只提取满足指定条件的记录，使查询更精确、更高效。

WHERE语法

比较运算符

基本比较

-- Equal

-- Not equal

-- Greater than

-- Greater than or equal

-- Less than

-- Less than or equal

范围运算符

-- Between (inclusive)

-- Date range

-- NOT BETWEEN

列表运算符

-- In list

-- With subquery

-- NOT IN

逻辑运算符

AND运算符

-- Multiple conditions (all must be true)

-- Multiple AND conditions

-- Combine with other operators

OR运算符

-- Any condition true

-- Multiple OR conditions

AND与OR组合

-- AND has higher precedence

-- Parentheses for clarity

NOT运算符

-- NOT equal

-- NOT IN

-- NOT BETWEEN

-- NOT LIKE

-- NOT NULL

-- Complex NOT

NULL条件

IS NULL / IS NULL

-- Find NULL values

-- Find missing relationships

-- Find incomplete records

IS NOT NULL / IS NOT NULL

-- Exclude NULL values

-- Only completed records

模式匹配

LIKE / LIKE

-- Starts with

-- Ends with

-- Contains

-- Single character wildcard

-- Multiple wildcards

NOT LIKE / NOT LIKE

-- Does not start with

-- Does not contain

大小写不敏感

-- MySQL LIKE is case-insensitive by default

-- For case-sensitive comparison

正则表达式

-- Starts with

-- Ends with

-- Contains any of characters

-- Multiple options

-- Complex patterns

-- NOT REGEXP

字符串比较

-- Exact match

-- Case-insensitive comparison

-- Length comparison

-- String functions in WHERE

-- Concatenation comparison

日期时间过滤

-- Specific date

-- Date range

-- Time comparison

-- Year filter

-- Month filter

-- Day of week

-- Date functions

数值过滤

-- Aggregate functions in WHERE (using HAVING)

-- Subquery with aggregate

-- Mathematical operations

组合条件

-- Complex query

-- Using CASE in WHERE (advanced)

性能考虑

索引使用

-- Use indexed columns in WHERE

-- Function on column prevents index use

-- Rewrite to use index

查询优化

-- Put most selective condition first

-- Avoid SELECT *

-- Use LIMIT for testing

故障排除

常见问题

-- Unexpected results
-- Check: Operator precedence, parentheses

-- No results returned
-- Check: NULL comparisons (use IS NULL, not = NULL)

-- Case sensitivity
-- Check: String comparisons, collation settings

-- Date format
-- Check: Date format matches MySQL expectations (YYYY-MM-DD)

调试

-- Add EXPLAIN to see query plan

-- Test conditions separately

-- Check data types

小结

WHERE子句过滤包括：

比较运算符 范围运算符 逻辑运算符 NULL处理 模式匹配 Time**: Date functions / 日期时间 性能优化

---

上一个：查询数据

下一个：UPDATE