你的位置:首页 > 数据库

[数据库]数据库时间戳设计


话说毕业后做过几年系统,对数据库也就是增删改查的水平,后来做的工作大多也没有对数据库深入使用,对数据库的学习确实比较松懈,真是对不起自己在这个行业摸爬这么多年的经历。昨天前对timestamp这个数据库类型的理解,仅限于日常见到最多的Oracle的timestamp的用法,一个毫秒级的时间戳(这里其实是自己的认识上的误区,其实人家的精度能做到纳米级别)。然后我的好兄弟一下刷新了我对他的仰慕,填补了我多年的知识缺陷和空白(我们要不要在一起啊。。。在一起啊。。。)。在SQLServer中,这个值居然由SQLServer自行维护并且能随着insert和update自动更新,而且这个字段存储的和时间也毫无关系,只是一个顺序递增的binary值。

言归正传,先说说我遇到的问题。假设现在有两个数据库表,A和B,A是数据源,提供数据,每隔十分钟会insert进一批数据。B是目标。另外有个SQL语句需要每隔十分钟将A表的数据复制到B表,每次只复制增量。对于这种需求,我最喜欢的做法是insert into select * from xxx的语法了,这里唯一的问题就是如何区别出增量的问题。

假设这个表有一个自增列,1-1000000000000,那么我每次只需要记录下上一次完成到哪一个就完事儿了。但是这里有诸多历史问题导致了这个方案只能想想就罢了。因为A不是我的,B也不是我的,我就是那个insert into……,好吧,这是一个合作的项目,我对A和B的掌控能力和理解深度都不太深。而且这样还有一个问题,自增列只会自增,一旦生成是不会更新的,当数据发生变化的时候,我们认为这些数据也是需要被同步的,那么update其实我们也需要。

原来我们工程师给出来的方案中无法避免的一个问题是它使用了>时间1 and <=时间2,当时间2这个时间点,有多条记录被insert的话(相同时间),那么在表A被insert,而同步语句又在执行的时候,是有很高的几率导致时间2这个时间点的数据记录只被部分传输。而下一次同步的时候,时间2已然变成时间1,而大于号将导致丢失的数据再也补不回来。(假设是>= and <=,将导致时间2这个时间点的数据重复)

如果能解决时间2这个时间点的数据只有1条,这个问题就迎刃而解了。那么对于SQLServer而言,timestamp这个数据类型,就是我们要的(虽然官方文档

https://msdn.microsoft.com/zh-cn/library/ms182776.aspx告诉我们,建议我们使用rowversion,但是在SQLServer2008R2的版本上,我没有成功,貌似这个类型不支持,这个回头再深究)。

那么我们还有Oracle、MySQL、PostgreSQL……OMZ……

简单地搜了一下,结论大致如下(有不对的请大家及时指正)

1、Oracle的timestamp,是一个纳秒级的时间,仅此而已,无法保证唯一性。而Oracle中也没有类似的数据类型能够直接做这件事。

这里有一点需要说明一下,Oracle的timestamp的默认时间精度是微秒(p=6),而Oracle能记录到(p=9)也就是纳秒。

可以参考:http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006050

但是有网友反映无论如何插入数据后再返回,只能取到微秒,后面3位总是0。网友的回复是这样的:

ORACLE的timestamp可精确到9位,但ORACLE取时间的函数也是调用OS的取时间函数,而LINUX只能精确到6位,所以也只能如此了。这个在O'REILLY出版的《ORACLE PL/SQL程序设计》中有说明。

原文链接:http://www.itpub.net/forum.php?mod=viewthread&tid=1931266&extra=&highlight=&page=4

2、MySQL的timestamp,和SQLServer类似,但是也很不相同,首先它能自动设置,不需要人工干预,自动设置为当前时间(这一点和SQLServer 不一样,SQLServer设置的是一个时间无关的二进制值,而这个设置的只是一个时间。)也就是说,它还会重复。而且,非常遗憾的是,这个时间还是个秒级的!同时,MySQL中也没有类似的数据类型能够直接做这件事。

可以参考:

http://www.jb51.net/article/51794.htm

http://dev.mysql.com/doc/refman/5.5/en/datetime.html

http://dev.mysql.com/doc/refman/5.5/en/fractional-seconds.html

http://www.jb51.net/article/51521.htm

3、PostgreSQL的timestamp,和MySQL类似,但是秒以下级别的精度有6位(也就是微秒,感觉各种神翻译把microsecond翻译成了毫秒),但是谁又能保证这个级别的时间戳就不会重复呢?

可以参考:

http://www.postgresql.org/docs/current/static/datatype-datetime.html

http://www.yiibai.com/manual/postgresql/datatype-datetime.html

那么在以上三种数据库中,我们选择了序号器+触发器(Cache需要设置大一点,保证性能)的方式来搞定这个事情,注意,这里触发器需要处理update的情况。

剩下的事情大家应该都会了,就是比较一个范围的问题了。