Ruby on Rails使用WHERE子句实现搜索功能结果异常求助

问题根因 #

你遇到的问题本质是SQL LIKE查询的大小写敏感性导致的：数据库中存储的firstname、lastname字段值为Mose、Collins（首字母大写，其余小写），默认大小写敏感的规则下，小写的m、c无法匹配大写的首字母，而o、se等字符和字段中的小写内容完全一致，所以可以正常返回结果。

排查步骤 #

• 直接在数据库客户端执行测试SQL验证：执行SELECT * FROM users WHERE firstname LIKE '%m%' OR lastname LIKE '%c%';，如果执行后无返回结果，即可确认是大小写敏感问题。
• 确认搜索参数传递正确性：在控制器create方法中加入日志打印Rails.logger.info "传入搜索词：#{@word}"，调用接口后查看日志中打印的搜索词是否和你输入的内容一致，排除参数丢失、转义错误的问题。
• 大小写一致搜索验证：输入大写的M、C、Co进行搜索，如果可以正常返回结果，即可完全定位根因为大小写匹配问题。

解决方案 #

方案1：修改查询语句适配大小写不敏感 #

根据你使用的数据库选择对应写法：

• PostgreSQL/SQLite：用大小写不敏感的ILIKE关键字替换LIKE即可

@users = User.where("firstname ILIKE ? OR lastname ILIKE ?", "%#{@word}%", "%#{@word}%")
@posts = Post.where("body ILIKE ?", "%#{@word}%")

• MySQL：默认配置下LIKE本身大小写不敏感，如果你修改过数据库排序规则为大小写敏感，可以用LOWER函数统一转换为小写后匹配

@users = User.where("LOWER(firstname) LIKE LOWER(?) OR LOWER(lastname) LIKE LOWER(?)", "%#{@word}%", "%#{@word}%")
@posts = Post.where("LOWER(body) LIKE LOWER(?)", "%#{@word}%")

方案2：使用Arel写跨数据库兼容的查询 #

如果你的项目需要兼容多种数据库，可以用Rails自带的Arel查询构造器，它会自动适配不同数据库的大小写不敏感查询语法：

# 用户搜索逻辑
user_arel = User.arel_table
@users = User.where(
  user_arel[:firstname].matches("%#{@word}%")
  .or(user_arel[:lastname].matches("%#{@word}%"))
)
# 帖子搜索逻辑
post_arel = Post.arel_table
@posts = Post.where(post_arel[:body].matches("%#{@word}%"))

优化建议 #

如果你的用户、帖子数据量超过万级，%关键词%的前后模糊查询无法走普通索引，查询性能会明显下降，建议后续可以：

• 给对应搜索字段加全文检索索引
• 引入Ransack、PgSearch等专业搜索类库简化搜索逻辑开发，同时支持更高性能的全文检索能力

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