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…

Accepted Answer

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

Disclaimer: I'm the main contributor of `uuid-creator`.

How to maintain insertion order using UUID

Solution

Problem