Recently I needed to implement a sort of queue system in a database. The PHP/MySQL script had to record new user-generated entries with a primary key based on when they were created. The problem was that UTC_TIMESTAMP(), by itself, isn’t granular enough to generate unique keys for calls requested within less than a second from each other – and, even if it was that granular, there would always be the remote eventuality of unpredictable primary key overlap (and consequent data loss) hanging over the system. So, I thought I’d keep the UTC_TIMESTAMP() value for the overall time in the primary key and append extra figures to it in order to make the key unique no matter how close two requests were. The appended extra figures represent only a correction value to the sampled datetime and are unrelated to any time measurement.
In this example, I use 2 extra-digits which account for a maximum of 100 new possible rows that can be inserted within the same second. Such limit can be extended. The column id (primary key) is a DOUBLE, here.
After trimming off the two UTC_TIMESTAMP() top digits from 2011, which leaves us with the stub “11” standing in for the year 2011, the result gets then multiplied by 100 to make room for the 2 extra-digits on the right handside. Below, it’s shown how to do that. 3 is just an example, any number between 1 and 99 will do.
SELECT (100*(UTC_TIMESTAMP()-2e13)+3);;
Someone might argue that taking the 2 top digits off makes this method work fine only up until the year 2099. Yep, that’s correct.
Let’s now go through the details of how to insert new rows with the composite key datetime + adjustment.
I need to insert a first row in the table since this example wouldn’t work without an entry’s id to compare with and I don’t want to slow down the Server’s digestion with verifying away a condition planned to be used only once and never again, at least on what I was working on then.
INSERT INTO tbl (id,data,notes) VALUES (100*(UTC_TIMESTAMP()-2e13),'test', '');
Then, it follows the snippet that generates appropriate consecutive primary keys.
INSERT INTO tbl (id,data,notes) VALUES (
IF
(
(100*(UTC_TIMESTAMP()-2e13))>(SELECT id FROM tbl AS t ORDER BY id DESC LIMIT 1),
100*(UTC_TIMESTAMP()-2e13),
(SELECT id FROM tbl AS t ORDER BY ids DESC LIMIT 1)+1
),
'add data',
'add notes');
The calculation of the primary key is carried out through the MySQL function IF(expr1, expr2, expr3).
The function IF(expr1, expr2, expr3) can be probably better grasped by adding some more words to it, IF(test expr, test-passed expr, no-pass expr). Better? By the way, that’s a function IF(*,*,*) not a statement IF which is different.
The conditional function, in the snippet, simply says “IF the UTC_TIMESTAMP-based key isn’t greater than the last one found in the table then someone else must’ve already created it”, so now my new key will be the last one which is already there plus 1. In this way, I’m sure that the new primary key will be unique regardless of the new entries created so far, within the same second.
You will have noticed that subqueries have also been used in the snippet above, and, as you probably know, subqueries like that can sometimes be a bit fussy to deal with because of the restrictions imposed on them. Renaming the column id FROM tbl “AS t” does the trick and makes it all hold together. Without it, an You can’t specify target table for update in FROM clause error would pop up.
Check this out for more details about subquery-restrictions.
stc0 