blocks|key|1716382|text|select+*+from+foo+where+upper(bar)+=+upper(?);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1716383|如果在调用方中将参数设置为大写，则可以避免第二次函数调用。|unstyled|1716384|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select * from foo where upper(bar) = upper(?);
</code></pre>

If you set the parameter to upper case in the caller, you can avoid the second function call.

blocks|key|1716685|text|这个故事的寓意是:不要在开发和生产中使用不同的软件堆栈。绝不可能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1716686|你只会得到你不能在dev中重现的bug；你的测试将一文不值。不要这样做。|1716687|使用不同的数据库引擎是不可能的--将会有更多的情况下，它的行为不同于像这样(另外，您是否检查了数据库使用的排序规则？它们在每种情况下都是相同的吗？如果不是这样，您可以忘记在varchar列上使用ORDER+BY+)|1716688|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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)

blocks|key|1716858|text|使用Arel：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1716859|Author.where(Author.arel_table[:name].matches("%25foo%25"))|code-block|syntax|javascript|1716860|matches将对Postgres使用ILIKE运算符，对其他所有内容使用LIKE运算符。|offset|length|style|CODE|1716861|entityMap^0|0|0|0|7|J|5|11|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

Use Arel:

<pre><code>Author.where(Author.arel_table[:name].matches("%foo%"))
</code></pre>

<code>matches</code> will use the <code>ILIKE</code> operator for Postgres, and <code>LIKE</code> for everything else.

blocks|key|1716439|text|在postgres中，您可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1716440|SELECT+whatever+FROM+mytable+WHERE+something+ILIKE+'match+this';|code-block|syntax|javascript|1716441|我不确定是否有用于MySQL的等价物，但你总是可以这样做，这有点丑陋，但应该可以在MySQL和postgres中工作：|1716442|SELECT+whatever+FROM+mytable+WHERE+UPPER(something)+=+UPPER('match+this');|1716443|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In postgres, you can do this:

<pre><code>SELECT whatever FROM mytable WHERE something ILIKE 'match this';
</code></pre>

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:

<pre><code>SELECT whatever FROM mytable WHERE UPPER(something) = UPPER('match this');
</code></pre>

blocks|key|1501882|text|REGEXP不区分大小写(除非与二进制一起使用)，并且可以使用，如下所示...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1501883|++++SELECT+id+FROM+person+WHERE+name+REGEXP+'john';|code-block|syntax|javascript|1501884|...to匹配“john”、“john”、“john”等。|1501885|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

REGEXP is case insensitive (unless used with BINARY), and can be used, like so...

<pre><code> SELECT id FROM person WHERE name REGEXP 'john';
</code></pre>

...to match 'John', 'JOHN', 'john', etc.

blocks|key|1716748|text|如果您使用的是PostgreSQL+8.4，则可以使用citext模块创建不区分大小写的文本字段。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1716749|entityMap|0|LINK|mutability|MUTABLE|url|http://developer.postgresql.org/pgdocs/postgres/citext.html^0|R|6|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

If you're using PostgreSQL 8.4 you can use the <a href="http://developer.postgresql.org/pgdocs/postgres/citext.html" rel="nofollow noreferrer">citext</a> module to create case insensitive text fields.

blocks|key|1501830|text|使用COLLATE。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1501831|http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html|offset|length|1501832|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1O|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

use COLLATE.

<a href="http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html</a>

blocks|key|1716602|text|您还可以考虑查看searchlogic插件，它可以为您执行LIKE/ILIKE切换。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1716603|entityMap|0|LINK|mutability|MUTABLE|url|http://github.com/binarylogic/searchlogic|1|http://github.com/binarylogic/searchlogic/blob/master/lib/searchlogic/named_scopes/conditions.rb#L89^0|8|B|0|T|A|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

You might also consider checking out the <a href="http://github.com/binarylogic/searchlogic" rel="nofollow noreferrer">searchlogic</a> plugin, which does the <a href="http://github.com/binarylogic/searchlogic/blob/master/lib/searchlogic/named_scopes/conditions.rb#L89" rel="nofollow noreferrer">LIKE/ILIKE</a> switch for you.

blocks|key|1501727|text|如果想要匹配块中的子字符串，也可以在postgres中使用~*。~匹配区分大小写的子串，~*不区分大小写的子串。这是一个缓慢的操作，但我可能会发现它对搜索有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1501728|Select+*+from+table+where+column+~*+'UnEvEn+TeXt';
Select+*+from+table+where+column+~+'Uneven+text';|code-block|syntax|javascript|1501729|两者都会遇到“这里的一些不均匀的文本”，只有前者会遇到“这里的一些不均匀的文本”。|1501730|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>Select * from table where column ~* 'UnEvEn TeXt';
Select * from table where column ~ 'Uneven text';
</code></pre>

Both would hit on "Some Uneven text here"
Only the former would hit on "Some UNEVEN TEXT here"

blocks|key|1716532|text|转换为upper是最好的，因为它涵盖了3个最常用的Rails数据库后端的兼容语法。PostgreSQL、MySQL和SQLite都支持此语法。它有一个(小)缺点，那就是你必须在你的应用程序或你的条件字符串中大写你的搜索字符串，让它看起来有点丑陋，但我认为你获得的兼容性使它变得值得。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1716533|MySQL和SQLite3都有一个不区分大小写的LIKE操作符。只有PostgreSQL有一个区分大小写的LIKE操作符和一个PostgreSQL特定的(根据手册)+ILIKE操作符，用于不区分大小写的搜索。您可以在Rails应用程序的条件中指定ILIKE+insead+of+LIKE，但请注意，该应用程序将在MySQL或SQLite下停止工作。|1716534|第三种选择可能是检查您正在使用的数据库引擎，并相应地修改搜索字符串。这可能更好，方法是侵入/+monkeypatching+ActiveRecord的连接适配器，让PostgreSQL适配器在查询执行之前修改查询字符串，用"LIKE“代替"ILIKE”。然而，这个解决方案是最复杂的，并且考虑到更容易的方法，比如大写这两个术语，我认为这不值得努力(尽管这样做你会得到很多布朗尼分数)。|1716535|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

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.

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).

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.

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?

How do you write a case insensitive query for both MySQL and Postgres?

我在本地运行MySQL数据库进行开发，但部署到使用Postgres的Heroku。Heroku处理几乎所有的事情，但是我的不区分大小写的Like语句变得区分大小写。我可以使用iLike语句，但是我的本地MySQL数据库无法处理这一点。编写与MySQL和Postgres兼容的不区分大小写的查询的最佳方法是什么？或者，我是否需要根据我的应用程序与之对话的数据库来编写单独的Like和iLike语句？

问如何为MySQL和Postgres编写不区分大小写的查询？
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何为MySQL和Postgres编写不区分大小写的查询？EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何为MySQL和Postgres编写不区分大小写的查询？
EN