PostgreSQL：如何进行“不区分大小写”的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7005302/
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
PostgreSQL: How to make "case-insensitive" query
提问by Jame
Is there any way to write case-insensitive queries in PostgreSQL, E.g. I want that following 3 queries return same result.
有什么方法可以在 PostgreSQL 中编写不区分大小写的查询，例如我希望以下 3 个查询返回相同的结果。
SELECT id FROM groups where name='administrator'
SELECT id FROM groups where name='ADMINISTRATOR'
SELECT id FROM groups where name='Administrator'
回答by Chandu
回答by Mohammad Reza Norouzi
using ILIKEinstead of LIKE
使用ILIKE代替LIKE
SELECT id FROM groups WHERE name ILIKE 'Administrator'
回答by Mike Sherrill 'Cat Recall'
The most common approach is to either lowercase or uppercase the search string and the data. But there are two problems with that.
最常见的方法是小写或大写搜索字符串和数据。但这有两个问题。
- It works in English, but not in all languages. (Maybe not even in most languages.) Not every lowercase letter has a corresponding uppercase letter; not every uppercase letter has a corresponding lowercase letter.
- Using functions like lower() and upper() will give you a sequential scan. It can't use indexes. On my test system, using lower() takes about 2000 times longer than a query that can use an index. (Test data has a little over 100k rows.)
- 它适用于英语，但并非适用于所有语言。（甚至可能在大多数语言中都没有。）并不是每个小写字母都有一个对应的大写字母；并非每个大写字母都有相应的小写字母。
- 使用诸如lower() 和upper() 之类的函数将为您提供顺序扫描。它不能使用索引。在我的测试系统上，使用lower() 比使用索引的查询耗时大约2000 倍。（测试数据有 10 万多行。）
There are at least three less frequently used solutions that might be more effective.
至少有三种不太常用的解决方案可能更有效。
- Use the citext module, which mostly mimics the behavior of a case-insensitive data type. Having loaded that module, you can create a case-insensitive index by
CREATE INDEX ON groups (name::citext);. (But see below.) - Use a case-insensitive collation. This is set when you initialize a database. Using a case-insensitive collation means you can accept just about any format from client code, and you'll still return useful results. (It also means you can't do case-sensitive queries. Duh.)
- Create a functional index. Create a lowercase index by using
CREATE INDEX ON groups (LOWER(name));. Having done that, you can take advantage of the index with queries likeSELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR');, orSELECT id FROM groups WHERE LOWER(name) = 'administrator';You have to rememberto use LOWER(), though.
- 使用citext 模块，它主要模仿不区分大小写的数据类型的行为。加载该模块后，您可以创建一个不区分大小写的索引
CREATE INDEX ON groups (name::citext);。（但见下文。） - 使用不区分大小写的排序规则。这是在初始化数据库时设置的。使用不区分大小写的排序规则意味着您可以接受来自客户端代码的几乎任何格式，并且您仍然会返回有用的结果。（这也意味着你不能做区分大小写的查询。呃。）
- 创建功能索引。使用
CREATE INDEX ON groups (LOWER(name));.创建小写索引。完成后，您可以通过查询来利用索引SELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR');，或者SELECT id FROM groups WHERE LOWER(name) = 'administrator';您必须记住使用 LOWER()，不过。
The citext module doesn't provide a true case-insensitive data type. Instead, it behaves as if each string were lowercased. That is, it behaves as if you had called lower()on each string, as in number 3 above. The advantage is that programmers don't have to remember to lowercase strings. But you need to read the sections "String Comparison Behavior" and "Limitations" in the docs before you decide to use citext.
citext 模块不提供真正的不区分大小写的数据类型。相反，它的行为就像每个字符串都是小写的。也就是说，它的行为就像您调用lower()了每个字符串一样，如上面的数字 3。优点是程序员不必记住小写字符串。但是在决定使用 citext 之前，您需要阅读文档中的“字符串比较行为”和“限制”部分。
回答by ADJ
You can use ILIKE. i.e.
您可以使用ILIKE. IE
SELECT id FROM groups where name ILIKE 'administrator'
回答by Priidu Neemre
You can also read up on the ILIKEkeyword. It can be quite useful at times, albeit it does not conform to the SQL standard. See here for more information: http://www.postgresql.org/docs/9.2/static/functions-matching.html
您还可以阅读ILIKE关键字。它有时非常有用，尽管它不符合 SQL 标准。有关更多信息，请参见此处：http: //www.postgresql.org/docs/9.2/static/functions-matching.html
回答by James Brown
You could also use POSIX regular expressions, like
您还可以使用 POSIX 正则表达式，例如
SELECT id FROM groups where name ~* 'administrator'
SELECT 'asd' ~* 'AsD'returns t
SELECT 'asd' ~* 'AsD'返回 t
回答by Robin Goh
Using ~*can improve greatly on performance, with functionality of INSTR.
使用~*INSTR 的功能可以大大提高性能。
SELECT id FROM groups WHERE name ~* 'adm'
return rows with name that contains OR equals to 'adm'.
返回名称包含 OR 等于 'adm' 的行。
