如何在MySQL和Postgres中编写不区分大小写的查询？

Question

我在本地开发中运行MySQL数据库，但是部署到使用Postgres的Heroku时遇到了问题。 Heroku几乎处理所有内容，但是我的不区分大小写的Like语句变成了区分大小写。 我可以使用iLike语句，但是我的本地MySQL数据库无法处理。

有没有一种最佳方法编写不区分大小写的查询，可以兼容MySQL和Postgres？还是我需要根据我的应用程序连接的数据库编写单独的Like和iLike语句？

- DA

---

如果您在生产环境中使用Postgres，请在本地也使用Postgres。这不会是您遇到的第一个问题，而且这还意味着您无法利用任何Postgres特有的功能。 - Samuel Neff

也许在你提出这个问题时安装和运行Postgres很困难，但现在使用Docker运行Postgres非常容易。 - Andy

Answer 1

这个故事的寓意是：不要在开发和生产环境中使用不同的软件堆栈。永远都不要这样做。

否则你最终会遇到一些无法在开发环境中重现的 bug，你的测试就毫无用处了。千万别这么做。

使用不同的数据库引擎也是行不通的——行为差异情况太多了，远远不止仅仅是“像”这种程度（而且，你有检查过数据库中所使用的排序规则吗？它们在每种情况下都是相同的吗？如果不是，那么你可以忘记在 varchar 列上使用 ORDER BY 时它们能够正常工作了）。

- MarkR

---

谢谢你的道德支持，加1！但说真的，这是正确的答案，比任何“只回答问题”的答案都要好得多。虽然我通常不喜欢这种寓意式的回答，但在这种情况下做得很好。 - Dan Rosenstark

AR/AM的整个目的是允许您在开发和生产中使用不同的数据库后端。在我看来，问题在于AR/AM生成查询的方式。 - Christopher Maujean

我希望我没有在这里胡说八道，但是我对这个回答有两个反对意见。首先，这不是对问题的回答。其次，在开发和生产中使用不同的数据库并不是主要问题。真正的问题在于没有使用抽象来执行不区分大小写的查询。理想情况下，活动记录会提供这种能力，而不必深入到特定于供应商的 SQL。我的建议是看一下 https://dev59.com/3XE95IYBdhLWcg3wp_gg 中的建议。 - M. Scott Ford

当然，你的建议非常正确，但是提问者的问题仍然是有意义的。编写符合SQL标准和平台无关的查询语句具有一定的价值，特别是如果未来有可能将系统移植到另一个数据库中。显然，在这种情况下没有完美的答案。 - mtjhax

对于任何非平凡项目，数据库抽象都是一个非常泄漏的问题；过度依赖它会导致问题。除非您正在构建支持多个数据库作为功能的终端用户成品解决方案，否则应始终保持警惕，不要让失败的抽象泄漏到代码库中，并进行强大的测试。根据我的经验，这往往不值得。 - Halil Özgür

Answer 2

select * from foo where upper(bar) = upper(?);

如果在调用者中将参数设置为大写，您可以避免第二个函数调用。

- Paul Tomblin

---

你可以确保将其大写：WHERE UPPER(bar) = UPPER(?)。 - Bill Karwin

我不确定百分之百，但我记得这将不使用foo上可能存在的任何索引，因为它无法扫描函数返回值与索引。 - richo

@Richo：如果需要，你可以在 upper(bar) 上创建一个索引：http://www.postgresql.org/docs/current/interactive/sql-createindex.html - mu is too short

我建议在Ruby中将传递给Arel的参数大写，以避免SQL需要进行额外的处理。where("UPPER(bar) = ?", parameter.upcase) - scarver2

Answer 3

使用 Arel：

Author.where(Author.arel_table[:name].matches("%foo%"))

matches将使用Postgres的ILIKE运算符，而对于其他情况则使用LIKE。

- jswanner

---

希望我能给予超过+1的赞...我从来不知道Arel可以做到这一点！也许是因为它几乎没有文档记录？嗯... - Martin T.

这个有文档记录吗？ - Dogweather

Answer 4

在Postgres中，你可以这样做：

SELECT whatever FROM mytable WHERE something ILIKE 'match this';

我不确定MySQL是否有等效的方法，但你可以这样做，虽然比较丑陋，但在MySQL和PostgreSQL中都应该有效：

SELECT whatever FROM mytable WHERE UPPER(something) = UPPER('match this');

- Adam Pierce

Answer 5

有几个答案，但都不是非常令人满意。

- 在MySQL和Postgres上，LOWER(bar) = LOWER(?) 可以运行，但在MySQL上可能会表现得非常糟糕：MySQL不会使用索引因为LOWER函数的存在。在Postgres上，你可以添加一个函数索引（在LOWER(bar)上），但MySQL不支持这一点。 - MySQL将自动执行大小写不敏感的匹配（除非你设置了区分大小写的排序规则），并使用它的索引。（bar = ?）。 - 从数据库外部的代码中，维护bar和bar_lower字段，其中bar_lower包含lower(bar)的结果。（这也可以使用数据库触发器来实现）。 （请参阅Drupal上关于此解决方案的讨论）。 这很笨拙，但至少在几乎所有数据库上都可以运行。

- tims

---

好的，谢谢你提到的第二点，我发现它默认确实是不区分大小写的。 - ADTC

Answer 6

正则表达式是大小写不敏感的（除非与BINARY一起使用），可以像这样使用...

    SELECT id FROM person WHERE name REGEXP 'john';

...匹配'John'、'JOHN'、'john'等。

- Ben Wilhelm

---

太棒了！我可以使用“|”来拥有多个搜索关键字。 - ADTC

虽然正则表达式非常灵活，但请注意它们的速度相对较慢，在处理大数据集或慢服务器时会更加明显。 - aydow

Answer 7

如果您使用的是PostgreSQL 8.4，可以使用citext模块创建不区分大小写的文本字段。

- MkV

---

或者添加一个功能性索引：http://www.postgresql.org/docs/7.3/static/indexes-functional.html - troelskn

Answer 8

使用COLLATE。

请参考此链接：http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

- RuelB

Answer 9

如果您想在一个块中匹配子字符串，您也可以使用postgres中的~*。 ~匹配区分大小写的子字符串，~*则匹配不区分大小写的子字符串。这是一个较慢的操作，但对于搜索可能会很有用。

Select * from table where column ~* 'UnEvEn TeXt';
Select * from table where column ~ 'Uneven text';

两者都会匹配 "Some Uneven text here" 只有前者才会匹配 "Some UNEVEN TEXT here"

- Sheldon Ross

Answer 10

你可能还可以考虑查看 searchlogic 插件，它可以为您执行 LIKE/ILIKE 开关。

- Trevor Turk