Doctrine + Symfony + PostgreSQL 实现忽略重音符号的模糊查询
重音不敏感的人员搜索实现方案
问题根源
当前代码仅做了大小写转换,未对带重音的变音字符(如á、é、í、ó、ú)做字符归一化处理,导致数据库在匹配时会将a和á判定为完全不同的字符串,出现输入无重音值搜不到带重音记录、输入带重音值搜不到无重音记录的问题。
可落地的实现方式
方案一:利用数据库内置的重音不敏感排序规则(最简单高效)
主流数据库(MySQL、PostgreSQL)都原生支持重音不敏感的排序规则,直接在查询匹配时强制指定该规则即可实现自动归一化,不需要额外开发逻辑:
function search($value) { $qb = $this->createQueryBuilder('p'); $qb->where( $qb->expr()->like( // 匹配时强制指定重音不敏感的排序规则,自动识别带/不带重音的等价字符 "LOWER(p.lastName) COLLATE utf8mb4_general_ci", "%" . mb_strtolower($value) . "%" ) ); return $qb->getQuery()->getResult(); }
这种方式下,数据库会在执行匹配时自动将带重音的字符映射到对应的无重音基础字符,输入Sanchez或Sánchez都能同时命中两条示例记录,且可以正常利用字段索引,查询性能不受影响。
方案二:跨数据库兼容的自定义归一化函数
如果需要在不同数据库间保持统一的匹配逻辑,可以通过Doctrine注册自定义DQL函数,实现对字段值的去重音处理,同时在应用层对用户输入的搜索值做相同的归一化处理:
- 注册Doctrine自定义
UNACCENT函数,函数内部根据使用的数据库类型,实现对字段值的重音剥离逻辑(PostgreSQL可直接调用内置unaccent()函数,MySQL可通过嵌套REPLACE语句实现字符替换) - 调整搜索方法逻辑:
function search($value) { // 对用户输入值先做重音剥离、转小写的归一化处理 $normalizedInput = mb_strtolower( str_replace( ['á','é','í','ó','ú','Á','É','Í','Ó','Ú'], ['a','e','i','o','u','a','e','i','o','u'], $value ) ); $qb = $this->createQueryBuilder('p'); // 查询时对数据库存储的姓名字段做相同的去重音处理,再做模糊匹配 $qb->where( $qb->expr()->like( 'UNACCENT(LOWER(p.lastName))', "'%" . $normalizedInput . "%'" ) ); return $qb->getQuery()->getResult(); }
注意事项
- 不要为了实现匹配把全量数据从数据库拉到PHP层再做过滤,数据量较大时会造成严重的性能损耗
- 选择排序规则时需要和数据库当前配置的字符集保持匹配,避免出现语法兼容问题
- 编写查询条件时尽量使用Doctrine提供的参数绑定方式,不要直接把用户输入拼接到DQL语句中,避免SQL注入风险
内容的提问来源于stack exchange,提问作者Luis Miguel
相关产品推荐
相关产品推荐

