如何通过SQL UPPER和LOWER函数实现不区分大小写的关键词搜索

UPPER/LOWER在WHERE中必须两端同时转换才能实现大小写不敏感匹配，如WHERE UPPER(name) = UPPER('john')；单边转换无效，且会导致索引失效、全表扫描。

UPPER/LOWER 函数在 WHERE 子句中必须成对使用

直接写 WHERE name = 'john' 永远不会匹配 'John' 或 'JOHN'，大小写敏感是默认行为。要实现不区分大小写的匹配，必须让左右两边的大小写状态一致——要么全转大写，要么全转小写。

常见错误是只转换一边：WHERE UPPER(name) = 'john'（右边没转），或 WHERE name = UPPER('john')（左边没转）。这两种写法都无效，因为一边是原始值，一边是转换后值，比较仍会失败。

• 正确写法是： WHERE UPPER(name) = UPPER('john') 或 WHERE LOWER(name) = LOWER('john')
• 推荐统一用 LOWER，因为部分数据库（如 PostgreSQL）对 Unicode 大写字母支持更稳定；UPPER 在某些土耳其语环境可能出错
• 如果关键词来自用户输入，务必在应用层先做一次 LOWER 或 UPPER 预处理，避免 SQL 中重复调用函数影响可读性

索引失效问题比想象中更常见

一旦在列上套用 UPPER() 或 LOWER()，绝大多数数据库（MySQL、PostgreSQL、SQL Server）会跳过普通 B-tree 索引，导致全表扫描。这不是“慢一点”，而是数据量稍大就明显卡顿。

解决方法不是不用函数，而是提前固化大小写一致性：

• 建表时增加生成列：name_lower TEXT GENERATED ALWAYS AS (LOWER(name)) STORED（PostgreSQL / MySQL 5.7+），然后对 name_lower 建索引
• 或者业务写入时就存 LOWER(name) 到单独字段（如 name_ci），查询走该字段 + 索引
• MySQL 8.0+ 支持函数索引：CREATE INDEX idx_name_lower ON users ((LOWER(name))) —— 注意括号写法，不是 LOWER(name) 字段名

LIKE 模糊搜索时大小写处理不能省略

WHERE LOWER(name) LIKE LOWER('%john%') 是安全写法；但写成 WHERE name LIKE '%john%' 在大多数数据库里仍是大小写敏感的（除非启用了 case-insensitive collation）。

尤其注意以下陷阱：

• PostgreSQL 默认区分大小写，ILIKE 是特例，但仅限 PostgreSQL；其他数据库没有等价语法
• MySQL 若用 utf8mb4_0900_as_cs 这类大小写敏感排序规则，LIKE 也区分大小写
• 参数化查询中，如果传入的是原始关键词，必须在 SQL 中显式包裹 LOWER()，不能依赖驱动或 ORM 自动处理

不同数据库对空值和特殊字符的处理差异

UPPER(NULL) 和 LOWER(NULL) 在所有主流数据库中都返回 NULL，这本身没问题；但容易被忽略的是：某些数据库对非 ASCII 字符（比如中文、德语 ß、希腊字母）的大小写转换行为不一致。

例如：

• MySQL 的 UPPER('straße') 返回 'STRASSE'（ß → SS），而 PostgreSQL 返回 'STRASSE' 或 'STRASSE'（取决于 locale），结果一致但原理不同
• SQLite 不内置 UPPER/LOWER 对 Unicode 的支持，遇到中文直接原样返回，无法用于中文关键词归一化
• 如果关键词含空格或标点（如 'John D.'），LOWER 只影响字母，不影响结构，所以预处理时是否 trim、是否替换多余空格，需由业务逻辑决定

真正麻烦的不是函数本身，而是你得确认当前数据库版本、字符集、排序规则三者共同作用下的实际行为——光看文档不够，最好在目标环境中实测几个边界词。