How to maintain insertion order using UUID

Question

On a pet project I started to work with UUIDs. The application is fairly simple, it adds data in a MySQL database with binary(16) primary keys.

To generate PK's I'm using JUG this way :

UUID uuid = Generators.timeBasedGenerator().generate();

Then I convert it to a byte array :

byte[] b = UUIDUtil.asByteArray(uuid);

The problem now is that I have no idea on the insertion order of my rows. If I sort my rows by the ID some recent rows comes before older rows (according to a DATETIME field)

What should I do to be able to keep the insertion order of my rows (for sorting purpose) ?

Illustration of the problem, UUIDs are sorted ASC, I'm expecting created_at being in the same order.

select hex(id), created_at from myTable order by id

+----------------------------------+---------------------+
| hex(id)                          | created_at          |
+----------------------------------+---------------------+
| 0913FF1FC53911E39D8D6F7C246EE143 | 2014-04-16 09:30:50 |
| 09378CB1C53911E39D8DD94CAEA8D23F | 2014-04-16 09:30:50 |
| 094A9F83C53911E39D8DDF087D0ED31A | 2014-04-16 09:30:51 |
| 0CBE40D5C0B711E38172B9CB0C485EE3 | 2014-04-10 15:50:17 |
| 0CBF5247C0B711E3817277CAF5E1D5B5 | 2014-04-10 15:50:17 |
| 0CC03CA9C0B711E381721BA12057F9E2 | 2014-04-10 15:50:17 |
| 0CC14E1BC0B711E381720505D5FFDCD3 | 2014-04-10 15:50:17 |
| 0CC2387DC0B711E38172F9A6B29EB613 | 2014-04-10 15:50:17 |
| 0CC349EFC0B711E381723D1DB8515E3F | 2014-04-10 15:50:17 |
| 0CC43451C0B711E3817257D8AFFD09B8 | 2014-04-10 15:50:17 |
| 0CC545C3C0B711E381729B3CB87CD707 | 2014-04-10 15:50:17 |
| 0CC8C835C0B711E38172CDA11992F9BC | 2014-04-10 15:50:17 |
| 0E33A6B5C08B11E396829782BD5365D2 | 2014-04-10 10:35:22 |
| 0E368CE7C08B11E39682A9F63D5EF0E6 | 2014-04-10 10:35:22 |
| 0E383A99C08B11E396825D6048BFC696 | 2014-04-10 10:35:22 |
| 128DD6C5C53911E39D8D7577DB523A2C | 2014-04-16 09:31:06 |
+----------------------------------+---------------------+

EDIT

Just to clarify, I of course know and am used to auto_increment PK's, I just wanted to see how it was achievable to work without them. (In case it is !)

Answer 1

Today there's a proposal to a new version that keeps the byte order of the timestamp, the version 6.

If you want to create a UUIDv6 by yourself, first generate a UUIDv1 and chenge the byte order this way:

From: aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee (time-based, version 1)
To:   cccbbbba-aaaa-6aaa-dddd-eeeeeeeeeeee (time-ordered, version 6)

If you want a library that already does it, you may use uuid-creator, assuming you are coding in Java. Example:

// Generate a UUIDv6
UUID uuid = UuidCreator.getTimeOrdered();

Project on github: https://github.com/f4b6a3/uuid-creator

If you are coding in PHP, you can use a library from Ben Ramsey. I think it was the first project to implement UUIDv6:

Project on github: https://github.com/ramsey/uuid

Answer 2

Please see this link https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/

Here the author rearranged the UUID to make it sequential, for this he created one MySQL user defined function.