通用的 Ruby 解决方案，用于 SQLite3 的 "LIKE" 或 PostgreSQL 的 "ILIKE"？

Question

我在开发中使用SQLite3，部署时使用PostgreSQL。然而，我遇到了以下问题：

我的简单搜索使用SQLite3：

def self.search(search)
    if search
      find(:all, :conditions => ["style LIKE ? OR construction LIKE ?", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
end

但是，它不能用于PostgreSQL，我需要将LIKE替换为ILIKE来解决这个问题:

def self.search(search)
    if search
      find(:all, :conditions => ["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
end

有没有“Ruby方式”可以跨任意数据库执行这些搜索？

编辑 - 根据你们的回答，我不认为我会找到通用的Ruby解决方案。

我遵循了Michael Hartl撰写的Ruby on Rails教程：通过示例学习Rails，其中最终的Gemfile显示了两个数据库... 嗯，令人失望...

- gabrielhilal

---

我了解这并不是直接回答你的问题，但我认为如果你的开发环境（SQLite）与生产环境（PostgreSQL）不匹配，那么你将会遇到极大的麻烦。如果某些东西在开发环境中可行，那么如果数据库完全不同，你如何知道它在生产环境中是否可行？请注意，这不是解释，请问是否需要其他翻译？ - Cody Caughlan

Answer 1

问题的根源在于这里：

我在开发中使用SQLite3，在部署时使用PostgreSQL。

这是个不好的想法™。你会一直遇到不兼容的问题，或者更糟糕的是：在造成损害之前没有意识到问题。
在开发和生产中都使用相同的RDBMS（PostgreSQL），避免无谓的麻烦。

当你被困在不幸的设置中时，有一个简单的解决办法：

lower(style) LIKE lower(?)

适用于两个平台。

如果您提供小写搜索模式，则不需要使用右侧的lower()。

在标准SQLite中，lower(X)仅折叠ASCII字母。引用SQLite手册中的章节核心函数:

lower（X）函数返回将所有ASCII字符转换为小写的字符串X的副本。默认内置的lower()函数仅适用于ASCII字符。要对非ASCII字符进行大小写转换，请加载ICU扩展。

强调加粗。

PostgreSQL lower(X)可以直接处理UTF-8。

作为一个欢迎的副作用，您可以在PostgreSQL中使用表达式lower(style)的索引lower(style)来加速查询，这比使用ILIKE和基本索引style更快。

此外，自从PostgreSQL 9.1以来，您可以使用带有pg_trgm扩展的GIN或GIST索引来加速任何LIKE和ILIKE查询——trigram索引是不区分大小写的。详情请参阅：

• 类似UTF-8字符串的自动完成字段

- Erwin Brandstetter

---

非常感谢您提供的非常有用的信息，我会尝试使用它。 - gabrielhilal

我和OP处于同样的情况，因为我也是从Hartl的Rails教程开始学习。我想将我的开发数据库更改为Postgresql，以使其与我的开发数据库匹配。我需要知道哪些信息才能做到这一点？可能会有什么后果？ - Jeff Zivkovic

Answer 2

我认为Arel是解决这个问题的最佳方法。它被Rails用于active record，并且与数据库无关。你的代码在sqlite3或postgres中将会以相同的方式工作，这似乎符合你的情况。使用matches方法将自动在postgres环境中切换到ilike。

users=User.arel_table
User.where(users[:style].matches("%#{search}%").or(users[:construction].matches("%#{search}%")))

您可以从github获取更多信息：https://github.com/rails/arel/

- John

---

+1，这种方法在这个railscast中有讨论：http://railscasts.com/episodes/355-hacking-with-arel - Harish Shetty

Answer 3

没有“Ruby方式”来搜索数据库 - Ruby on Rails（特别是ActiveRecord）包含了用于执行CRUD操作的辅助方法，但使用LIKE进行搜索比你提供的示例没有更好的方法。与此讨论相关的Rails文档部分将是 ActiveRecord :: FinderMethods。顺便提一下，你可以直接使用all而不是做find(:all)。Rails文档使用与执行LIKE语句相同的语法，例如：

Person.exists?(['name LIKE ?', "%#{query}%"])

使用上述方法是完全安全的。

下面这种语句不安全的原因是因为where字符串直接传递到数据库查询中，没有任何净化处理，这会导致你的数据库容易受到攻击（例如，params[:first_name]中的一个简单的撇号可能会破坏整个查询并使你的数据库容易受到攻击——特别是SQL注入）。在上面的示例中，ActiveRecord可以净化你传递到查询中的参数。

Client.where("first_name LIKE '%#{params[:first_name]}%'")

- Michael Frederick

Answer 4

我曾经也遇到过同样的问题，这是我的解决方案：

我写了一个小库，使得可以为每个数据库使用该函数：

class AdapterSpecific

  class << self
    def like_case_insensitive
      case ActiveRecord::Base.connection.adapter_name
      when 'PostgreSQL'
        'ILIKE'
      else
        'LIKE'
      end
    end    

    def random
      #something
    end
  end

在您的模型中使用它：

def self.search(search)
    if search
      find(:all, :conditions => ["style #{AdapterSpecific.like_case_insensitive} :query OR construction #{AdapterSpecific.like_case_insensitive} :query", {:query => "%#{search}%"}])
    else
      find(:all)
    end
end

自从写下这篇文章后，我将部署数据库迁移到了Postgres，因为这是一个更好的设置，有很多原因（请参见其他答案）。

您可能还想使用Postgres的全文搜索，这将使您的文本搜索更加高效，请参见texticle获取基本实现或pg_search如果您需要更多定制化。

- Adrien Coquio

Answer 5

很遗憾，我认为你不会找到一个好的解决方案。除了使用 :conditions 语法之外，您可以使用 .where 子句：

where(["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])

很不幸，如你所料，这种备选语法会遇到完全相同的问题。这只是在开发环境与生产环境有显著不同的情况下可能会遇到的烦恼之一--SQLite和PostgresSQL还有其他相当大的差异。我建议在开发机器上安装Postgres并使用它。这将使开发更容易，并使您的代码更加清晰。

- Veraticus

---

非常感谢您的回复，但是在Rails指南上看到，它说“将自己的条件作为纯字符串构建可能会使您容易受到SQL注入攻击。例如，Client.where("first_name LIKE '%#{params[:first_name]}%'")是不安全的。”因此，我认为有一种更好的方法（“Ruby方式”）来解决这个问题。 - gabrielhilal

是的，那不是一个好方法，但你现在这样做也可以。Rails会在替换字符串之前对其进行清理。然而，你在注释中编写的代码并没有对字符串进行清理。 - rubish

Answer 6

虽然在生产和开发中使用不同的数据库并不是一个好的实践，但使用 squeel gem 仍然是一个好的选择：https://github.com/ernie/squeel/ 使用它，您可以通过 DSL 编写查询，这种方法比原始 SQL 更易于阅读和清洁，并且 gem 将处理其翻译为特定于所使用的 RDBMS 的 SQL。

Ryan Bates 在这个视频中讲解得非常好：http://railscasts.com/episodes/354-squeel。

- Greg Funtusov

Answer 7

使用LIKE进行搜索可能会对数据库造成痛苦。肯定不会使用索引，这可能是非常昂贵的。

更长的答案是：我建议在开发过程中使用Postgres（放弃sqlite3），然后通过Postgres的tsvector类型，在所有可搜索字段style, construction上拥有全文索引。

在使用索引时，Postgres的全文搜索非常快速。

- Cody Caughlan

---

PostgreSQL允许对类似和不区分大小写的主题进行索引。 - rubiii

@rubiii 你说得对，很好知道。看起来你必须确保创建索引并指定区域设置/操作符类以确保PG使用它。http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html - Cody Caughlan