如何在knex的whereRaw查询中正确使用参数绑定实现全名模糊搜索

问题修复方案 #

1. 现有参数绑定的正确写法 #

你当前代码的错误有两点：

• ??是knex的标识符占位符，仅用于转义表名、字段名这类SQL标识符，绑定普通查询值需要用单个?
• 通配符%不要硬编码在SQL语句的引号内，应该和搜索值拼接后作为绑定参数传入，避免语法解析错误

正确代码如下：

const searchUser = (query) => {
  // 提前把通配符和搜索值拼接
  const name = `%${query.toLowerCase()}%`;
  return knex('users')
    .select('*')
    .whereRaw('concat(LOWER(first_name), \' \' , LOWER(last_name)) LIKE ?', [name]);
};

2. 不使用concat和whereRaw的实现方案 #

如果不想写全量原生SQL语句，可以根据你的业务场景选择以下两种方案：

方案1：仅用局部raw拼接字段，搭配whereILike方法

knex内置的whereILike方法会自动处理大小写匹配，仅需要用局部raw拼接两个字段即可：

const searchUser = (query) => {
  const name = `%${query.toLowerCase()}%`;
  return knex('users')
    .select('*')
    // 不同数据库的拼接符兼容可以替换为knex.fn.concat方法
    .whereILike(knex.raw('?? || \' \' || ??', ['first_name', 'last_name']), name);
};

方案2：完全不用raw相关API，拆分关键词匹配

如果你的业务场景不需要严格匹配「名+姓」的连续顺序，可以拆分搜索关键词分别匹配两个字段，完全不需要拼接字段：

const searchUser = (query) => {
  // 按空格拆分所有搜索关键词
  const keywords = query.trim().split(/\s+/);
  const queryBuilder = knex('users').select('*');
  keywords.forEach(keyword => {
    const matchStr = `%${keyword}%`;
    // 每个关键词都需要匹配到名或者姓
    queryBuilder.where(sub => {
      sub.whereILike('first_name', matchStr)
        .orWhereILike('last_name', matchStr);
    });
  });
  return queryBuilder;
};

内容的提问来源于stack exchange，提问作者ryanzplee