有什么方法可以在 PostgreSQL 中编写不区分大小写的查询,例如我希望以下 3 个查询返回相同的结果。
SELECT id FROM groups where name='administrator'
SELECT id FROM groups where name='ADMINISTRATOR'
SELECT id FROM groups where name='Administrator'
使用 ILIKE
而不是 LIKE
SELECT id FROM groups WHERE name ILIKE 'Administrator'
ILIKE
。
org.hibernate.dialect.PostgreSQL94Dialect
和 Spring Boot 2.0.6.RELEASE。但是 IntelliJ 抱怨它。
最常见的方法是将搜索字符串和数据小写或大写。但这样做有两个问题。
它适用于英语,但并非适用于所有语言。 (可能甚至在大多数语言中都没有。)不是每个小写字母都有对应的大写字母。不是每个大写字母都有对应的小写字母。使用像 lower() 和 upper() 这样的函数会给你一个顺序扫描。它不能使用索引。在我的测试系统上,使用 lower() 比使用索引的查询要长约 2000 倍。 (测试数据有超过 100k 行。)
至少有三种不太常用的解决方案可能更有效。
使用 citext 模块,它主要模仿不区分大小写的数据类型的行为。加载该模块后,您可以通过 CREATE INDEX ON groups (name::citext); 创建一个不区分大小写的索引。 (但见下文。)使用不区分大小写的排序规则。这是在初始化数据库时设置的。使用不区分大小写的排序规则意味着您可以接受来自客户端代码的几乎任何格式,并且您仍然会返回有用的结果。 (这也意味着您不能进行区分大小写的查询。Duh。)创建一个功能索引。使用 CREATE INDEX ON groups (LOWER(name)); 创建小写索引。完成此操作后,您可以通过 SELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR'); 或 SELECT id FROM groups WHERE LOWER(name) = 'administrator'; 之类的查询来利用索引。不过,您必须记住使用 LOWER()。
citext 模块不提供真正的不区分大小写的数据类型。相反,它的行为就像每个字符串都是小写的。也就是说,它的行为就像您在每个字符串上调用了 lower()
一样,如上面的数字 3 所示。优点是程序员不必记住小写字符串。但是在决定使用 citext 之前,您需要阅读文档中的“字符串比较行为”和“限制”部分。
col = 'a'
和 col = 'b'
)。关于#2:正如您所说,您可以在表达式上创建索引,所以这不是一个真正的问题。但我同意你的观点,改变排序规则很可能是最好的解决方案。
select * from pg_collation;
。
您可以使用 ILIKE
。 IE
SELECT id FROM groups where name ILIKE 'administrator'
lower
函数。查看更多details
您还可以阅读 ILIKE
关键字。它有时会非常有用,尽管它不符合 SQL 标准。有关详细信息,请参见此处:http://www.postgresql.org/docs/9.2/static/functions-matching.html
email ILIKE 'user-input-email-here'
的查询,请确保转义用户输入。否则,人们可以输入匹配任何内容的字符,例如 %。
ILIKE
和 prepared statements
这会保护我免受 sql injection
的伤害吗?
lower(column_name) like %expression%
慢。
您还可以使用 POSIX 正则表达式,例如
SELECT id FROM groups where name ~* 'administrator'
SELECT 'asd' ~* 'AsD'
返回 t
通过 INSTR 的功能,使用 ~*
可以大大提高性能。
SELECT id FROM groups WHERE name ~* 'adm'
返回名称包含 OR 等于 'adm' 的行。
~*
不是完全不区分大小写的匹配。它是正则表达式模式匹配。在您的示例中,如果 db 包含 ADM
,则 where name ~* 'Adm'
或 where name ~* 'Ad'
将产生结果。请改用 ILIKE
ILIKE 在这种情况下工作:
SELECT id
FROM groups
WHERE name ILIKE 'Administrator'
使用ILIKE
select id from groups where name ILIKE 'adminstration';
如果您来 expressjs 背景和名称是一个变量,请使用
select id from groups where name ILIKE $1;
对于不区分大小写的参数化查询,您可以使用以下语法:
"select * from article where upper(content) LIKE upper('%' || $1 || '%')"
-- Install 'Case Ignore Test Extension'
create extension citext;
-- Make a request
select 'Thomas'::citext in ('thomas', 'tiago');
select name from users where name::citext in ('thomas', 'tiago');
select id from groups where name in ('administrator', 'ADMINISTRATOR', 'Administrator')
LIKE 'xxx%'
查询(即CREATE INDEX ix_groups_name ON groups (lower(name) varchar_pattern_ops)
)一起使用,还要指定varchar_pattern_ops
。ILIKE
,它会起作用,but with slow response
。为了根据计算结果快速访问表,我建议任何只是检查这个的人都应该接受接受的答案。查看更多详细信息 here 和 here