你如何为 MySQL 和 Postgres 编写不区分大小写的查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/203399/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you write a case insensitive query for both MySQL and Postgres?
提问by DA.
I'm running a MySQL database locally for development, but deploying to Heroku which uses Postgres. Heroku handles almost everything, but my case-insensitive Like statements become case sensitive. I could use iLike statements, but my local MySQL database can't handle that.
我在本地运行 MySQL 数据库进行开发，但部署到使用 Postgres 的 Heroku。Heroku 处理几乎所有事情，但我不区分大小写的 Like 语句变得区分大小写。我可以使用 iLike 语句，但我的本地 MySQL 数据库无法处理。
What is the best way to write a case insensitive query that is compatible with both MySQL and Postgres? Or do I need to write separate Like and iLike statements depending on the DB my app is talking to?
编写与 MySQL 和 Postgres 兼容的不区分大小写的查询的最佳方法是什么？或者我是否需要根据我的应用程序正在与之交谈的数据库编写单独的 Like 和 iLike 语句？
采纳答案by Paul Tomblin
select * from foo where upper(bar) = upper(?);
If you set the parameter to upper case in the caller, you can avoid the second function call.
如果在调用者中将参数设置为大写，则可以避免第二次函数调用。
回答by MarkR
The moral of this story is: Don't use a different software stack for development and production. Never.
这个故事的寓意是：不要使用不同的软件堆栈进行开发和生产。绝不。
You'll just end up with bugs which you can't reproduce in dev; your testing will be worthless. Just don't do it.
您最终会遇到无法在开发中重现的错误；你的测试将毫无价值。只是不要这样做。
Using a different database engine is out of the question - there will be FAR more cases where it behaves differently than just LIKE (also, have you checked the collations in use by the databases? Are they identical in EVERY CASE? If not, you can forget ORDER BY on varchar columns working the same)
使用不同的数据库引擎是不可能的 - 将会有更多的情况，它的行为与 LIKE 不同（此外，您是否检查过数据库使用的排序规则？它们在每种情况下都相同吗？如果不是，您可以忘记工作相同的 varchar 列上的 ORDER BY）
回答by jswanner
Use Arel:
使用阿瑞尔：
Author.where(Author.arel_table[:name].matches("%foo%"))
matcheswill use the ILIKEoperator for Postgres, and LIKEfor everything else.
matches将对ILIKEPostgres 和LIKE其他所有内容使用运算符。
回答by Adam Pierce
In postgres, you can do this:
在 postgres 中，你可以这样做：
SELECT whatever FROM mytable WHERE something ILIKE 'match this';
I'm not sure if there is an equivalent for MySQL but you can always do this which is a bit ugly but should work in both MySQL and postgres:
我不确定是否有 MySQL 的等价物，但您总是可以这样做，这有点难看，但应该在 MySQL 和 postgres 中都可以使用：
SELECT whatever FROM mytable WHERE UPPER(something) = UPPER('match this');
回答by tims
There are several answers, none of which are very satisfactory.
有几个答案，没有一个是非常令人满意的。
- LOWER(bar) = LOWER(?)will workon MySQL and Postgres, but is likely to perform terribly on MySQL: MySQL won't use its indexes because of the LOWER function. On Postgres you can add a functional index (on LOWER(bar)) but MySQL doesn't support this.
- MySQL will (unless you have set a case-sensitive collation) do case-insensitive matching automatically, and use its indexes. (bar = ?).
- From your code outside the database, maintain barand bar_lowerfields, where bar_lower contains the result of lower(bar). (This may be possible using database triggers, also). (See a discussion of this solution on Drupal). This is clumsy but does at least run the same way on pretty much every database.
- LOWER(bar) = LOWER(?)将在 MySQL 和 Postgres 上工作，但在 MySQL 上可能表现不佳：由于 LOWER 函数，MySQL 不会使用其索引。在 Postgres 上，您可以添加功能索引（在LOWER(bar) 上），但 MySQL 不支持此功能。
- MySQL 将（除非您设置了区分大小写的排序规则）自动进行不区分大小写的匹配，并使用其索引。（酒吧=？）。
- 从数据库外部的代码中，维护bar和bar_lower字段，其中 bar_lower 包含lower(bar)的结果。（这也可以使用数据库触发器）。（请参阅Drupal上对此解决方案的讨论）。这很笨拙，但至少在几乎每个数据库上都以相同的方式运行。
回答by Ben Wilhelm
REGEXP is case insensitive (unless used with BINARY), and can be used, like so...
REGEXP 不区分大小写（除非与 BINARY 一起使用），并且可以像这样使用...
SELECT id FROM person WHERE name REGEXP 'john';
...to match 'John', 'JOHN', 'john', etc.
...匹配“约翰”、“约翰”、“约翰”等。
回答by RuelB
回答by MkV
回答by Sheldon Ross
You can also use ~* in postgres if you want to match a substring within a block. ~ matches case-sensitive substring, ~* case insensitive substring. Its a slow operation, but might I find it useful for searches.
如果要匹配块中的子字符串，也可以在 postgres 中使用 ~*。~ 匹配区分大小写的子串， ~* 不区分大小写的子串。这是一个缓慢的操作，但我可能会发现它对搜索有用。
Select * from table where column ~* 'UnEvEn TeXt';
Select * from table where column ~ 'Uneven text';
Both would hit on "Some Uneven text here" Only the former would hit on "Some UNEVEN TEXT here"
两者都会点击“这里有一些不均匀的文字”只有前者会点击“这里有一些不均匀的文字”
回答by Trevor Turk
You might also consider checking out the searchlogicplugin, which does the LIKE/ILIKEswitch for you.
您也可以考虑查看searchlogic插件，它为您执行LIKE/ILIKE开关。
