如何编写空安全比较“<=>” 在纯 SQL 中？

Question

在Mysql中有一个比较运算符是空安全的：<=>。 我在 Java 程序中创建如下准备语句时使用它：

String routerAddress = getSomeValue();
String sql = "SELECT * FROM ROUTERS WHERE ROUTER_ADDRESS <=> ? ";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, routerAddress);

现在我想切换到 H2 数据库。 如何写<=> 纯 SQL 中的运算符（例如使用 IS NULL 和 IS NOT NULL）？ 我只想使用 stmt.setString 操作一次。 列名多写几次就可以了。

相关问题是Get null == null in SQL。 但是这个答案需要将搜索值写入2次（即：我的PreparedStatement中的2个问号）！？

参考： http://dev.mysql.com/ doc/refman/5.0/en/comparison-operators.html#operator_equal-to

Answer 1

相关问题是 Get null == null in SQL。 但是这个答案需要将搜索值写入两次（即：我的PreparedStatement中的2个问号）！？

排名第二和后续的答案给出了一种方法来执行此操作，而无需两次绑定搜索值：

SELECT * FROM ROUTERS 
WHERE coalesce(ROUTER_ADDRESS, '') = coalesce( ?, '');

请注意，这需要一个永远不能是有效列值的虚拟值（即“带外”）； 我正在使用空字符串。 如果你没有任何这样的值，你将不得不忍受两次绑定该值：

SELECT * FROM ROUTERS 
WHERE ROUTER_ADDRESS = ? or (ROUTER_ADDRESS is null and ? is null);

Answer 2

SQL 中标准的 NULL 安全相等运算符是 IS DISTINCT FROM 和 IS NOT DISTINCT FROM。

Answer 3

在 SQL 中，NULL 不等于其自身。 因此，您可以：

1 - 将其替换为虚拟值并比较它们，如：

SELECT * FROM ROUTERS WHERE ISNULL(ROUTER_ADDRESS,'xxx') <=> ISNULL(?,'xxx')

或

2 - 将其替换为更复杂的逻辑测试，如：

SELECT *
FROM ROUTERS 
WHERE (
       (ROUTER_ADDRESS IS NULL AND ? IS NOT NULL)
       OR
       (ROUTER_ADDRESS IS NOT NULL AND ? IS NULL)
       OR
       (ROUTER_ADDRESS IS NOT NULL AND ? IS NOT NULL AND ROUTER_ADDRESS <> ?
      )

Answer 4

如果您想要 ROUTERS，其中 ROUTER_ADDRESS 何时为空？ 为 null 那么这可能有效：

SELECT * 
FROM ROUTERS 
WHERE ROUTER_ADDRESS = (case when ROUTER_ADDRESS is null and ? is null then null 
                             else ? end)

Answer 5

重写<=>的正确答案是 在纯 SQL 中（因为您没有使用 MySQL）是

“IS NOT DISTINCT FROM”，

假设您的数据库引擎支持它。 （MySQL 没有，但许多其他数据库系统有）

如果做不到这一点，许多其他数据库系统都有自己的专有技巧。

如果两者都没有，则需要合并到 where 子句内的无效数据。

COALESCE (a, "__NULL__") 

应该适用于字符或文本字段。 （我的意思是确切的字符串，它永远不应该显示在真实数据中）。 非文本字段比较棘手，因为 sql 喜欢进行隐式转换，这使得合并到带外数据变得非常困难。 如果字段仅包含过去的日期，您可以将空值合并到遥远的未来。 如果字段仅包含特定日期之后的日期，则可以合并到遥远的过去。 对于无符号数字，您可以将它们合并为 -1（即使该字段不能包含负 1，该比较仍然有效）。

如果这不是一个选择，你就必须进行非常愚蠢的长检查。

如果您实际上不需要将 null 与空不同地考虑，您可以合并到它，但这仅适用于文本/字符。