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

2022-01-31 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 的简单搜索:

My simple search using SQLite3:

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

但是PostgreSQL不行，需要把ILIKE的LIKE换成ILIKE解决:p>

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(?)

在两个平台上都可以工作.

Works on both platforms alike.

  • 如果您提供小写搜索模式，您可以删除右侧的 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 下(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 查询 - trigrams不区分大小写.此相关答案中的详细说明和链接:

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 字符串

相关文章