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:

enter image description here

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;

enter image description here

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?

  • UUIDs (and other hash-like ids) are not intended for display. Whyever would you want what you are asking for? My question is serious. If you have a good use case for getting that ordering (and thanks for the two workarounds), then maybe the documentation should point them out.
    – Rick James
    Aug 11, 2022 at 16:59

2 Answers 2


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.

  • A subtle incompatibility between MariaDB and MySQL or older versions of MariaDB? Is it noted in the documentation?
    – Rick James
    Aug 11, 2022 at 17:01
  • The incompatibility within MariaDB existed only between the preview release of uuid in the 10.7 beta after that so its not documented. I hope to write up the impact of this answer and the MySQL difference in the KB for the uuid data type today.
    – danblack
    Aug 11, 2022 at 23:46
  • I haven't found a decent MySQL reference with this level of detail. Do you know one?
    – danblack
    Aug 12, 2022 at 8:09
  • Well, this Question points out one incompatibility (albeit a minor one). I'm pretty sure Oracle does not shuffle the bits the way that MariaDB does.
    – Rick James
    Aug 12, 2022 at 16:48

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); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.