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数据库进行开发，但是部署到Heroku使用Postgres。Heroku几乎处理所有事情，但是我的大小写不敏感的语句变得大小写敏感。我可以使用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兼容的不区分大小写查询的最佳方式是什么?或者我是否需要根据我的应用与DB对话而分别编写Like和iLike语句?
11 个解决方案
#1
58
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.
如果在调用者中将参数设置为大写，则可以避免第二个函数调用。
#2
74
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.
最后你会发现在dev中不能复制的bug;你的测试将毫无价值。只是不要这样做。
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)
使用不同的数据库引擎是不可能的——有更多的情况下，它的行为会与类似的不同(另外，您是否检查过数据库使用的排序?)它们在任何情况下都是一样的吗?如果不是，您可以忘记在varchar列上使用相同的命令)
#3
36
Use Arel:
用服装:
Author.where(Author.arel_table[:name].matches("%foo%"))
matches will use the ILIKE operator for Postgres, and LIKE for everything else.
match将对Postgres使用ILIKE操作符，以及其他所有操作。
#4
13
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');
#5
8
There are several answers, none of which are very satisfactory.
有几个答案，没有一个是非常令人满意的。
- LOWER(bar) = LOWER(?) will work on 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.
- low (bar) = LOWER(?)可以用在MySQL和Postgres上，但是很可能在MySQL上表现得很糟糕:MySQL由于功能较低，所以不会使用索引。在Postgres上，您可以添加一个函数索引(在较低的栏上)，但是MySQL不支持这一点。
- MySQL will (unless you have set a case-sensitive collation) do case-insensitive matching automatically, and use its indexes. (bar = ?).
- MySQL将(除非您设置了区分大小写的排序)自动执行不区分大小写的匹配，并使用它的索引。(bar = ?)。
- From your code outside the database, maintain bar and bar_lower fields, 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.
- 在数据库之外的代码中，维护bar和bar_lower字段，bar_lower字段包含lower(bar)的结果。(也可以使用数据库触发器)。(请参阅Drupal上对这个解决方案的讨论)。这很笨拙，但至少在几乎所有数据库上都是这样运行的。
#6
5
REGEXP is case insensitive (unless used with BINARY), and can be used, like so...
REGEXP是不区分大小写的(除非与二进制一起使用)，并且可以使用，比如……
SELECT id FROM person WHERE name REGEXP 'john';
...to match 'John', 'JOHN', 'john', etc.
…匹配“约翰”、“约翰”、“约翰”等等。
#7
2
If you're using PostgreSQL 8.4 you can use the citext module to create case insensitive text fields.
如果使用PostgreSQL 8.4，则可以使用citext模块来创建大小写不敏感的文本字段。
#8
2
use COLLATE.
使用核对。
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
#9
1
You might also consider checking out the searchlogic plugin, which does the LIKE/ILIKE switch for you.
您还可以考虑检查searchlogic插件，它为您提供LIKE/ILIKE切换。
#10
1
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"
两者都会出现在"一些不均匀的文本"只有前者会出现在"一些不均匀的文本"
#11
0
Converting to upper is best as it covers compatible syntax for the 3 most-used Rails database backends. PostgreSQL, MySQL and SQLite all support this syntax. It has the (minor) drawback that you have to uppercase your search string in your application or in your conditions string, making it a bit uglier, but I think the compatibility you gain makes it worthwile.
转换为upper是最好的，因为它涵盖了3个最常用的Rails数据库后端的兼容语法。PostgreSQL、MySQL和SQLite都支持这种语法。它有(次要的)缺点，你必须在你的应用程序或你的条件字符串中使用大写的搜索字符串，使它有点难看，但我认为你获得的兼容性使它值得。
Both MySQL and SQLite3 have a case-insensitive LIKE operator. Only PostgreSQL has a case-sensitive LIKE operator and a PostgreSQL-specific (per the manual) ILIKE operator for case-insensitive searches. You might specify ILIKE insead of LIKE in your conditions on the Rails application, but be aware that the application will cease to work under MySQL or SQLite.
MySQL和SQLite3都有一个不区分大小写的操作符。只有PostgreSQL具有区分大小写的操作符和特定于PostgreSQL(根据手册)的类ILIKE操作符，用于不区分大小写的搜索。您可以在Rails应用程序的条件中指定ILIKE insead，但请注意，该应用程序将在MySQL或SQLite下停止工作。
A third option might be to check which database engine you're using and modify the search string accordingly. This might be better done by hacking into / monkeypatching ActiveRecord's connection adapters and have the PostgreSQL adapter modify the query string to substitute "LIKE" for "ILIKE" prior to query execution. This solution is however the most convoluted and in light of easier ways like uppercasing both terms, I think this is not worh the effort (although you'd get plenty of brownie points for doing it this way).
第三个选项可能是检查正在使用的数据库引擎，并相应地修改搜索字符串。通过入侵/ monkeypatching ActiveRecord的连接适配器，并让PostgreSQL适配器在查询执行之前修改查询字符串，将“LIKE”替换为“ILIKE”，这可能会更好。然而，这个解决方案是最复杂的，而且考虑到更简单的方法，如大写两项，我认为这不是值得努力的(尽管这样做会得到很多印象分)。
#1
58
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.
如果在调用者中将参数设置为大写，则可以避免第二个函数调用。
#2
74
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.
最后你会发现在dev中不能复制的bug;你的测试将毫无价值。只是不要这样做。
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)
使用不同的数据库引擎是不可能的——有更多的情况下，它的行为会与类似的不同(另外，您是否检查过数据库使用的排序?)它们在任何情况下都是一样的吗?如果不是，您可以忘记在varchar列上使用相同的命令)
#3
36
Use Arel:
用服装:
Author.where(Author.arel_table[:name].matches("%foo%"))
matches will use the ILIKE operator for Postgres, and LIKE for everything else.
match将对Postgres使用ILIKE操作符，以及其他所有操作。
#4
13
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');
#5
8
There are several answers, none of which are very satisfactory.
有几个答案，没有一个是非常令人满意的。
- LOWER(bar) = LOWER(?) will work on 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.
- low (bar) = LOWER(?)可以用在MySQL和Postgres上，但是很可能在MySQL上表现得很糟糕:MySQL由于功能较低，所以不会使用索引。在Postgres上，您可以添加一个函数索引(在较低的栏上)，但是MySQL不支持这一点。
- MySQL will (unless you have set a case-sensitive collation) do case-insensitive matching automatically, and use its indexes. (bar = ?).
- MySQL将(除非您设置了区分大小写的排序)自动执行不区分大小写的匹配，并使用它的索引。(bar = ?)。
- From your code outside the database, maintain bar and bar_lower fields, 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.
- 在数据库之外的代码中，维护bar和bar_lower字段，bar_lower字段包含lower(bar)的结果。(也可以使用数据库触发器)。(请参阅Drupal上对这个解决方案的讨论)。这很笨拙，但至少在几乎所有数据库上都是这样运行的。
#6
5
REGEXP is case insensitive (unless used with BINARY), and can be used, like so...
REGEXP是不区分大小写的(除非与二进制一起使用)，并且可以使用，比如……
SELECT id FROM person WHERE name REGEXP 'john';
...to match 'John', 'JOHN', 'john', etc.
…匹配“约翰”、“约翰”、“约翰”等等。
#7
2
If you're using PostgreSQL 8.4 you can use the citext module to create case insensitive text fields.
如果使用PostgreSQL 8.4，则可以使用citext模块来创建大小写不敏感的文本字段。
#8
2
use COLLATE.
使用核对。
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
#9
1
You might also consider checking out the searchlogic plugin, which does the LIKE/ILIKE switch for you.
您还可以考虑检查searchlogic插件，它为您提供LIKE/ILIKE切换。
#10
1
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"
两者都会出现在"一些不均匀的文本"只有前者会出现在"一些不均匀的文本"
#11
0
Converting to upper is best as it covers compatible syntax for the 3 most-used Rails database backends. PostgreSQL, MySQL and SQLite all support this syntax. It has the (minor) drawback that you have to uppercase your search string in your application or in your conditions string, making it a bit uglier, but I think the compatibility you gain makes it worthwile.
转换为upper是最好的，因为它涵盖了3个最常用的Rails数据库后端的兼容语法。PostgreSQL、MySQL和SQLite都支持这种语法。它有(次要的)缺点，你必须在你的应用程序或你的条件字符串中使用大写的搜索字符串，使它有点难看，但我认为你获得的兼容性使它值得。
Both MySQL and SQLite3 have a case-insensitive LIKE operator. Only PostgreSQL has a case-sensitive LIKE operator and a PostgreSQL-specific (per the manual) ILIKE operator for case-insensitive searches. You might specify ILIKE insead of LIKE in your conditions on the Rails application, but be aware that the application will cease to work under MySQL or SQLite.
MySQL和SQLite3都有一个不区分大小写的操作符。只有PostgreSQL具有区分大小写的操作符和特定于PostgreSQL(根据手册)的类ILIKE操作符，用于不区分大小写的搜索。您可以在Rails应用程序的条件中指定ILIKE insead，但请注意，该应用程序将在MySQL或SQLite下停止工作。
A third option might be to check which database engine you're using and modify the search string accordingly. This might be better done by hacking into / monkeypatching ActiveRecord's connection adapters and have the PostgreSQL adapter modify the query string to substitute "LIKE" for "ILIKE" prior to query execution. This solution is however the most convoluted and in light of easier ways like uppercasing both terms, I think this is not worh the effort (although you'd get plenty of brownie points for doing it this way).
第三个选项可能是检查正在使用的数据库引擎，并相应地修改搜索字符串。通过入侵/ monkeypatching ActiveRecord的连接适配器，并让PostgreSQL适配器在查询执行之前修改查询字符串，将“LIKE”替换为“ILIKE”，这可能会更好。然而，这个解决方案是最复杂的，而且考虑到更简单的方法，如大写两项，我认为这不是值得努力的(尽管这样做会得到很多印象分)。