DB2从9.5以后引入了隐藏列,就是你用select * from table的方式查询不到这个列,但是可以显式的指定列名来看该列的值。
CREATE TABLE CUSTOMER
(
CUSTNO INTEGER NOT NULL,
CUST_NAME VARCHAR(50),
CUST_INFOCHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
)
插入数据的时候,不用指定隐藏列
INSERT INTO CUSTOMER VALUES(1,'111')
INSERT INTO CUSTOMER VALUES(2,'222')
INSERT INTO CUSTOMER VALUES(3,'333')
INSERT INTO CUSTOMER VALUES(4,'444')
隐藏列的值在这里是插入的时间戳,如果你更新一行,那么这个时间戳也是相应改变的。
describe 表是可以看到这个隐藏列的
db2 => describe table customer
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
CUSTNO SYSIBM INTEGER 4 0 No
CUST_NAME SYSIBM VARCHAR 50 0 Yes
CUST_INFOCHANGE SYSIBM TIMESTAMP
CREATE TABLE CUSTOMER
(
CUSTNO INTEGER NOT NULL,
CUST_NAME VARCHAR(50),
CUST_INFOCHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
)
插入数据的时候,不用指定隐藏列
INSERT INTO CUSTOMER VALUES(1,'111')
INSERT INTO CUSTOMER VALUES(2,'222')
INSERT INTO CUSTOMER VALUES(3,'333')
INSERT INTO CUSTOMER VALUES(4,'444')
隐藏列的值在这里是插入的时间戳,如果你更新一行,那么这个时间戳也是相应改变的。
describe 表是可以看到这个隐藏列的
db2 => describe table customer
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
CUSTNO SYSIBM INTEGER 4 0 No
CUST_NAME SYSIBM VARCHAR 50 0 Yes
CUST_INFOCHANGE SYSIBM TIMESTAMP