spring - JPA findBy...IgnoreCase 和 PostgreSQL 索引

Question

我有以下关于 findByUsernameIgnoreCase 查找和索引的问题。例如，我在 users 表中有数据库列 username(唯一索引)，我想要不区分大小写的查找。目前所有的记录都是大写的。

问题是 findByUsernameIgnoreCase(...) 是否会使用索引？

在规范中，IgnoreCase 的结果是 where UPPER(username)=UPPER(?1) 这让我在索引顺序上感到困惑。

Answer 1

对于方法 findBy...IgnoreCase("someName") Spring Data JPA 生成一个像这样的 SQL:

select ... from users u where upper(u.username) = upper('someName')

因此，如果将它与索引一起使用，您应该创建一个“upper(username)”索引，如下所示:

create index index_users_username_upper on users (upper(username));

查询计划示例:

Bitmap Heap Scan on parents p  (cost=83.17..8791.50 rows=5000 width=346) (actual time=0.024..0.024 rows=0 loops=1)  Filter: (upper(username) ~~ 'SOMENAME'::text)  ->  Bitmap Index Scan on index_users_username_upper  (cost=0.00..81.92 rows=5000 width=0) (actual time=0.022..0.022 rows=0 loops=1)        Index Cond: (upper(name) = 'SOMENAME'::text)Planning time: 0.207 msExecution time: 0.051 ms

I think that storing username in uppercase is not necessarily in that case...

---

You can always create your own query method that will match your conditions, for example:

@Query("select u from User u where upper(u.username) = ?1")
List<User> findByUsernameIgnoreCase(string name);

在这种情况下，您可以使用 username 的简单索引。