sql中使用timestamp增量抽取数据

网址：http://www.cnblogs.com/shuaifei/p/4469526.html

最近的项目中需要对上百万级的数据进行增量抽取操作，因此了解了一下TIMESTAMP的应用，特此记录

timestamp -- 时间戳：数据库中自动生成的唯一二进制数字，与时间和日期无关的， 通常用作给表行加版本戳的机制。存储大小为 8个字节

每个数据库都有一个计数器，当对数据库中包含 timestamp 列的表执行插入或更新操作时，该计数器值就会增加。该计数器是数据库时间戳。这 可以跟踪数据库内的相对时间，而不是时钟相关联的实际时间。一个表只能有一个 timestamp 列。每次修改或插入包含 timestamp 列的行 时，就会在 timestamp 列中插入增量数据库时间戳值。这一属性使 timestamp 列不适合作为键使用，尤其是不能作为主键使用。对行的任 何更新都会更改 timestamp 值，从而更改键值。如果该列属于主键，那么旧的键值将无效，进而引用该旧值的外键也将不再有效。如果该表在动态游标 中引用，则所有更新均会更改游标中行的位置。如果该列属于索引键，则对数据行的所有更新还将导致索引更新。

使用某一行中的 timestamp 列可以很容易地确定该行中的任何值自上次读取以后是否发生了更改。如果对行进行了更改，就会更新该时间戳值。如果没有对行进行更改，则该时间戳值将与以前读取该行时的时间戳值一致。

使用记录:

现有源表tmp_a和目标表tmp_b，需要每天都将tmp_a中的修改的数据同步到tmp_b中

1 2 3 4 5 6 7

--创建源表tmp_a create table tmp_a(a varchar(10),b TIMESTAMP) insert into tmp_a(a) values( 'abc') insert into tmp_a(a) values( '123')   --创建目标表tmp_b create table tmp_b(a varchar(10),b TIMESTAMP)

　查询tmp_a和tmp_b数据

1 2 3

--查询tmp_a SELECT * FROM tmp_a SELECT * FROM tmp_b

　　结果,最大的值为 0x00000000000084FD

 

将tmp_a的数据到如tmp_b中

1 2 3

--导入表tmp_b INSERT INTO tmp_b (a,b) SELECT a,b FROM tmp_a

　执行后会发现会报错误:

消息 273，级别 16，状态 1，第 2 行
不能将显式值插入时间戳列。请对列列表使用 INSERT 来排除时间戳列，或将 DEFAULT 插入时间戳列。

查询资料后得知：timestamp这个值一般都是数据库自动添加和修改的，相当于自动增长标识一样（而且执行update修改语句这个字段也会自动更新），所以一般这个字段我们只做查询操作。如果要更新这个字段则会提示这个错误信息：不能更新时间戳列。但是这个字段是可以手动添加的，不过也只能使用DEFALUT字段（default字段为SQL Service数据库的一个默认值），如果传入其他值则会提示错误信息：不能将显式值插入时间戳列。请对列列表使用 INSERT 来排除时间戳列，或将 DEFAULT 插入时间戳列

因此需要将timestamp数据进行转化

修改tmp_b的结构为：

1 2	--创建目标表tmp_b create table tmp_b(a varchar(10),b VARBINARY(8))

　然后将tmp_a中列b的值转化为 十六进制字符串

1 2 3

--导入表tmp_b INSERT INTO tmp_b (a,b) SELECT a,CAST(b AS VARBINARY(8)) FROM tmp_a

　执行成功，这样tmp_a和tmp_b数据相同

修改tmp_a中的数据，会发现tmp_a中的列值为‘abc’的列b已修改

1 2 3 4 5 6 7 8 9

UPDATE tmp_a SET a='def' WHERE a='abc'   SELECT * FROM tmp_a   --结果 a   b def 0x0000000000008502 123 0x0000000000008501

　　对比两个表的数据,不同的b列数据,则进行更新操作

1	SELECT * FROM tmp_a ta WHERE ta.b>0x00000000000084FD