SQLite3“LIKE"的通用Ruby解决方案或 PostgreSQL “ILIKE"?

2021-12-02 00:00:00 postgresql pattern-matching ruby-on-rails search sqlite

我使用 SQLite3 进行开发，使用 PostgreSQL 进行部署.但是，我面临以下问题:

I am using SQLite3 for development and PostgreSQL for deployment. However, I am facing the following problem:

我使用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来解决问题:

However, it doesn't work for PostgreSQL, and I need to replace the LIKE for ILIKE to solve the problem:

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

是否有一种Ruby 方式"可以在任何数据库中进行这些搜索?

Is there a "Ruby way" to do these searches across any database?

编辑 - 根据您的回答，我不相信我会为此找到通用的 Ruby 解决方案.

EDIT - based on your answers I don't believe I will find a generic Ruby solution for that.

我遵循了 Ruby on Rails 教程:通过示例学习 Rails - Michael Hartl，其中最后的 Gemfile 显示了两个数据库......好吧，令人失望......

I have followed the Ruby on Rails Tutorial: Learn Rails by Example - by Michael Hartl, where the final Gemfile shows both databases... well, disappointing...

推荐答案

问题的根源就在这里:

我使用 SQLite3 进行开发，使用 PostgreSQL 进行部署.

I am using SQLite3 for development and PostgreSQL for deployment.

这是个坏主意™.您将不断遇到不兼容问题 - 或者更糟:在损坏完成之前不会意识到一些不兼容问题.
使用相同的 RDBMS (PostgreSQL) 进行开发和生产，省去毫无意义的麻烦.

That's a bad idea™. You will keep running into incompatibilities - or worse: not realize some until damage is done.
Use the same RDBMS (PostgreSQL) for development and production and save yourself the pointless trouble.

虽然您被不幸的设置困住了，但有一个简单的解决方法:

While you are stuck with your unfortunate setup, there is a simple fix:

lower(style) LIKE lower(?)

在两个平台上都适用.

  • 如果您提供小写搜索模式，您可以删除右侧的 lower().

在标准 SQLite lower(X) 中只折叠 ASCII 字母.有关更多信息，我引用了SQLite 手册中的核心函数一章:

In standard SQLite lower(X) only folds ASCII letters. For more, I quote the chapter Core Functions in the SQLite manual:

lower(X) 函数返回字符串 X 的副本，其中包含所有ASCII 字符转换为小写.默认内置的lower()函数仅适用于 ASCII 字符.进行大小写转换非 ASCII 字符，加载 ICU 扩展.

The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.

强调我的.

PostgreSQL lower(X) 开箱即用，支持 UTF-8.

PostgreSQL lower(X) works with UTF-8 out of the box.

作为一个受欢迎的副作用，您可以使用 加速该查询.html" rel="noreferrer">表达式上的索引 lower(style)，这将比使用 ILIKE 和 样式.

As a welcome side effect, you can speed up that query in PostgreSQL with an index on the expression lower(style), which will be faster than using ILIKE and a basic index on style.

此外，从 PostgreSQL 9.1 开始，您可以将 GIN 或 GIST 索引与 pg_trgm 扩展 以加快any LIKE 和 ILIKE 查询 - 三元组不区分大小写.此相关答案中的详细说明和链接:

Also, since PostgreSQL 9.1 you can use a GIN or GIST index with the pg_trgm extension to speed up any LIKE and ILIKE query - trigrams are case-insensitive. Detailed instructions and links in this related answer:

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

相关文章