Can I get a unique TIMESTAMP for every record in MySQL
mysql, sql, timestamp
Solution
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.
You can use a UUID if you need a unique number that is ordered temporally.
`SELECT UUID();` yields something like:
45f9b8d6-8f00-11e1-8920-842b2b55ce56
And some time later:
004b721a-8f01-11e1-8920-842b2b55ce56
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(), 16, 3), '-', SUBSTR(UUID(), 10, 4),
'-', SUBSTR(UUID(), 1, 8))
Yields:
1e1-8f00-45f9b8d6
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`).
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.
Useful reading for understanding the components of UUID
- Universally unique identifier (UUID)
- Extracting timestamp and MAC address from UUIDs
Problem
Is there a possibility of getting a unique timestamp value for for each record in MySQL??.. I created a sample table ``` CREATE TABLE t1 (id int primary key, name varchar(50), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ``` and ran some sample INSERTIONS and seems to be timestamp values are duplicated. ``` e.g insert into t1(id,name) values(1,"test"); ```