Derby 对 NULL 值的处理 [英] Derby's handling of NULL values

问题描述

我是 Derby 的新手，我注意到就 null 值而言，我遇到了与使用 DB2 RDBMS 时类似的问题.Derby 文档指出，null 值必须具有与之关联的类型(DB2 最终在 9.7 版本中删除了该类型):

I am new to Derby and I noticed that I face similar problems as when using the DB2 RDBMS as far as null values are concerned. The Derby documentation states, that a null value must have a type associated with it (something that DB2 finally got rid of in version 9.7):

http://db.apache.org/derby/docs/10.7/ref/crefsqlj21305.html

现在，我正在尝试在这里找到解决此问题的通用解决方案，因为这将成为我的数据库抽象库的一部分 jOOQ.下面的例子只是记录了这个问题.想想任何其他(更复杂的)例子.以下不起作用:

Now, I am trying to find a general solution to this problem here as this will be a part of my database abstraction library jOOQ. The below example just documents the problem. Think of any other (more complex) example. The following doesn't work:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
select 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', null, null 
from SYSIBM.SYSDUMMY1

也不这样做(这实际上是 jOOQ 所做的):

Neither does this (which is what is actually done by jOOQ):

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
select ?, ?, ?, ?, ?, ? 
from SYSIBM.SYSDUMMY1

因为这两个 null 值没有与之关联的类型.解决方案是这样写:

Because the two null values have no type associated with it. The solution would be to write something like this:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
select 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', cast(null as int), cast(null as varchar(500)) 
from SYSIBM.SYSDUMMY1

或者像这样，分别

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
select 
  ?, ?, ?, ?, cast(? as int), cast(? as varchar(500)) 
from SYSIBM.SYSDUMMY1

但很多时候，在 Java 中，null 应该被强制转换为的类型是未知的:

But very often, in Java, the type that null should be cast to is unknown:

• 在此示例中，类型可以从插入子句派生，但对于更一般的用例而言，这可能被证明是复杂的或不可能的.
• 在其他示例中，我可以选择任何类型进行强制转换(例如，始终强制转换为 int)，但这在本示例中不起作用，因为您不能放置 强制转换(null as int) 值到 ADDRESS.
• 使用 HSQLDB(这个问题的另一个候选者)，我可以简单地编写 cast(null as object)，这在大多数情况下都可以工作.但是 Derby 没有 object 类型.

• In this example, the types could be derived from the insert clause, but that might prove to be complicated or impossible for more general use-cases.
• In other examples, I could just pick any type for the cast (e.g. always casting to int), but that wouldn't work in this example, as you cannot put a cast(null as int) value into ADDRESS.
• With HSQLDB (another candidate for this problem), I can simply write cast(null as object) which will work in most cases. But Derby does not have an object type.

这个问题之前一直困扰着我使用 DB2，我还没有找到解决方案.有谁知道针对这些 RDBMS 中的任何一个的稳定和通用解决方案?

This problem has been annoying me with DB2 before and I haven't found a solution yet. Does anyone know of a stable, and general solution to this problem for any of these RDBMS?

• 德比
• DB2

推荐答案

如果在 INSERT 上使用 VALUES 子句，则不必强制转换 NULL 值:

If you use the VALUES clause on your INSERT, you don't have to cast the NULL values:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
VALUES ( 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', null, null 
);

这将像您期望的那样工作(即数据库可以确定 NULL 对应于一个整数和 varchar(500).这在 DB2 和 Derby 中都有效(并且应该在几乎任何其他数据库引擎中也适用).

This will work like you expect (i.e. the database can determine that the NULLs correspond to an integer and varchar(500). This works in both DB2 and Derby (and should work in pretty much any other database engine, as well).

您也可以将 VALUES 与参数标记一起使用，而无需 CAST.

You can use VALUES with parameter markers as well, without having to CAST them.

在发出 insert into ... select from 语句时必须进行强制转换的原因是因为 SELECT 部分具有优先权—— select 语句返回某些数据类型，无论它们是否是与您尝试将它们插入的表兼容.如果它们不兼容，您将得到一个错误(对于像 DB2 <= 9.5 这样的强类型数据库引擎)，或者引擎将执行隐式类型转换(如果可能).

The reason that you have to cast when issuing an insert into ... select from statement is because the SELECT portion takes precedence -- the select statement returns certain data types, regardless of whether they are compatible with the table you're trying to insert them in to. If they aren't compatible, you will either get an error (with strongly typed database engines like DB2 <= 9.5) or the engine will do implicit type conversion (when possible).

这篇关于Derby 对 NULL 值的处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！