Skip to content

Instantly share code, notes, and snippets.

@mattlord
Last active March 4, 2024 23:51
Show Gist options
  • Save mattlord/c41a58ac48e08cbc68e23aed126f1869 to your computer and use it in GitHub Desktop.
Save mattlord/c41a58ac48e08cbc68e23aed126f1869 to your computer and use it in GitHub Desktop.
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment