如何实现SQL模糊搜索忽略大小写_使用LOWER函数与索引配合-SQL-PHP中文网

mysql中lower(column) like lower('%abc%')导致索引失效，因函数作用于列使索引无法匹配原始排序值；唯一例外是列使用大小写不敏感校对集时，直接like可走索引。

如何实现sql模糊搜索忽略大小写_使用lower函数与索引配合

直接在 WHERE 条件里写 LOWER(column) LIKE LOWER('%abc%')，绝大多数情况下会全表扫描——因为函数作用于列，MySQL 无法利用该列原有索引。

索引是按原始值（比如 'User'）排序构建的，LOWER('User') 得到 'user'，但索引里没存这个转换结果
即使列上有 utf8mb4_0900_as_cs 这类大小写敏感校对集，LIKE 默认仍按校对集规则比较，不等于“自动忽略大小写”
唯一例外：当列使用大小写不敏感校对集（如 utf8mb4_0900_as_ci），column LIKE '%abc%' 本身就忽略大小写，且能走索引（前提是没前置通配符）

ILIKE 本身不走 B-tree 索引，但可以配合表达式索引解决。

建索引时显式指定小写表达式：CREATE INDEX idx_users_name_lower ON users ((LOWER(name)))
查询必须严格匹配该表达式：WHERE LOWER(name) LIKE LOWER('john%') 或 WHERE LOWER(name) = 'john'
注意：如果写成 name ILIKE 'john%'，哪怕有表达式索引也不会用——优化器不识别这种等价转换
表达式索引只对 LOWER() 有效，UPPER() 或其他函数需单独建对应索引

用 COLLATE 强制大小写不敏感，可能让索引失效，关键看是否改变列的隐式校对行为。

如果原列是 SQL_Latin1_General_CP1_CI_AS（CI = case-insensitive），直接 WHERE name LIKE '%abc%' 就忽略大小写，且可用索引（无前导 % 时）
如果原列是大小写敏感校对集（CS），强行加 COLLATE SQL_Latin1_General_CP1_CI_AS，例如 WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%abc%'，会导致索引失效
更稳妥的做法：建计算列 + 索引，比如 ALTER TABLE users ADD name_lower AS LOWER(name) PERSISTED，再在 name_lower 上建索引

想一套代码适配 MySQL/PostgreSQL/SQL Server，又不想牺牲性能，就得把大小写归一化逻辑提到应用层做。

用户输入搜索词，服务端统一转小写（input.toLowerCase()），然后查原始列（假设列已建普通 B-tree 索引）
但前提是：列值也始终以小写存储（或写入时就 LOWER()），否则大小写混存会导致漏匹配
这个方案下，WHERE name LIKE 'john%' 可走索引；但 WHERE name LIKE '%john%' 依然无法避免全扫——这是模糊搜索本身的限制，和大小写无关
真正要支持高效任意位置模糊搜，得上 pg_trgm（PostgreSQL）、FULLTEXT（MySQL）或专用搜索引擎

最容易被忽略的是：校对集（collation）和索引的绑定关系是静态的，改了列的校对集不等于自动重建索引，有些数据库甚至要求先删索引再改校对集。别在上线前一刻才发现索引失效。