MySQL 8 UUID Example

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)


mysql> SELECT UUID() INTO @myuuid;
Query OK, 1 row affected (0.00 sec)


mysql> SELECT @myuuid;
+--------------------------------------+
| @myuuid                              |
+--------------------------------------+
| b0a697d4-e5af-11eb-91bc-14c18c7abe49 |
+--------------------------------------+
1 row in set (0.00 sec)


# UUID_TO_BIN() creates a compact 16 byte format
# and with the second parameter of "1" you can change the 
# segment order so that the timestamp parts are first and
# you retain the insert in PK order behavior that is
# critical for InnoDB performance due to its use of index
# organized tables (AKA clustered PK index)
# See: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@myuuid, 1), 1);
+-----------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@myuuid, 1), 1) |
+-----------------------------------------+
| b0a697d4-e5af-11eb-91bc-14c18c7abe49    |
+-----------------------------------------+
1 row in set (0.00 sec)


# So we can leverage that, along with functions in DEFAULT clauses,
# and CHECK constraints to support UUIDs efficiently and safely as PKs.
# We can also use a generated column to make it easier on clients.
mysql> CREATE TABLE uuid_test (
    ->    pkuuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)),
    ->    name VARCHAR(100),
    ->    uuid_str CHAR(36) GENERATED ALWAYS AS (BIN_TO_UUID(pkuuid, 1)),
    ->    CONSTRAINT pkuuid_valid CHECK (IS_UUID(BIN_TO_UUID(pkuuid, 1))),
    ->    PRIMARY KEY (pkuuid)
    -> );
Query OK, 0 rows affected (0.01 sec)


mysql> INSERT INTO uuid_test (name) VALUES ("matt");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO uuid_test (name) VALUES ("faith");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO uuid_test (name) VALUES ("lily");
Query OK, 1 row affected (0.00 sec)


mysql> SELECT * FROM uuid_test ORDER BY pkuuid;
+------------------------------------+-------+--------------------------------------+
| pkuuid                             | name  | uuid_str                             |
+------------------------------------+-------+--------------------------------------+
| 0x11EC7A64BB61BEC0BC4D53A33A376454 | matt  | bb61bec0-7a64-11ec-bc4d-53a33a376454 |
| 0x11EC7A64C0C45E7CBC4D53A33A376454 | faith | c0c45e7c-7a64-11ec-bc4d-53a33a376454 |
| 0x11EC7A64C3FC5216BC4D53A33A376454 | lily  | c3fc5216-7a64-11ec-bc4d-53a33a376454 |
+------------------------------------+-------+--------------------------------------+
3 rows in set (0.00 sec)