我可以为MySQL中的每个记录获取唯一的TIMESTAMP吗? [英] Can I get a unique TIMESTAMP for every record in MySQL

问题描述

是否有可能为MySQL中的每条记录获取唯一的时间戳值??

Is there a possibility of getting a unique timestamp value for for each record in MySQL??..

我创建了一个示例表

CREATE TABLE t1 (id int primary key, name varchar(50), 
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

并运行了一些INSERTIONS示例，似乎时间戳记值重复了.

and ran some sample INSERTIONS and seems to be timestamp values are duplicated.

e.g  insert into t1(id,name) values(1,"test");

推荐答案

有一天(5.6.4)，MySQL将提供

Some day soon (5.6.4), MySQL will provide fractional seconds in TIMESTAMP columns, however, even fractional seconds aren't guaranteed to be unique, though theoretically, they'd most often be unique, especially if you limited MySQL to a single thread.

如果 UUID ，则可以使用 UUID 您需要一个按时间顺序排列的唯一编号.

You can use a UUID if you need a unique number that is ordered temporally.

SELECT UUID()会产生类似以下内容:

SELECT UUID() yields something like:

45f9b8d6-8f00-11e1-8920-842b2b55ce56

一段时间后:

004b721a-8f01-11e1-8920-842b2b55ce56

UUID的前三个部分由时间组成，但是，它们的顺序是从最高精度到最小精度，因此您需要使用SUBSTR()和CONCAT()颠倒前三个部分，如下所示:

The first three portions of a UUID consist of the time, however, they're in order from highest precision to least, so you'd need to reverse the first three portions using SUBSTR() and CONCAT() like this:

SELECT CONCAT(SUBSTR(UUID(), 15, 4), '-', SUBSTR(UUID(), 10, 4),
  '-', SUBSTR(UUID(), 1, 8))

收益:

11e1-8f00-45f9b8d6

您显然不能使用像这样的函数作为默认值，因此必须在代码中进行设置，但这是保证唯一的时间顺序值. UUID()的工作级别比秒(时钟周期)低得多，因此可以确保每次调用都具有唯一性，并且开销较低(没有像auto_increment那样的锁定).

You obviously couldn't use a function like this as a default value, so you'd have to set it in code, but it's a guaranteed unique temporally ordered value. UUID() works at a much lower level than seconds (clock cycles), so it's guaranteed unique with each call and has low overhead (no locking like auto_increment).

在数据库服务器上使用UUID()可能比在应用程序服务器上使用类似的功能(例如PHP的microtime()功能)更可取，因为您的数据库服务器更加集中.您可能有不止一个应用程序(Web)服务器，该服务器可能会生成冲突值，而microtime()仍不能保证唯一的值.

Using the UUID() on the database server may be preferred to using a similar function, such as PHP's microtime() function on the application server because your database server is more centralized. You may have more than one application (web) server, which may generate colliding values, and microtime() still doesn't guarantee unique values.

这篇关于我可以为MySQL中的每个记录获取唯一的TIMESTAMP吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！