数据库时间戳设计

话说毕业后做过几年系统，对数据库也就是增删改查的水平，后来做的工作大多也没有对数据库深入使用，对数据库的学习确实比较松懈，真是对不起自己在这个行业摸爬这么多年的经历。昨天前对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的情况。

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