Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I want to use BINARY UUIDs as my primary key in my tables, but using my own custom functions that generates optimised UUIDs loosely based on this article: https://mariadb.com/kb/en/guiduuid-performance/

The table structure and two main functions of interest here are:

CREATE TABLE `Test` (
  `Id` BINARY(16),
  `Data` VARCHAR(100)
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
CREATE DEFINER = 'user'@'%' FUNCTION `OPTIMISE_UUID_STR`(`_uuid` VARCHAR(36))
    RETURNS VARCHAR(32) CHARACTER SET utf8mb4
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
00       10        20        30
123456789012345678901234567890123456
====================================
AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE
00       10        20        30
12345678901234567890123456789012
================================
CCCCBBBBAAAAAAAADDDDEEEEEEEEEEEE
RETURN UCASE(CONCAT(
        SUBSTR(_uuid, 15, 4), /* Time nodes reversed */
        SUBSTR(_uuid, 10, 4),
        SUBSTR(_uuid,  1, 8),
        SUBSTR(_uuid, 20, 4),  /* MAC nodes last */
        SUBSTR(_uuid, 25, 12)));
CREATE DEFINER = 'user'@'%' FUNCTION `CONVERT_OPTIMISED_UUID_STR_TO_BIN`(`_hexstr` BINARY(32))
    RETURNS BINARY(16)
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
Convert optimised UUID from string hex representation to binary. If the UUID is not optimised, it makes no sense to convert
RETURN UNHEX(_hexstr);
I cannot use my custom functions in column definition as shown below
CREATE TABLE `Test` (
  `Id` BINARY(16) NOT NULL DEFAULT CONVERT_OPTIMISED_UUID_STR_TO_BIN(OPTIMISE_UUID_STR(UUID())),
I get the error "Function or expression 'OPTIMISE_UUID_STR()' cannot be used in the DEFAULT clause of Id"
So I tried using the same in Triggers:
CREATE DEFINER = 'user'@'%' TRIGGER `Test_before_ins_tr1` BEFORE INSERT ON `Test`
  FOR EACH ROW
BEGIN
  IF (new.Id IS NULL) OR (new.Id = X'0000000000000000') OR (new.Id = X'FFFFFFFFFFFFFFFF') THEN
    SET new.Id = CONVERT_OPTIMISED_UUID_STR_TO_BIN(OPTIMISE_UUID_STR(UUID()));
  END IF;   
The above works pretty good, but the issue is that I cannot define the Id column as PRIMARY KEY, which I want to because PRIMARY KEYs have to be NOT NULL, and setting this means I have to pre-generate optimised UUIDs. I do not want to do this as I would like the DB to take care of generating the optimised UUIDs.
As you might have inferred looking at the above Trigger definition, I tried setting a default value on the Id column, such as:
Id` BINARY(16) NOT NULL DEFAULT X'0000000000000000'
Id` BINARY(16) NOT NULL DEFAULT X'FFFFFFFFFFFFFFFF'
Id` BINARY(16) NOT NULL DEFAULT '0' /* I tried setting 0, but always seem to revert to '0' */
and this default value would be picked up by the trigger and a correct optimised UUID assigned. But that also does not work as the DB complains "Column 'Id' cannot be null" even though a DEFAULT value has been set.
So my actual question is: Can I generate a custom (optimised UUID) BINARY value for a PRIMARY KEY column?
                The uuid datatype in MariaDB-10.7+ is already optimized for storage/searching for the UUIDv1 type. Also see discussion on MDEV-29959 for other types.
– danblack
                Dec 4, 2022 at 2:07
Yes, it's doable even without triggers and/or stored functions:
MariaDB from version 10.6:
Use function SYS_GUID() which returns same result as UUID() but without - characters. The result of this function can be directly converted to a 16-byte value with UNHEX() function.
Example:
CREATE TABLE test (a BINARY(16) NOT NULL DEFAULT UNHEX(SYS_GUID()) PRIMARY KEY);
INSERT INTO test VALUES (DEFAULT);
INSERT INTO test VALUES (DEFAULT);
SELECT HEX(a) FROM test;
+----------------------------------+
| HEX(a)                           |
+----------------------------------+
| 53EE84FB733911EDA238D83BBF89F2E2 |
| 61AC0286733911EDA238D83BBF89F2E2 |
+----------------------------------+
MariaDB from version 10.7 (as mentioned in danielblack's comment):
Use UUID datatype which stores UUID() (and SYS_GUID()) values as 16 byte:
CREATE TABLE test (a UUID not NULL default UUID() PRIMARY KEY);
INSERT INTO test VALUES (DEFAULT);
INSERT INTO test VALUES (DEFAULT);
SELECT a FROM test;
+--------------------------------------+
| a                                    |
+--------------------------------------+
| 6c42e367-733b-11ed-a238-d83bbf89f2e2 |
| 6cbc0418-733b-11ed-a238-d83bbf89f2e2 |
+--------------------------------------+
Addendum: If you are using a version < 10.6 and your requirements match the following limitations, you could also use UUID_SHORT() function, which generates a 64-bit identifier.
Short answer: Yes
Long answer:
The PRIMARY KEY can be almost any datatype with whatever values you can create.
TEXT or BLOB are not allowed.  Not even TINYTEXT.
VARCHAR (etc) are not allowed beyond some size (depends on version and CHARACTER SET).  VARCHAR(191) (or smaller) works in all combinations.  The ubiquitous VARCHAR(255) works in many situations.
MySQL 8.0 has builtin functions for converting between binary and string UUIDs.  This also provides functions (like yours) for such: UUIDs
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.