UUID to BIN: problematic & solutions, POC

I recently tested MariaDB’s plugin architecture to add functions for UUIDs. My friend Daniël took a look at the GitHub repo and left me some insightful comments, which I addressed in issue #1.

If you know me, you may know that while my daughters are practicing basketball, I’m waiting coding… As I’ve always been very interested in UUIDs, especially how to use and store them as Primary Keys in InnoDB, I decided to investigate the right way to do it in MariaDB.

I implemented uuid_to_bin() and bin_to_uuid(). MariaDB and MySQL, by default, use UUIDv1 when they generate UUIDs. And this is a problem for Primary Keys in InnoDB! I already explained this in this post.

Using UUIDv1 as a primary key can trigger severe InnoDB Cluster Index rebalancing. Also, for efficiency, it’s better to store the UUIDs as BINARY. Therefore, in MySQL, the uuid_to_bin() allows a swap flag to let the fixed part of the UUID at the beginning and put the timestamp part at the end when converted as binary.

Of course, with MySQL, if you try to convert a binary representation of a UUID without setting the swap flag, the result can be completely different from what you expect. This often leads to some issues.

My thinking here is to find a way to avoid this kind of problem.

So instead of returning 16 bytes when converting a UUID to BINARY, I decided to return 17 bytes when UUIDs_v1 are used, with the last byte (the 17th) serving as a flag indicating whether swap is used.

For other UUID versions, no swap is applied (version 4 doesn’t include any timesamp, and in version 7 it is already ordered), and it returns only 16 bytes.

The functions allows then to set the flag, but will be used only with version 1:

MariaDB > SELECT uuid() INTO @uuid;
Query OK, 1 row affected (0.000 sec)

MariaDB > select @uuid, uuid_version(@uuid);
+--------------------------------------+---------------------+
| @uuid                                | uuid_version(@uuid) |
+--------------------------------------+---------------------+
| 25c58bae-0cb6-11f1-a5b7-5e1b9081e705 |                   1 |
+--------------------------------------+---------------------+
1 row in set (0.000 sec)

MariaDB > SELECT HEX(UUID_TO_BIN(@uuid));
+------------------------------------+
| HEX(UUID_TO_BIN(@uuid))            |
+------------------------------------+
| 25C58BAE0CB611F1A5B75E1B9081E70500 |
+------------------------------------+
1 row in set (0.000 sec)

MariaDB > SELECT HEX(UUID_TO_BIN(@uuid,1));
+------------------------------------+
| HEX(UUID_TO_BIN(@uuid,1))          |
+------------------------------------+
| A5B75E1B9081E70511F10CB625C58BAE01 |
+------------------------------------+
1 row in set (0.000 sec)

MariaDB > SELECT LENGTH(UUID_TO_BIN(@uuid,1));
+------------------------------+
| LENGTH(UUID_TO_BIN(@uuid,1)) |
+------------------------------+
|                           17 |
+------------------------------+
1 row in set (0.000 sec)

We can see that it doesn’t have any effect on other versions:

MariaDB > SELECT uuid_v4() INTO @uuid4;
Query OK, 1 row affected (0.000 sec)

MariaDB > SELECT uuid_v7() INTO @uuid7;
Query OK, 1 row affected (0.000 sec)

MariaDB > SELECT @uuid4, uuid_version(@uuid4);
+--------------------------------------+----------------------+
| @uuid4                               | uuid_version(@uuid4) |
+--------------------------------------+----------------------+
| 753cfcf4-96da-42ae-a00c-20e48a2b85b7 |                    4 |
+--------------------------------------+----------------------+
1 row in set (0.000 sec)

MariaDB > SELECT @uuid4, uuid_version(@uuid7);
+--------------------------------------+----------------------+
| @uuid4                               | uuid_version(@uuid7) |
+--------------------------------------+----------------------+
| 753cfcf4-96da-42ae-a00c-20e48a2b85b7 |                    7 |
+--------------------------------------+----------------------+
1 row in set (0.000 sec)

MariaDB > SELECT HEX(UUID_TO_BIN(@uuid4));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid4))         |
+----------------------------------+
| 753CFCF496DA42AEA00C20E48A2B85B7 |
+----------------------------------+
1 row in set (0.000 sec)

MariaDB > SELECT HEX(UUID_TO_BIN(@uuid4,1));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid4,1))       |
+----------------------------------+
| 753CFCF496DA42AEA00C20E48A2B85B7 |
+----------------------------------+
1 row in set (0.000 sec)

MariaDB > SELECT HEX(UUID_TO_BIN(@uuid7));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid7))         |
+----------------------------------+
| 019C70579D887A28B5F9C20D58E82205 |
+----------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT HEX(UUID_TO_BIN(@uuid7,1));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid7,1))       |
+----------------------------------+
| 019C70579D887A28B5F9C20D58E82205 |
+----------------------------------+
1 row in set (0.000 sec)

MariaDB > SELECT LENGTH(UUID_TO_BIN(@uuid4));
+-----------------------------+
| LENGTH(UUID_TO_BIN(@uuid4)) |
+-----------------------------+
|                          16 |
+-----------------------------+
1 row in set (0.000 sec)

MariaDB > SELECT LENGTH(UUID_TO_BIN(@uuid7));
+-----------------------------+
| LENGTH(UUID_TO_BIN(@uuid7)) |
+-----------------------------+
|                          16 |
+-----------------------------+
1 row in set (0.000 sec)

As shown above, for UUID versions 4 and 7, the swap flag has no effect.

Automatic Decoding

Thanks to the extra byte, it’s possible to always decode the binary representation of the UUID automatically without having to specify the flag. If the binary representation contains the extra byte, the flag in the function doesn’t have any effect:

MariaDB > SELECT @uuid;
+--------------------------------------+
| @uuid                                |
+--------------------------------------+
| 25c58bae-0cb6-11f1-a5b7-5e1b9081e705 |
+--------------------------------------+
1 row in set (0.000 sec)

MariaDB > SELECT @uuid, bin_to_uuid(@uuidbin) automatic,
          bin_to_uuid(@uuidbin,0) force_flag_off, 
          bin_to_uuid(@uuidbin,1) force_flag_on\G
*************************** 1. row ***************************
         @uuid: 25c58bae-0cb6-11f1-a5b7-5e1b9081e705
     automatic: 25c58bae-0cb6-11f1-a5b7-5e1b9081e705
force_flag_off: 25c58bae-0cb6-11f1-a5b7-5e1b9081e705
 force_flag_on: 25c58bae-0cb6-11f1-a5b7-5e1b9081e705
1 row in set (0.000 sec)

However, when the extra byte is not present, to keep compatibility with old data, the flag can be used:

MariaDB > SELECT uuid_to_bin(@uuid,1) into @uuidbin;
Query OK, 1 row affected (0.001 sec)

MariaDB > SELECT HEX(@uuidbin);
+------------------------------------+
| hex(@uuidbin)                      |
+------------------------------------+
| A5B75E1B9081E70511F10CB625C58BAE01 |
+------------------------------------+
1 row in set (0.000 sec)

We can see that the flag is present (last 2 characters, 01). Let’s store the binary representation using 16 bytes:

MariaDB > SELECT UNHEX("A5B75E1B9081E70511F10CB625C58BAE") INTO @bin;
Query OK, 1 row affected (0.000 sec)

MariaDB > SELECT bin_to_uuid(@bin);
+--------------------------------------+
| bin_to_uuid(@bin)                    |
+--------------------------------------+
| a5b75e1b-9081-e705-11f1-0cb625c58bae |
+--------------------------------------+
1 row in set (0.000 sec)

MariaDB > SELECT bin_to_uuid(@bin,1);
+--------------------------------------+
| bin_to_uuid(@bin,1)                  |
+--------------------------------------+
| 25c58bae-0cb6-11f1-a5b7-5e1b9081e705 |
+--------------------------------------+
1 row in set (0.001 sec)

MariaDB > SELECT bin_to_uuid(@bin,0);
+--------------------------------------+
| bin_to_uuid(@bin,0)                  |
+--------------------------------------+
| a5b75e1b-9081-e705-11f1-0cb625c58bae |
+--------------------------------------+
1 row in set (0.001 sec)

Only the one using the flag returns the right value.

Default behavior

I also added GLOBAL and SESSION variables that define the behavior of the uuid_to_bin() function when used with UUID version 1:

MariaDB > SHOW VARIABLES LIKE 'uuid_to_bin_swap';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| uuid_to_bin_swap | OFF   |
+------------------+-------+
1 row in set (0.001 sec)

MariaDB > SELECT @uuid, hex(uuid_to_bin(@uuid));
+--------------------------------------+------------------------------------+
| @uuid                                | hex(uuid_to_bin(@uuid))            |
+--------------------------------------+------------------------------------+
| 25c58bae-0cb6-11f1-a5b7-5e1b9081e705 | 25C58BAE0CB611F1A5B75E1B9081E70500 |
+--------------------------------------+------------------------------------+
1 row in set (0.000 sec)

MariaDB > SET uuid_to_bin_swap=1;
Query OK, 0 rows affected (0.000 sec)

MariaDB > SELECT @uuid, hex(uuid_to_bin(@uuid));
+--------------------------------------+------------------------------------+
| @uuid                                | hex(uuid_to_bin(@uuid))            |
+--------------------------------------+------------------------------------+
| 25c58bae-0cb6-11f1-a5b7-5e1b9081e705 | A5B75E1B9081E70511F10CB625C58BAE01 |
+--------------------------------------+------------------------------------+
1 row in set (0.000 sec)

Example

Let’s now create a table using UUIDv1 as the primary key:

CREATE TABLE `t` (
  `uuid` varbinary(17) NOT NULL DEFAULT uuid_to_bin(uuid()) 
         CHECK (uuid_version(bin_to_uuid(`uuid`)) = 1),
  `name` varchar(20) DEFAULT NULL,
  `created` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB

As you can see, we store the binary representation of the UUID within 17 bytes, and we generate the UUID automatically and convert it by default. Additionally, we created a check constraint to ensure we use UUIDv1 when one is specified.

We also need to set the GLOBAL variable uuid_to_bin_swap to ON to ensure the right incremental order of the new records:

MariaDB > SHOW GLOBAL VARIABLES LIKE 'uuid_to_bin_swap';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| uuid_to_bin_swap | OFF   |
+------------------+-------+
1 row in set (0.001 sec)

MariaDB > SET GLOBAL uuid_to_bin_swap=on;
Query OK, 0 rows affected (0.000 sec)

MariaDB > SHOW GLOBAL VARIABLES LIKE 'uuid_to_bin_swap';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| uuid_to_bin_swap | ON    |
+------------------+-------+
1 row in set (0.001 sec)

We can add some records…

MariaDB > select bin_to_uuid(uuid), name, created from t;
+--------------------------------------+-------+---------------------+
| bin_to_uuid(uuid)                    | name  | created             |
+--------------------------------------+-------+---------------------+
| dccdbd5c-0cce-11f1-a5b7-5e1b9081e705 | one   | 2026-02-18 14:36:43 |
| df10587d-0cce-11f1-a5b7-5e1b9081e705 | two   | 2026-02-18 14:36:47 |
| fb487fec-0cce-11f1-a5b7-5e1b9081e705 | three | 2026-02-18 14:37:34 |
| 050f7db4-0ccf-11f1-a5b7-5e1b9081e705 | four  | 2026-02-18 14:37:50 |
+--------------------------------------+-------+---------------------+
4 rows in set (0.000 sec)

We can see that even if the UUIDs of the last record should have been the first one when sorted, it’s in the right place in the table.

We can verify with the binary value:

MariaDB > select bin_to_uuid(uuid), hex(uuid), name from t;
+--------------------------------------+------------------------------------+-------+
| bin_to_uuid(uuid)                    | hex(uuid)                          | name  |
+--------------------------------------+------------------------------------+-------+
| dccdbd5c-0cce-11f1-a5b7-5e1b9081e705 | A5B75E1B9081E70511F10CCEDCCDBD5C01 | one   |
| df10587d-0cce-11f1-a5b7-5e1b9081e705 | A5B75E1B9081E70511F10CCEDF10587D01 | two   |
| fb487fec-0cce-11f1-a5b7-5e1b9081e705 | A5B75E1B9081E70511F10CCEFB487FEC01 | three |
| 050f7db4-0ccf-11f1-a5b7-5e1b9081e705 | A5B75E1B9081E70511F10CCF050F7DB401 | four  |
+--------------------------------------+------------------------------------+-------+
4 rows in set (0.001 sec)

We can see that the swap flag byte is set to ON for each UUID.

And finally, we can test our CHECK CONSTRAINT for fun:

MariaDB > update t set uuid=uuid_to_bin(uuid_v4()) where name='two';
ERROR 4025 (23000): CONSTRAINT `t.uuid` failed for `test`.`t`

MariaDB > update t set uuid=uuid_to_bin(uuid()) where name='two';
Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Of course, in general, you don’t modify the UUID of a record.

Conclusion

This code adds support for UUID_TO_BIN() in MariaDB and changes its behavior to be more automated when used with UUID_v1.

Something you may also have noticed is that UDFs can also be used in the table’s definition, for example, for default values. This is not the case in MySQL, which is frustrating for DBAs who want to customize their database. This is also a strong point when you create extensions for MariaDB.

The code is on GitHub on a dedicated branch. Let me know what you think. Don’t hesitate to provide your comments.

Once again, thank you, Daniël, for reminding me that MariaDB has a UUID datatype!
The UUID datatype appears to store values incrementally for UUIDv1 and UUIDv7, as expected.

So, what is the point of having uuid_to_bin() and bin_to_uuid() ? I don’t know (yet?). At least the previous plugin functions are working.

And even if this code is not relevant, it illustrates how easy it is to extend MariaDB Server.