ilike是postgresql特有的不区分大小写的模式匹配操作符，语法与like完全相同但自动忽略大小写；like默认区分大小写，而ilike无需lower()函数包裹、不依赖collate，支持%和_通配符，且可在join的on条件中直接使用。

ILIKE 是什么，和 LIKE 有什么区别？

ILIKE 是 PostgreSQL 特有的操作符，专为不区分大小写的模式匹配设计。它和 LIKE 语法完全一致，唯一区别是 ILIKE 自动忽略大小写，而 LIKE 严格区分（取决于当前 collation，但默认行为就是区分）。

常见错误现象：写 WHERE name LIKE '%apple%' 却查不到 'Apple' 或 'APPLE'，其实只需要把 LIKE 换成 ILIKE 就行。

• ILIKE 不依赖 lower() 包裹字段，避免函数索引失效问题
• 它能直接利用普通 B-tree 索引（如果字段上有 text_pattern_ops 或使用 citext 类型会更高效）
• 注意：在非 C locale 下，某些 Unicode 字符可能表现异常（比如土耳其语的 i/I），这是底层 collation 行为，不是 ILIKE 的 bug

怎么写一个安全、可索引的 ILIKE 查询？

模糊查询容易拖慢数据库，尤其用 % 开头时。要兼顾功能和性能，得看场景选写法：

• 前缀匹配（如查“张”开头的姓名）：WHERE name ILIKE '张%' —— 可走 B-tree 索引（需字段类型支持，如 text + 默认 collation）
• 后缀匹配（如查以“.pdf”结尾的文件名）：WHERE filename ILIKE '%.pdf' —— 无法用普通索引，建议建 reverse(filename) 函数索引
• 全文包含（如查含“admin”的用户名）：WHERE username ILIKE '%admin%' —— 必须全表扫描，除非改用 pg_trgm 扩展 + gin 索引
• 避免写成 WHERE lower(name) LIKE lower('%Apple%') —— 这会让索引失效，且比 ILIKE 多一次函数调用

遇到中文或特殊字符时 ILIKE 为什么有时不生效？

ILIKE 依赖当前数据库的 collation 规则，默认 en_US.utf8 对中文其实没“大小写”概念，所以中文字母混排时（如 “UserAdmin123”）能正常匹配，但纯中文基本不受影响 —— 这不是 bug，是预期行为。

容易踩的坑：

PostgreSQL 18.4 ubuntu

PostgreSQL 18.4 官方 Ubuntu 安装包现已发布，这是目前最新的稳定版本。推荐通过官方 APT 仓库安装：先执行 sudo apt update 更新索引，再运行 sudo apt install postgresql-18 即可完成部署。新版本引入了异步 I/O 子系统，在顺序扫描与 VACUUM 场景下性能提升显著，同时支持 UUID v7 原生生成函数与虚拟生成列。

下载

• 把 ILIKE 和正则 ~<em></em> 混用：~ 功能更强但更慢，简单模糊查优先用 ILIKE
• 在 WHERE 中对字段套函数再 ILIKE，比如 WHERE upper(name) ILIKE '%JOHN%' —— 多余，直接 name ILIKE '%john%' 更清晰且等效
• 使用 ESCAPE 时忘记转义特殊字符：WHERE tag ILIKE '%100\% off%' ESCAPE '\'，否则 % 被当成通配符

要不要换成 citext 类型来一劳永逸？

citext 是 PostgreSQL 的扩展类型（需 CREATE EXTENSION citext;），把字段声明为 citext 后，所有比较（=、>、IN、甚至 ORDER BY）都自动不区分大小写，连 LIKE 都变成类 ILIKE 行为。

但它不是银弹：

• citext 比 text 略慢（每次比较都做 lower()）
• 无法控制“何时区分/不区分”，全局生效，可能破坏业务逻辑（比如密码哈希值不该用 citext）
• 和某些 ORM（如 Django 的 CharField）配合时需显式指定类型，否则迁移可能失败
• 如果只在少数查询里需要不区分大小写，用 ILIKE 更轻量、意图更明确

真正该换 citext 的场景，是那些天然就该忽略大小写的字段，比如邮箱、用户名、API key —— 而不是所有字符串字段。

记住：ILIKE 是按需启用的开关，citext 是给字段打上的永久标签。选哪个，取决于你是在写一条查询，还是在定义数据契约。