问题描述
我希望 JOOQ 生成 ... in (?) 并将列表绑定为数组参数 (Postgres).我的代码看起来像
I want JOOQ to generate ... in (?) and bind list as an array parameter (Postgres). My code looks like
.where(
Tables.TABLE.FIELD.in(idsList)
)
- 我该怎么做?
- 为什么默认情况下不这样做，因为它在 (?, ?, ?, ?, ...) 中生成(并由 PG 解析)字符串效率更高
推荐答案
您可以使用 DSL.any(T[]) 运算符，例如
You can use the DSL.any(T[]) operator, e.g.
TABLE.FIELD.eq(any(1, 2, 3))
这只会将一个数组变量绑定到 JDBC 语句
This will bind only one array variable to the JDBC statement
为什么默认情况下不这样做，因为它更有效地生成(并由 PG 解析)字符串
可能值得考虑 Settings 中的标志.我已经为此注册了一个功能请求:https://github.com/jOOQ/jOOQ/issues/6029
It might be worth thinking about a flag in Settings. I've registered a feature request for this: https://github.com/jOOQ/jOOQ/issues/6029
一般来说，jOOQ 允许用户准确地编写他们想要发送到数据库服务器的 SQL，因此 SQL 的自动优化"和重写可能对某些用户来说是出乎意料的.这只是 jOOQ 中的一般经验法则.始终值得考虑优化并通过 Settings 选择加入.
In general, jOOQ allows users to write exactly the SQL they want to send to the database server, so the automatic "optimisation" and rewriting of SQL might appear quite unexpected to some users. This is just a general rule of thumb in jOOQ. It's always worth thinking about optimisations and making them opt-in through Settings.
然而，实际衡量这些东西总是很重要的.虽然使用您建议的方法肯定会减少解析和 SQL 生成开销，但请注意，数组的基数可能比硬连线的 IN 列表更难正确估计.将数组用于小列表可能会对您的执行计划产生负面影响.因此，您在解析器端节省的几微秒将与执行端的几毫秒 (?) 相比！
However, it's always important to actually measure these things. While there is certainly a bit less of a parsing and SQL generation overhead with your suggested approach, beware that an array's cardinality may be much harder to estimate correctly than a hard-wired IN list. Using an array for small lists can have negative effects on your execution plan. So, the few microseconds you're saving on the parser side will weigh against the few milliseconds (?) at the execution side!
我在以下博客文章中对此进行了基准测试:https://blog.jooq.org/2017/03/30/sql-in-predicate-with-in-list-or-with-array-which-is-faster
I've benchmarked this in the following blog post:https://blog.jooq.org/2017/03/30/sql-in-predicate-with-in-list-or-with-array-which-is-faster
IN 列表似乎始终优于数组版本(在我的特定基准案例中)，直到长度约为 50
The IN list seems to consistently outperform the array version (in my specific benchmark case) until a length of around 50
这篇关于如何让 JOOQ 在 IN 子句中使用数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！