DB2 通用数据库中的生成列和标识列详解

太过爱你忘了你带给我的痛 2022-04-10 13:21 189阅读 0赞

## 前言 ##

生成列（包括标识列）是 DB2 的一个重要的特性，用来自动生成列值。一个生成列的值不是由 INSERT 或者 UPDATE 操作派生，而是根据预定义由 DB2 自动生成。在应用程序中，用户可以根据不同的需求选择不同的生成列从而简化应用程序的开发或提高。

## 生成列 ##

DB2 的生成列（GENERATED COLUMNS）由 CREATE TABLE 或 ALTER TABLE 语句中的 GENERATED ALWAYS AS ... 子句创建。

DB2 的生成列的值由用户定义的表达式产生，DB2 根据表达式来计算生成列的列值。在应用程序自定义生成列时，需要在 CREATE TABLE 或者 ALTER TABLE 语句中指定 GENERATED AS EXPRESSION 语句。

清单 1. DB2 应用程序自定义生成列

CREATE TABLE employees ( 
         empID INTEGER NOT NULL PRIMARY KEY, 
         name VARCHAR(20), 
         yr_onboard SMALLINT, 
         yr_leaving SMALLINT, 
         yr_in_IBM SMALLINT GENERATED ALWAYS AS (yr_leaving – yr_onboard))

如上的 DDL 包含生成列示例。通过使用下列表达式，yr\_in\_IBM 列由 DB2 自动计算产生：

yr_leaving – yr_onboard = yr_in_IBM

以 GENERATED ALWAYS 方式定义的生成列将不能接受由应用程序提供的值，而是必须由 DB2 根据您提供的表达式生成那些值。以上表 employees 为例，下列 INSERT 语句需用 DEFAULT 关键字将三个新的行添加到 employees 表中：

清单 2.将三个新的行添加到 employees 表

INSERT INTO employees (empID,name,yr_onboard,yr_leaving,yr_in_IBM) VALUES 
         (1, ’ Jacky Chen ’ ,2001,2008,DEFAULT), 
         (2, ’ Bruce Li ’ ,1997,2010,DEFAULT), 
         (3, ’ Roger Lin ’ ,1998,2005,DEFAULT)

DEFAULT 关键字用来说明 DB2 将为各行根据表达式生成列值。在运行了这个 INSERT 语句后，您可以看到由 DB2 为 Jacky Chen，Bruce Li 和 Roger Lin 生成的值。通过查询表 employees 的内容，您可以验证这一点：

清单 3.查询表 employees 的内容

EMPID NAME         YR_ONBOARD   YR_LEAVING   YR_IN_IBM 
    ----  -------      ------        --------     ------- 
    1      Jacky Chen  2001           2008           7 
    2      Bruce Li    1997           2010          13 
    3      Roger Lin   1998           2005           7 
     
    3 record(s) selected.

如果强行插入用户指定值的话，如在插入数据时，指定 Jacky Chen 的 yr\_in\_IBM 值为 20，应用程序会收到 SQL0798N 错误。在 GENERATED AS EXPRESSION 语句中，未指定 ALWAYS 关键字与指定 ALWAYS 关键字的效果是一样的。需要注意的是，和标识列不同，用户自定义的生成列中，不允许使用 BY DEFAULT 关键字来定义 GENERATED AS EXPRESSION。否则应用程序会收到 SQL0104N 错误。也就是说，对于用户自定义的生成列，由用户提供计算表达式，但是由 DB2 根据表达式计算产生列值，应用程序（或用户）无法自己指定具体列值。

另外，请注意如下的 INSERT 语句，yr\_leaving 的值为空。

INSERT INTO employees (empID,name,yr_onboard,yr_leaving,yr_in_IBM) VALUES 
    (4, ’ Alice Wang ’ ,2001,null,DEFAULT)

在这种情况下，yr\_in\_IBM 的值也将为空，查询表 employees 的内容，您可以验证这一点：

清单 4.查询表 employees 的内容

EMPID NAME         YR_ONBOARD   YR_LEAVING   YR_IN_IBM 
    ----  -------      ------        --------     ------- 
    1      Jacky Chen  2001           2008           7 
    2      Bruce Li    1997           2010          13 
    3      Roger Lin   1998           2005           7 
    4      Alice Wang  2001            -              - 
     
    4 record(s) selected.

对于 UPDATE 操作，生成列的值会根据表达式中涉及字段的值的变化，相应地被更改。例如，对于如下的对 yr\_leaving 值的改变，yr\_in\_IBM 的值也相应的改变。

UPDATE employees SET YR_LEAVING=2011 WHERE EMPID=3

然后，查询表 employees 的内容：

清单 5.查询表 employees 的内容

EMPID NAME         YR_ONBOARD   YR_LEAVING   YR_IN_IBM 
    ----  -------      ------        --------     ------- 
    1      Jacky Chen  2001           2008           7 
    2      Bruce Li    1997           2010          13 
    3      Roger Lin   1998           2011          13 
    4      Alice Wang  2001            -              - 
     
    4 record(s) selected.

但是生成列本身的值是不允许直接由UPDATE 更改的，如下的操作将会收到 SQL0798N 错误。

UPDATE employees SET YR_IN_IBM=11 WHERE EMPID=3

一个表可以包含一个或者多个生成列。生成列的一个重要也是主要的用途是为了提高查询的性能。如果在表定义之前，根据应用，我们就已知某特定计算表达式在查询中会经常被使用，而我们又有查询性能的要求，我们就可以为此定义一个额外的列，从而提高查询的性能。尤其是在以下两种情况下，查询中对表达式的计算将会非常昂贵：

*  同一表达式的计算在一个查询中被多次使用
 *  表达式的计算很复杂

为了提高查询性能，如果额外定义一个包含表达式计算结果的列，当执行一个包含相同计算表达式需要的查询时，我们就可以直接使用生成列，不然或查询重写优化器也会选择使用生成列从而替代表达式的计算。

请参考下面的例子，我们将创建一个含有两个生成列 c3 和 c4 的表 t1:

清单 6.创建一个含有两个生成列 c3 和 c4 的表 t1

CREATE TABLE t1 ( 
                       c1 INT, 
                       c2 DOUBLE, 
                       c3 DOUBLE GENERATED ALWAYS AS (c1 + c2) 
                       c4 GENERATED ALWAYS AS (CASE WHEN c1 > c2 THEN 1 ELSE NULL END))

然后，我们可以在生成列 c4 上创建索引：

CREATE INDEX i1 ON t1(c4)

在这种情况下，如下的查询将被优化：

SELECT COUNT(*) FROM t1 WHERE c1 > c2

查询优化器将会把以上查询重写为：

SELECT COUNT(*) FROM t1 WHERE c4 IS NOT NULL

类似的例子：

SELECT c1 + c2 FROM t1 WHERE (c1 + c2) * c1 > 100

将被重写为：

SELECT c3 FROM t1 WHERE c3 * c1 > 100

另外，对于包含表连接操作的查询，额外的生成列也将有助于优化器选择最优的连接策略。而且由于生成列可以在表建立之后在添加或者删除，这在很大程度上方便了应用对查询的优化。

## 标识列 ##

标识列作为一种特定类型的生成列，用来自动生成数值 , 被称为标识列。用户可通过在 CREATE TABLE 或者 ALTER TABLE 语句中指定 GENERATED AS IDENTITY 语句来定义标识列。

请参考下列数据定义语言（DDL），它将在数据库中创建一个表：

清单 7.在数据库中创建一个表

CREATE TABLE employees ( 
      empID INTEGER GENERATED BY DEFAULT AS IDENTITY, 
      name VARCHAR(20), 
      yr_onboard SMALLINT, 
      yr_leaving SMALLINT, 
      yr_in_IBM SMALLINT 
    ) 
    ALTER TABLE employees ADD PRIMARY KEY(empID)

该 DDL 包含标识列的生成列示例，当用 IDENTITY 属性定义表的列时，每当将一行插入表时，就会自动为该列生成一个数值。

需要注意的是，GENERATED AS IDENTITY 语句并不会保证标识列的唯一性，需要额外为标识列定义唯一约束来保证其唯一性，就如示例中所示一样，通过 ALTER TABLE 语句，将标识列定义为主键。

再者，在 GENERATED AS IDENTITY 语句中，指定 BY DEFAULT 关键字和 ALWAYS 关键字的作用是不同的。对于使用 GENERATED ALWAYS 关键字的标识列，它的值将始终由 DB2 产生；而以 GENERATED BY DEFAULT 方式定义的标识列将接受由应用程序提供的值。如果应用程序不为带有这类列的表提供列值，则 DB2 会自动生成那些值。以上表 employees 为例，如果您愿意，可以自己指定 empID 列的值。

下列 INSERT 语句将三个新的行添加到 employees 表中 :

清单 8.自己指定 empID 列的值

INSERT INTO employees (empID,name,yr_onboard,yr_leaving,yr_in_IBM) VALUES 
    (100, ’ Jacky Chen ’ ,2001,2008,7), 
    (DEFAULT, ’ Bruce Li ’ ,1997,2010,13), 
    (DEFAULT, ’ Roger Lin ’ ,1998,2005,7)

在上面的示例中，为第一行指定了标识列值（在本例中为 100），DEFAULT 关键字用来说明 DB2 将为其余行自动生成列值。在运行了这个 INSERT 语句后，您可以看到由 DB2 为 Bruce Li 和 Roger Lin 自动生成的值，还可以看到用户为 Jacky Chen 提供的值 100。 通过查询表 employees 的内容，您可以验证这一点：

清单 9.查询表 employees 的内容

EMPID NAME         YR_ONBOARD   YR_LEAVING   YR_IN_IBM 
    ----  -------      ------        --------     ------- 
    100   Jacky Chen  2001           2008           7 
    1      Bruce Li    1997           2010          13 
    2      Roger Lin   1998           2005           7 
     
    3 record(s) selected.

如果 GENERATED ALWAYS 方式定义 empID 列，INSERT 语句将只能使用 DEFAULT 关键字，而不能插入值 100；否则应用程序将收到 SQL0798N 错误。

如下示例创建和 employees 类似名为 employees2 的新表，只是对下面 **粗体**部分进行一些更改：

清单 10.创建名为 employees2 的新表

CREATE TABLE employees2( 
      empID INTEGER GENERATED ALWAYSAS IDENTITY, 
      name VARCHAR(20), 
      yr_onboard SMALLINT, 
      yr_leaving SMALLINT, 
      yr_in_IBM SMALLINT 
    ) 
    ALTER TABLE employees2 ADD PRIMARY KEY(empID)

如果对 employees2 表运行与用于 employees 表相同的 INSERT 语句，您会收到 SQL0798N 错误。我们只能将 INSERT 语句对下面 **粗体**部分进行更改：

清单 11.对 SQL 语句进行更改

INSERT INTO employees2 (empID,name,yr_onboard,yr_leaving,yr_in_IBM) VALUES 
    (DEFAULT, ’ Jacky Chen ’ ,2001,2008,7), 
    (DEFAULT, ’ Bruce Li ’ ,1997,2010,13), 
    (DEFAULT, ’ Roger Lin ’ ,1998,2005,7)

因为 employees2 表创建时带有要求 DB2 必须生成标识列的数值。现在，如果查询 employees2 表的内容，你将会得到如下的结果：

清单 12.新的查询结果

除了通过定义标识列（含有 IDENTITY 属性的列）来自动生成一个数字序列，DB2 还提供了另外一种自动生成数字序列的方法，使用序列表达式（SEQUENCE EXPRESSION）来引用序列对象。序列表达式可以出现在表达式能够出现的大多数位置。序列表达式可以指定要返回的值是新生成的值还是以前生成的值。虽然标识列和序列都用于生成数值，但是需要根据具体情况选择使用一种来代替另一种。

对于下列情况，更适合使用标识列：

*  表中只有一列需要自动生成列值
 *  每一行都需要独立的值
 *  用自动生成器来产生表的主键
 *  生成新值的进程与 INSERT 操作紧密联系

对于下列情况，更适合使用序列对象：

*  在多张表中共同使用一个序列
 *  生成新值的进程与任何对表的引用无关
 *  生成新值的进程与任何对表的引用无关

与序列对象不同，标识列是在表上定义的，因此需要某些限制。每张表最多只能有一个标识列。当创建一个列为标识列时，必须指定确切的数字类型。因为标识属性为列生成一个值，这类似于 DEFAULT 子句所做的，所以定义标识列的同时不能指定 DEFAULT 子句（WITH DEFAULT）。再者，标识列被隐式地定义为 NOT NULL。

## 修改时间戳列 ##

修改时间戳列（ROW CHANGE TIMESTAMP 生成列）也是由 DB2 自动产生的生成列的一种，但由于其相对较为特殊，因此将其单列在一节进行讲解。用户通过以下任意一种操作定义 ROW CHANGE TIMESTAMP 生成列：

*  CREATE TABLE 时定义 ROW CHANGE TIMESTAMP 列
 *  ALTER TALBE 时添加 ROW CHANGE TIMESTAMP 列

ROW CHANGE TIMESTAMP 表达式返回的时间戳表示最后一次进行行修改的时间，使用类似于 CURRENT TIMESTAMP 的本地时间表示。对于已经更新过的行，将反映对行进行的最近更新。否则，该值将对应于最初的行插入时间。若在 CREATE TABLE 时未创建 ROW CHANGE TIMESTAMP 列，而后通过 ALTER TABLE 语句进行添加，则该语句之前的行记录将被置为 ROW CHANGE TIMESTAMP 列的默认值，为 0001 年 1 月 1 日午夜。在该语句后，进行更新的行具有唯一的时间戳。可以通过查询系统目录表 SYSCAT.COLUMNS 表来查看行的修改时间戳列是否存在：

清单 13.查看行的修改时间戳列

SELECT COLNAME, ROWCHANGETIMESTAMP, GENERATED FROM SYSCAT.COLUMNS 
    WHERE TABNAME='tablename' AND ROWCHANGETIMESTAMP='Y'
     
    COLNAME       ROWCHANGETIMESTAMP    GENERATED 
    ------------  ------------------    --------- 
    LAST_UPDATE   Y                        A

在这个示例中，存在一个行修改时间戳列 LAST\_UPDATE，并通过 GENERATED ALWAYS 子句定义（值“A”表示 GENERATED ALWAYS，而值“D”表示 GENERATED BY DEFAULT）。某些应用程序需要了解特定时间范围内的数据库更新，以便进行数据复制、场景审计等等。这可以通过包含行修改时间戳的表实现，通过定义行修改时间戳列来保存 ROW CHANGE TIMESTAMP 表达式生成的时间戳。

请参考下列数据定义语言（DDL），它将在数据库中创建一个含有修改时间戳列的表：

清单 14.创建一个含有修改时间戳列的表

CREATE TABLE employees ( 
    empID INTERGER NOT NULL PRIMARY KEY, 
    name VARCHAR(20), 
    yr_onboard SMALLINT, 
    yr_leaving SMALLINT, 
    yr_in_IBM  SMALLINT 
    LAST_UPDATE TIMESTAMP NOT NULL 
                     GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)

如果我们对此表做如下插入数据的操作：

清单 15.对新表插入数据

INSERT INTO employees (empID,name,yr_onboard,yr_leaving,yr_in_IBM) VALUES 
    (1, ’ Jacky Chen ’ ,2001,2008,7), 
    (2, ’ Bruce Li ’ ,1997,2010,13), 
    (3, ’ Roger Lin ’ ,1998,2005,7)

通过查询可以发现 ROW CHANGE TIMESTAMP 列值为行插入时间：

清单 16.行插入时间

EMPID NAME         YR_ONBOARD   YR_LEAVING   YR_IN_IBM   LAST_UPDATE 
    ----  -------      ------        --------     -------     ------------ 
    1      Jacky Chen  2001           2008           7           2011-08-05 14:30:01.596000 
    2      Bruce Li    1997           2010          13           2011-08-05 14:30:01.612000 
    3      Roger Lin   1998           2005           7           2011-08-05 14:30:01.612021 
     
    3 record(s) selected.

如果我们接下来我们再对表做如下更新：

UPDATE employees SET yr_leaving=2009,yr_in_IBM=8 WHERE empID=1

再次对 employees 表进行查询，可以发现，被更新的行具有新的行修改时间戳 (**粗体**)。

清单 17.查看新的行修改时间戳

EMPID NAME         YR_ONBOARD   YR_LEAVING   YR_IN_IBM   LAST_UPDATE 
    ----  -------      ------        --------     -------     ------------ 
    1      Jacky Chen  2001           2009           8           2011-08-05 14:31:50.131000
    2      Bruce Li    1997           2010          13           2011-08-05 14:30:01.612000 
    3      Roger Lin   1998           2005           7           2011-08-05 14:30:01.612021 
     
    3 record(s) selected.

另外我们还可以通过 ROW CHANGE TIMESTAMP 列查询一段时间内修改过的行的情况，例如查询最近 3 个月内发生修改的所有行，可采用如下方法：

清单 18.查询一段时间内修改过的行的情况

SELECT * FROM employees WHERE 
                 LAST_UPDATE <= CURRENT TIMESTAMP 
    AND 
                 LAST_UPDATE >= CURRENT TIMESTAMP - 90 days

对于 CREATE TABLE 期间未创建 ROW CHANGE TIMESTAMP 列的情况，可以通过 ALTER TABLE 添加行修改时间戳列，如下表所示：

清单 19.通过 ALTER TABLE 添加行修改时间戳列

ALTER TABLE employees ADD COLUMN 
                 LAST_UPDATE TIMESTAMP NOT NULL 
                 GENERATED ALWAYS 
                 FOR EACH ROW ON UPDATE AS 
                 ROW CHANGE TIMESTAMP

此时，在 ALTER TABLE 添加 ROW CHANGE TIMESTAMP 语句前已经存在的行，将被置为行修改时间戳列的默认值，如下表 (**粗体**):

清单 20.查询结果

AEMPID NAME         YR_ONBOARD   YR_LEAVING   YR_IN_IBM   LAST_UPDATE 
    ----  -------      ------        --------     -------     ------------ 
    1      Jacky Chen  2001           2009           8           0001-01-01 00:00:00.000000
    2      Bruce Li    1997           2010          13           0001-01-01 00:00:00.000000
    3      Roger Lin   1998           2005           7           0001-01-01 00:00:00.000000
     
    3 record(s) selected.

然后，如果对原有列进行更新，则 LAST\_UPDATE 列将具有 CURRENT TIMESTAMP 值；或者 INSERT 新的行记录，新的行记录也会有当前的系统时间戳值。

## 小结 ##

综上，本文一共介绍了四种在 DB2 表中自动生成列值的方法 :

*  **普通的生成列**，列值由用户定义的表达式产生，主要用来简化应用程序以及提高查询性能；
 *  **标识列**，用来自动生成数值，通常用来自动编号，DB2 还提供了另外一种自动生成数字序列的方法，使用序列表达式，上文中介绍了二者的区别；
 *  **修改时间戳列**，也是由 DB2 自动产生的生成列的一种，用来记录某行发生变化的时间戳，从而帮助应用程序了解特定时间范围内的数据库更新，以便进行数据复制、场景审计等等。