MariaDB UUID datatype not orders correctly

Question

I'm using MariaDB 10.8.3. I have a table with a PK of type UUID. I'm inserting ordered UUIDs generated in application side. When I retrive the data of the table ordering by UUID column, it is not ordered as expected. For example:

SELECT id_label, date_add FROM mod_label ORDER BY id_label ASC;

The retrieved data is this:

But if I cast the UUID to char or convert to hex, the result is ordered as expected:

SELECT id_label, date_add FROM mod_label ORDER BY CAST(id_label AS CHAR(36)) ASC;
SELECT id_label, date_add FROM mod_label ORDER BY HEX(id_label) ASC;

As I understand it, MariaDB saves UUIDs as 128-bit integers, so I don't understand this behavior.

Can someone explain me this behavior and how to fix it?

Answer 1

Because MariaDB UUID are stored in an index friendly manner, the order (per code) is:

UUID values, llllllll-mmmm-Vhhh-vsss-nnnnnnnnnnnn

are stored as, nnnnnnnnnnnn-vsss-Vhhh-mmmm-llllllll

This provides a sorting order, if a UUIDv1 (node and timestamp) is used, of the node, followed by the timestamp.

Answer 2

If you use uuid v7, then you can convert it to mariadb-sortable format with php 8.1:

$uuid = Uuid::uuid7()->toString()

$sortableUuid = substr($uuid, 24, 8)
            . "-"
            . substr($uuid, 32, 4)
            . "-"
            . substr($uuid, 19, 4)
            . "-"
            . substr($uuid, 14, 4)
            . "-"
            . substr($uuid, 0, 8)
            . substr($uuid, 9, 4);