Rails MySQL ILIKE查询

Question

我的语法有误。我想要创建一个名为by_name的作用域，找到所有名称属性中包含传入字符串（不区分大小写）的agencies。

这是我拥有的：

class Agency < ActiveRecord::Base
  scope :by_name, ->(agency_name) { where('name ILIKE ?', "%#{agency_name}%") }
end

在Rails控制台中，我输入了agencies = Agency.by_name("foo")。这是生成的查询：

SELECT `agencies`.* FROM `agencies`  WHERE (name ILIKE '%foo%')

以下是错误消息：

Mysql2::Error: 您的SQL语法有误；请检查与您的MySQL服务器版本相对应的手册，了解正确使用语法的方法 'ILIKE '%foo%')

- Neil

Answer 1

我认为应该是：

 scope :by_name, lambda { |agency_name| 
   where('name LIKE ?', "%#{agency_name}%") # not ILIKE
 }

PostgreSQL中可以使用关键字ILIKE（而不是LIKE）进行模糊匹配，根据活动的语言环境，匹配时不区分大小写。这不属于SQL标准，但是它是PostgreSQL的扩展。

在MySQL中没有ILIKE关键字，可以查看MySQL字符串比较函数文档。

---

Bonus - 你也可以使用Arel。看一下：

scope :by_name, lambda { |agency_name| 
  where(Agency.arel_table[:name].matches("%#{agency_name}%"))
}

- Andrey Deineko

Answer 2

为什么是这样呢？因为在MySQL中并没有ILIKE这个东西，只有LIKE。你可能在PostgreSQL中看到了ILIKE，它是LIKE的不区分大小写版本，但你当前使用的关系型数据库管理系统与此不同。

最好的方法是在两侧使用LOWER函数来实现大致相等的效果：

.where('LOWER(name) LIKE LOWER(?)', "%#{agency_name}%")

感谢 @mu is too short 提供的 答案。

- D-side

---

这不是一个理想的解决方案，因为如果在name列上存在索引，查询优化器将无法使用它。更好的解决方案是将表的排序规则设置为utf8_general_ci——ci表示不区分大小写，这样做将使所有文本匹配不区分大小写。 - Gary Benade

@GaryBenade 如果我想让用户在大小写敏感标志开启或关闭的情况下进行搜索怎么办？ - Vael Victus

@VaelVictus 这可能会有点超出 MySQL 的本意，但由于缺乏功能性索引，两个版本（关于大小写不敏感）都可以被存储和索引。或者可以使用类似 ElasticSearch 的单独搜索服务器来实现这个目的。 - D-side