第2 章 SQL 问题

2.1 本章目的
在本章中就在 HSQLDB 主页论坛或邮件列表中多次提出的问题进行解答，如果你打算在应
用程序中使用HSQLDB 的话，你应该阅读一下本章。
2.2 对SQL 标准的支持
1.8.0 版本的HSQLDB 支持SQL92、99 和2003 标准规定的SQL 方言。这意味着HSQLDB
中支持的标准特性（例如左外连接）的语法是由标准文本规定的。许多SQL92、99 甚至更
高级的特征在HSQLDB 中得到了支持，并且对SQL2003 标准的大多数以及一些可选的特性
进行支持。然而，对于某些标准的特性没有支持，所以HSQLDB 就没有做出支持各个级别
所有的标准特性的声明。
“SQL 语法”一章列出了HSQLDB 所支持的所有的关键字和语法。当书写有关HSQLDB 或者
转换现有的有关HSQLDB 的SQL DDL（数据定义语言）和DML（数据操作语言）语句的
时候，你应该查阅一下HSQLDB 所支持的语法，并对SQL 语句作出相应的修改。
SQL 标准中保留的关键字是不能作为表明或字段名使用的。例如，“POSITION”被作为与Java
中的String.indexOf()作用类似的函数加以保留。HSQLDB 目前并不限制使用它不支持其用
法的关键字或用户能够区分清楚的关键字。例如，“BEGIN”是HSQLDB 目前没有支持的关
键字，所以你也可使用它作为表或者列的名称。不过你应该避免使用这些保留字，因为在
HSQLDB 以后的版本中有可能支持这些保留字，否则将拒绝含有这些保留字表定义或查询
语句。全部SQL 保留字列表请参看org.hsqldb.Token 类。
HSQLDB 也支持一些SQL 标准之外的关键字和表达式作为性能的增强。像SELECT TOP 5
FROM .., SELECT LIMIT 0 10 FROM ... 或者 DROP TABLE mytable IF EXISTS 这样的表达
式都是HSQLDB 增强性能所支持。
所有被双引号标注的关键字可以被用做数据库对象。
2.3 约束和索引
2.3.1 主键约束
在 1.7.0 版本之前，一个CONSTRAINT <name> PRIMARY KEY（名为name 的主键约束）
被在内部翻译成一个唯一的索引，另外，一个隐藏列被添加到具有额外唯一索引的表上。从
1.7.0 开始，单一列主键和多列主键(single-column and multi-column PRIMARY KEY)约束都得

到支持。它们由主键列指定的唯一索引支持，而没有额外的隐藏列来维护它们的索引。
2.3.2 唯一性约束
根据 SQL 标准，一个单一列上的唯一性约束表示不允许存在两个相同的值（空值出外），也
就是说这样的列中可以一个或更多为空值（NULL）的行而不违反唯一性约束。
多个列(c1, c2, c3, ..)的唯一性约束表示这些列中的任何两个值的集合都不相等（除非至少其
中有一个为空）。每一个单一列内部可以有重复的值。下面这里满足两列上的唯一性约束。
例 2.1. 满足2 列唯一性约束的列值
1, 2
2, 1
2, 2
NULL, 1
NULL, 1
1, NULL
NULL, NULL
NULL, NULL
自从 1.7.2 版本以来，对于空值的唯一性约束和索引的处理已经向遵循SQL 标准改变。任何
唯一性约束列的值全为空的行，通常都可以被添加到表中，所以对于唯一性约束的列来说，
如果其中的一个行的值为空，那么多个行就可以具有相同的值。
2.3.3 唯一性索引
在 1.8.0 中，用户定义的唯一性索引仍然可以进行声明，但是它已经不被赞成使用了，你应
该使用一个唯一性约束来替代唯一性索引。
像在早期版本的HSQLDB中一样，名为<name>的唯一性约束通常在内部创建列的唯一索引，
所以它实际上和已经废除的唯一性索引声明具有相同的效果。
2.3.4 外键
HSQLDB 从1.7.0 版开始具有单一列外键和多列外键的特性。一个外键也可以被指定引用一
个没有命名的目标列的目标表。在这种情况下，目标表的主键列被用作引用列。任何外键中
的一对引用和被引用的列应该具有相同的数据库类型。当声明了一个外键时，主键表被引用
的列必须具有一个唯一性约束（或主键），而在引用列里会自动创建一个非唯一性索引。例
如：

CREATE TABLE child(c1 INTEGER, c2 VARCHAR, FOREIGN KEY (c1, c2) REFERENCES
parent(p1, p2));
parent 表中的 (p1,p2) 列一定存在一个唯一性约束。child 表中的(c1,c2)列会自动生成一个非
唯一性索引。p1 和c1 列一定具有相同的类型（INTEGER），p2 和c2 列一定具有相同的类
型（VARCHAR）。
2.3.5 索引和查询速度
HSQLDB 没有使用索引来改善查询结果的分类，但是索引在提高查询速度上起着至关重要
的作用。如果一个表没有使用索引，进行类似于DELETE 这样的查询操作的时候，表中所
有的行记录都要被检查一遍。WHERE 从句中的列中，如果有一个列建立索引的话，那么查
询操作就可能直接从第一个候选行记录开始，从而减少了要检查的行记录的数量。
索引在多个表之间进行连接操作的时候显得更重要。在 SELECT ... FROM t1 JOIN t2 ON
t1.c1 = t2.c2 执行的时候，对t1 中的行一个接一个的进行操作，来查找t2 中没有与之匹配的
行。如果没有t2.c2 没有任何索引的话，那么，对于t1 的每一行来说，要必须检查t2 的所有
行。然而有一个索引的话，在很短的时间内就能找到匹配的行。如果，查询(query)在t1 上
还有一个条件（例如，SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 WHERE t1.c3 = 4），那
么在t1.c3 上创建一个索引的话，将不需要一个接一个的检查所有的行，并且将每返回一行
的查询时间降低到1 个毫秒以下。所以，如果t1 和t2 各有10000 行记录，那么没有索引的
查询将会进行100,000,000 次行检查。如果在t2.c2 上创建一个索引的话，查询次数将会降低
到10000 次行检查和索引的查找。在t2.c2 上有另外一个索引的话，只需要检查4 行就可以
得到第一个符合条件的结果行。
对于主键和唯一列，将对自动生成索引，否则，你要使用CREATE INDEX 命令来定义索引。
注意：在 HSQLDB 中，多列（multiple columns）上的一个唯一性索引可以在内部被用作列
表第一列上的非唯一性缩影。例如，CONSTRAINT name1 UNIQUE (c1, c2, c3);表示这是
CREATE INDEX name2 ON atable(c1)等价的表示方式，所以你只要列表第一列中一个的话，
就不需要指定一个额外的索引。
在 1.8.0 版本中，一个多列的索引将会加速包含所有列的连接和值的查询。你不需要在这些
列上声明任何附加的单独的索引，除非你仅对这些列的子集进行查询。例如，在三个列上拥
有主键或唯一性约束或只是一个普通的索引的表的行记录，在三个列的值都在WHERE 从
句中指定了时候，查询就会比较高效。例如，SELECT ... FROM t1 WHERE t1.c1 = 4 AND t1.c2
= 6 AND t1.c3 = 8 将会使用t1(c1,c2,c3)上的索引（如果存在的话）。
作为多键索引改进的结果，和以前相比，声明索引或约束的列的顺序带给查询速度的影响大
大减少。如果包含多较多不同值的列首先出现的话，查询速度将会有一点提高。
一个多列索引不会加快仅对第二列或第三列查询的速度。第一列必须在JOIN .. ON 或者
WHERE 条件中指出。

查询速度很大程序上取决于 JOIN .. ON 或者 FROM 从句中表的顺序。例如，下面的第二
个查询在大的表中会更快一些（假如TB.COL3 上有索引的话）。因为，如果下面的查询被用
到第一表中（以及TB.COL3 上有索引）的话，TB.COL3 可以被很快的查出来：
（TB 是一个很大的表，但符合TB.COL3=4 条件的仅仅只有几行记录）
SELECT * FROM TA JOIN TB ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
SELECT * FROM TB JOIN TA ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
基本规则是将这样表放在首位：其中的一个列上具有一个缩小的条件(narrowing condition)。
1.7.3 版本的HSQLDB 具有为视图或用在查询中的子选择(subselect)创建自动的，快速的
(on-the-fly)索引的特征。当一个索引被连接到一个表或一个视图的时候，会被添加到一个不
同于所连接的视图里。
2.3.6 Where 条件或者连接
使用 WHERE 条件连接表可能会降低执行速度。例如下面的查询通常会比较慢，甚至有索
引的时候：
SELECT ... FROM TA, TB, TC WHERE TC.COL3 = TA.COL1 AND TC.COL3=TB.COL2 AND
TC.COL4
上面的查询表示 TA.COL1 = TB.COL2 但是却没有显式地设置条件。如果TA 和TB 每个都
含有100 行的记录，那么即使在被连接的列上建有索引时，也有10000 个组合combinations）
被连接到TC 来满足这些列的条件。使用JOIN 关键词，条件TA.COL1 = TB.COL2 将必须被
显示声明，它将会使TA 和TB 行在被TC 连接前降低它们的结合（combinations）次数，这
样将会使对大表查询的执行大大加快：
SELECT ... FROM TA JOIN TB ON TA.COL1 = TB.COL2 JOIN TC ON TB.COL2 = TC.COL3
WHERE TC.COL4 = 1
如果连接查询中表的顺序改变了的话，那么下面查询的执行速度会被大大加快，所以使用了
TC.COL1 = 1 以及将列的行的较小集合连接在一起：
SELECT ... FROM TC JOIN TB ON TC.COL3 = TB.COL2 JOIN TA ON TC.COL3 =
TA.COL1 WHERE TC.COL4 = 1
在上述的例子中，数据库引擎将TC.COL4 = 1 自动应用到TC，仅仅连接了行的集合来使这
个条件也满足其他的表。TC.COL4, TB.COL2 和TA.COL1 上的索引如果存在的话将会被使
用，进而加速查询。

较
SELECT ... FROM TA WHERE TA.COL1 = (SELECT MAX(TB.COL2) FROM TB
WHERE TB.COL3 = 4)
SELECT ... FROM (SELECT MAX(TB.COL2) C1 FROM TB WHERE TB.COL3 = 4) T2
JOIN TA ON TA.COL1 = T2.C1
第二个查询将 MAX(TB.COL2)转变成一个单行的表，但后将它连接到TA。因为TA.COL1
上存在索引，所以这个查询的速度会非常快。第一个查询将会测试TA 中的每一行记录，重
复的对MAX(TB.COL2)进行估算。
2.4 类型和算术操作
HSQLDB 支持的所有类型的数据库表都可以被索引，也可以进行比较。所有的类型都可以
用CONVERT()库函数进行显式转换，但是在大多数情况下它们可以被自动的转换。不推荐
在LONGVARBINARY, LONGVARCHAR 和 OTHER 列使用索引,因为这些索引可能在将
来的版本中是不允许的。
早期版本的 HSQLDB 在算术操作的处理上有些不足。例如，不可能把10/2.5 插入到任何
DOUBLE 或者DECIMAL 列。在1.7.0 版本中，遵循下列规则的全操作(full operations)是可
能的：
TINYINT, SMALLINT, INTEGER, BIGINT, NUMBER 和DECIMAL（没有小数点）是
HSQLDB 支持的整值类型，它们在Java 中被映射成byte, short, int, long 和 BigDecimal。SQL
类型规定了每个类型的最大值和最小值。例如，TINYINT 的值范围是从-128 到 +127，虽
然实际用于处理TINYINT 类型的Java 类型是java.lang.Integer。
REAL, FLOAT, DOUBLE 在Java 中都被映射成double。
DECIMAL 和NUMERIC 被映射成java.math.BigDecimal，它们可以有很多的位数。
2.4.1 整型类型
TINYINT, SMALLINT, INTEGER, BIGINT, NUMBER 和 DECIMAL（不带小数点)在内部都
是完全可以互换的，不存在数据窄化(narrowing)的情况。返回的操作结果依赖于不同的操作
数类型，它可以是存在于JDBC 结果集里面的任何相关的Java 类型(Integer, Long or

BigDecimal)。只要返回值可以被结果类型所表示，ResultSet.getXXXX()方法就能够用来获取
数据。这个类型是基于查询的，而不是实际返回的行。当向数据库的表中添加了更多数据之
后返回多行记录的时候，返回一条行记录的相同查询的类型不发生变化。
如果 SELECT 语句涉及到一个简单的列或函数的话，那么返回类型就是所对应的列的类型
或者函数的返回值类型。例如：
CREATE TABLE t(a INTEGER, b BIGINT); SELECT MAX(a), MAX(b) FROM t;
上面的依据将会返回一个结果集，其第一列的类型是java.lang.Integer，第二列的类型是
java.lang.Long。然而，
SELECT MAX(a) + 0, MAX(b) + 0 FROM t;
将返回 java.lang.Long 和 BigDecimal 的值, 它是作为所有返回值的统一类型的提升生成
的。
在表达式中的中间整形数值的大小上，没有内建的限制。因此，你应该检查结果集列的类型，
选择一个合适的getXXXX()方法来获取该值。另外你也可以使用getObject()方法，然后，将
结果转换成java.lang.Number，在结果上使用intValue() 或 longValue()方法。
当表达式的结果存在数据库表的一个列中，它必须适合目标列，否则，会返回一个错误。例
如，当计算1234567890123456789012 / 12345687901234567890 时，结果可以被存储在任何
类型的列中，甚至是个TINY 列，因为它是一个很小的值。
2.4.2 其他数字类型
在 SQL 语句中，如果不是指数形式，带有小数点的数字都被当作DECIMAL 处理。因此0.2
被认为是DECIMAL 类型的值，而0.2E0 则被看作DOUBLE 类型。
当对某个值使用PreparedStatement.setDouble()或setFloat()方法时，该值就被自动默认为
DOUBLE 类型处理。
当表达式的一部分是 REAL、FLOAT 或者DOUBLE（所有同义的类型）类型时，那么结果
类型则是DOUBLE。
此外，当DOUBLE 类型不存在时，如果表达式的一部分是DECIMAL 或NUMERIC 类型时，
结果类型就是DECIMAL，只要结果可以用这种数字类型表示，那么它可以从ResultSet 中
按照任何所需的数字类型来获取。这就意味着如果一个DECIMAL 值的范围在
Double.MIN_VALUE- Double_MAX_VALUE 之间，那么它就可以被转换成DOUBLE 类型。
和整形数值相似，当表达式结果存入表的列中时，它的类型必须符合该列的类型，否则将有
错误出现。

进行除法运算时，DOUBLE 和DECIMAL 类型间的区别就显得很重要的。当操作数类型是
DECIMAL，结果的取值范围（小数点右边的位数）取的是两个操作数中较大的范围。如果
是DOUBLE 类型，那么范围则是运算的精确结果。例如，10.0/8.0（DECIMAL）等于1.2，
但是10.0E0/8.0E0（DOUBLE）等于1.25。如果不是除法运算，DECIMAL 类型能精确的描
述算法；如果两个相乘，那么范围就是两个数范围之和。
REAL、FLOAT 和DOUBLE 类型数值都被当作java.long.Double 对象存入数据库中。同时也
可以存储和支持一些特殊值如NaN 和正负无穷大。这些值可以通过JDBC PreparedStatement
方法提交给数据库，并且可以返回结果集对象。
2.4.3 二进制和Boolean 类型
从 1.7.2 开始，BIT 通常也可以看作BOOLEAN 的别名。BOOLEAN 列的主要描述是’true’
或’false’，通过JDBC 使用时，可以被看作boolean 或者String 类型。BOOLEAN 列的类型
还可以使用任何数字类型的值进行初始化。这样的情况下，0 将被转化成false，其他非0 值
将被转换成true。
从 1.7.3 开始，BOOLEAN 类型遵循SQL 标准，除了支持TRUE 和FALSE 类型以外，也提
供了对UNDEFINED 状态的支持。NULL 值被当作未定义类型处理。这一改进影响了那些
包含NOT IN 的查询。这样的查询语句，请参看测试文本文件TestSelfNot.txt。
2.4.4 Java 对象的存储和操作
从 1.7.2 开始，对Java 对象的存储和操作的支持有了很大的提高，通过使用各种变量的
PreparedStatement.setObject()方法，任一个序列化的JAVA 对象都可以被直接的插入到
OTHER 类型的列中。
为了比较，在索引里边，任何两个Java 对象都被看作是相等，除非他们中的一个是NULL。
在OTHER 类型的列中，你不能搜索一个指定的对象或者进行连接操作。
请注意，HSQLDB 不是一个对象－关系数据库。Java 对象只是简单地存储在内部，除了将
OTHER 类型的列值赋为NULL 或测试该列值是否为NULL 之外，不应该对它们进行其他的
操作。测试例如WHERE object1=object2，或者WHERE ojbect1=?并不能得到你预期的结果。
任何非空的对象都能满足这样的测试。但是WHERE object1 IS NOT NULL 就能很好的执行。
普通列的数据被分配给 Java 对象列（例如，在类似UPDATE mytable SET objectcol = intcol
WHERE…的SQL 语句中，向列中填充一个整型或字符串）时，数据库并不会返回错误，但
在将来，这样的做法时很不恰当的。因此请使用OTHER 类型列来存贮对象而不是其他类型
的数据。

2.4.5 类型的大小、精度和范围
在 1.7.2 版本以前，所有带有大小、精度或范围的限制的数据库表的列类型定义可有可无。
在 1.8.0 版本中，这样的限定必须和SQL 标准一致。例如INTEGER（8）这样的形式将不能
被接受。除非设置了数据库的属性， 要你这个限定仍然可以被忽略。SET
PROPERTY ”sql.enforce_strict_size” TRUE语句将为CHARACTER或VARCHAR列强制设置
大小，并且当插入或更新一个CHARACTER 列时填充字符串。精度和范围限制也被转化为
DECIMAL 和NUMERIC 类型。TIMESTAMP 只能使用0 或6 这样的精度。
如你所料，将一个值转换成一个CHARACTER 的限定类型将导致切割或填充。因此象
CAST(mycol AS VARCHAR(2))=’xy’这样的测试语句结果得到以xy 开头的值。这和
SUBSTRING(mycol FROM 1 FOR 2)=’xy’是相同的。
2.5 序列和标识
SEQUENCE关键字作为SQL200n 标准语法的子集被引入到1.7.2 版本中。相应的，IDENTITY
列的SQL200n 语法也被引进。
2.5.1 标识自动增长列
每个表都可以有一个自动增长列，众所周知的就是 IDENTITY 列。一个IDENTITY 列总是
被当作表的主键处理（因此，多列主键不可能有一个IDENTITY 列）。作为一个捷径，已经
为CREATE TABLE <tablename>(<colname>IDENTITY,…)提供了支持。
从1.7.2版本开始，SQL标准语法默认的支持指定初始值。支持的形式是： (<colname>
INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH n, [INCREMENT BY
m])PRIMARY KEY, ...).。同时也对BIGINT特性列提供了支持。因此，一个IDENTITY是一
个由Sequence生成器生成默认值的INTEGER或者BIGINT列。
当使用INSERT INTO <tablename>…语句向表中添加一个新列时；你可以在IDENTITY列中
使用NULL值，这样该列的值将会自动生成。IDENTITY（）函数返回通过连接插入到
IDENTITY列中的最后一个值。使用CALL IDENTITY（）这个SQL语句取得这个值。如果
想在子表中使用这个值，你可以使用语句INSERT INTO <childtable> VALUES(...,
IDENTITY(),...);。在任何附加的更新或插入语句在数据库中执行前，两种类型的IDENTITY
（）函数必须被调用。
接下来要使用的的IDENTITY值可以通过语句ALTER TABLE ALTER COLUMN
<columnname> RESTART WITH <new value>;来设置。

2.5.2 序列
SQL200n语法和用法和现有的大多数据库引擎所支持的是有区别的。可以通过命令CREATE
SEQUENCE创建Sequences，并且可以通过命令ALTER SEQUENCE在任何时候进行修改它
们的当前值。一个sequence的下一个值是通过NEXT VALUE FOR <name>表达式取得。这个
表达式可以用来插入或者更新表的行，你也可以在select语句中使用它。例如，如果想计算
在一个Sequence序列中SELECT返回的行，可以使用：
例2.3 计算选择操作以后返回的行
SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...
请注意，顺序的语义并不完全和SQL200n定义的相同。例如，如果你在相同的列插入操作中
两次使用相同的序列，你将会得到两个不同的值，而不是标准所说的同一个值。
你可以通过查询SYSTEM SEQUENCES表得到下一个值，这个值将从任何一个已定义的序列
中返回。SEQUENCE_NAME列保存的是名称，NEXT_VALUE列保存的是下一个被返回的
值。
2.5.3 事务的问题
HSQLDB在READ_UNCOMMITTED级别上支持事务，就象0级别上的事务隔离。这就意味
着在一个事务的生命周期内，其他数据库的连接可以看到这个事务对数据的改变。总的来说，
对事务的支持还是不错的。象数据库突然关闭而事务被提交这样的BUG已经被修复了。然
而，下面的问题将会在多个数据库连接使用事务时出现问题:
如果两个事务修改同一行，而两个事务提交时没有异常出现。这样的情况可以通过这种方式
的设计来避免：应用程序数据的一致性不依赖一个事务对数据的独占性修改。你可以通过设
置一个数据库属性，在这样的情况发生时会抛出一个异常：SET PROPERTY
"sql.tx_no_multi_rewrite" TRUE
当一个ALTER TABLE…INSERT COLUMN或者DROP COLUMN命令导致数表的结构变化
时，当前的会话被提交。如果由另一个连接启动的未提交的事务此时应在受影响的表中修改
了数据，那么这个事务在ALTER TABLE命令后不可能被回滚。这一点同样也适用于ADD
INDEX 或ADD CONSTRAINT命令。只有在其他连接没有使用事务时，才推荐使用ALTER
等这样的命令。
在CHECKPOINT命令执行以后，未授权事务才能继续运行、提交或回滚。然而，如果数据
库随后并没有使用SHUTDOWN命令正常的关闭的话，在数据库关闭时仍然是未提交状态的
任何这样事务，将被在下次启动数据库时被部分提交（CHECKPOINT的状态）。不管程序
中没有未授权的事务的情况，还是因为想这样的事务不可能持续过长时间，以至于非正常关
闭可能影响数据的情况，都推荐使用CHECKPOINT。

2.5.4 新特性和变化
在最新的1.8.0版本中增加了许多更好的对SQL的支持。这些都在SQL语法这一章和文件
在../changelog_1_8_0.txt，../changelog_1_7_2.txt中列出了。象POSITION(),SUBSTRING(),
NULLIF(), COALESCE(), CASE ... WHEN .. ELSE, ANY, ALL这样的表达式和函数也在其
中。另外一些改进虽然在文档中不是很明显，但能使数据库性能比以前版本有很大的改进。
这其中最重要的就是能在连接（非空列将不再被连接）和外连接（现在的结果是正确的）中
处理NULL值。你应该对使用新版本数据库引擎的应用程序进行测试，确定这些程序不再使
用旧版本的那些错误的特性。在将来的版本中，数据库引擎仍将朝着完全支持SQL标准的方
向改进，因此最好不要依赖当前版本中任何的非标准特性。