MySQL LIKE子句

概述

LIKE运算符用于WHERE子句中的模式匹配。它在列中搜索指定的模式，支持通配符进行灵活匹配。

LIKE语法

通配符

百分号

-- Matches any sequence of characters

-- Matches zero or more characters

下划线

-- Matches exactly one character

-- Multiple underscores

组合通配符

-- Start with, end with

-- Complex patterns

-- Escape wildcards

LIKE模式

以...开头

-- Names starting with A

-- Emails from specific domain

-- Products in category

以...结尾

-- Files with specific extension

-- Users with specific suffix

包含

-- Products containing 'pro'

-- Users with specific pattern

-- Case-insensitive contains

指定模式

-- Exactly 5 characters

-- Starts with A, ends with Z

-- Contains exactly 3 characters

NOT LIKE / NOT LIKE

-- Does not start with

-- Does not contain

-- Does not match pattern

不同数据类型的LIKE

字符串

-- Text matching

-- Case sensitivity (MySQL LIKE is case-insensitive by default)

数字

-- Numbers as strings

-- ZIP codes

日期

-- Dates as strings

-- Years

ESCAPE子句

转义默认字符

-- Escape % character

-- Escape underscore

自定义转义字符

-- Use different escape character

-- Complex pattern with escape

不同上下文中的LIKE

UPDATE语句

-- Update matching patterns

DELETE语句

-- Delete matching patterns

INSERT语句

-- Not typically used in INSERT, but in SELECT for insert

性能考虑

索引使用

-- LIKE with leading wildcard cannot use index

-- LIKE without leading wildcard CAN use index

-- Leading wildcard pattern

优化策略

-- Avoid leading wildcards when possible
-- Instead of: WHERE email LIKE '%@gmail.com'
-- Use: WHERE email REGEXP '@gmail.com$' (still slow, but more flexible)

-- Use FULLTEXT search for large text columns

-- Consider('mysql tutorial storing reverse strings for trailing wildcard

LIKE与其他模式匹配

LIKE与REGEXP比较

方面	LIKE	REGEXP
通配符	%, _	Complex regex
性能	Faster	Slower
灵活性	Limited	High
开头通配符	Cannot use index	Cannot use index
用例	Simple patterns	Complex patterns

-- LIKE examples

-- REGEXP examples

LIKE与IN比较

-- IN for exact matches

-- LIKE for patterns

实用示例

搜索功能

-- Simple search

-- Search with word boundaries

-- Case-insensitive search

数据验证

-- Find invalid email patterns

-- Find invalid phone formats

-- Find malformed codes

数据清洗

-- Find duplicate-like entries

-- Find entries with special characters

-- Find empty or whitespace-only

小结

LIKE子句提供：

通配符：%（任意），_（单个） 模式匹配：简单字符串模式 大小写敏感性：默认不区分大小写 ESCAPE：自定义转义字符 性能：避免开头通配符 替代方案：REGEXP用于复杂模式

---

上一个：DELETE

下一个：UNION