提问人：Νίκος Ανδρεάδης 提问时间：10/30/2023 更新时间：10/31/2023 访问量：86
如何处理对 100+ 种组合的数据库的 SQL 查询？
How to handle SQL queries to a database with a propability of 100+ combinations?
问：
我的用户界面有 9 个字段，每个字段都代表我的数据库表中的一列。 所以问题是我需要涵盖 9 个字段的所有可能组合，并向数据库发送特定查询。 例： 如果用户输入 2 个字段，我会从数据库中返回与这 2 个项目的组合匹配的所有项目。 如果用户输入 3 个字段，我会从数据库中返回与这 3 个项目的组合匹配的所有项目。 很明显，如果你计算它们，这些组合是 100+。 使用带有JDBC驱动程序的JAVA和MySQL，提供了保护SQL注入的Prepared Statement。 但是这样一来，我应该制作 100+ 个函数来涵盖所有组合，并且代码将是重复的。
我是如何解决的： 我创建了一个查询生成器，它接受用户输入的输入，并将 SQL QUERY 创建为字符串。 然后我使用 Statement Class 将其传递给数据库。这样，我涵盖了所有可能的组合，但我必须自己从零开始制作SQL注入保护，通常它是我确信已经解决的问题的自定义解决方案。
如果有官方方法可以做到这一点，请告诉我！
答：
0赞
Akina
10/30/2023
#1
SELECT table.*
FROM table
JOIN ( SELECT @column1 AS column1
, @column2 AS column2
......
, @column9 AS column9
) AS criteria ON ( table.column1 = criteria.column1 OR criteria.column1 IS NULL)
AND ( table.column2 = criteria.column2 OR criteria.column2 IS NULL)
..........
AND ( table.column9 = criteria.column1 OR criteria.column9 IS NULL)
如果用户输入了某个列的值，则在查询中提供输入的条件，否则提供 NULL （）。command.Parameters.AddWithValue("@columnX", (object)value ?? DBNull.Value)
评论
0赞
Νίκος Ανδρεάδης
10/30/2023
谢谢，但我不需要 JOIN 表。我有一个表，只需要使搜索变得简单，而不是太复杂而无法编码。如果您有时间，请解释一下您提供的代码如何与JDBC一起使用吗？
0赞
Marmite Bomber
10/31/2023
#2
基本上有两种方法可以解决这个问题。
第一个使用一个带有 OR 条件的语句来处理缺失的条件。
第二种方法使用动态生成的语句，仅包含具有给定值的谓词。
第一种解决方案的缺点是你得到一个语句，即一个处理所有输入的执行计划。
第二种解决方案允许根据输入选择适当的执行计划。缺点是你使用不同数量的绑定变量登陆，这使得它在技术上难以处理。
这是汤姆·凯特（Tom Kyte）多年来推广的想法
您可以为每个选择生成谓词，但取决于是否给定该值：
column = :bind_variable -- if the value is passed
(1=1 or :bind_variable is NULL) – if no value is NOT passed
快捷方式逻辑消除了没有值的谓词，但您可以在预准备语句中获得绑定变量的完整数量。
三列全部传递的示例
where
col1 = :1 and
col2 = :2 and
col3 = :3
一个输入的示例
where
col1 = :1 and
(1=1 or :2 is NULL) and
(1=1 or :3 is NULL)
没有输入的示例
where
(1=1 or :1 is NULL) and
(1=1 or :2 is NULL) and
(1=1 or :3 is NULL)
这里是第二个示例的 Oracle 执行计划，显示只计算谓词，所有其他谓词都被丢弃。类似适用于PostgreSQL，我没有测试MySQL。col1
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 18 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | COL1_IDX | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=TO_NUMBER(:1))
对于第三个查询（无条件），以full table scan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 17M| 896 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1000K| 17M| 896 (1)| 00:00:01 |
--------------------------------------------------------------------------
因此，如果结果的基数高度依赖于选择，则此方法更适合于一刀切所有 OR 谓词解决方案。
评论
0赞
Νίκος Ανδρεάδης
11/1/2023
非常感谢您的回答！我实际上通过 Java 代码实现了第二个解决方案。例如，在数据库中执行解决方案是最佳选择，还是您可以选择在服务器中执行？
0赞
Marmite Bomber
11/2/2023
这两个选项（DB服务或客户端生成的SQL查询）都是可能的。什么是更好的（最佳的）只取决于开发人员的导向（以数据库为中心与以客户为中心;）@ΝίκοςΑνδρεάδης
1赞
Νίκος Ανδρεάδης
11/3/2023
我还没有名声来点赞:P
评论