MariaDB table with UUID primary key as BINARY(16) NOT NULL-CodePudding

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
/*
FROM

00       10        20        30
123456789012345678901234567890123456
====================================
AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE


TO

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


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

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

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'

and

Id` BINARY(16) NOT NULL DEFAULT X'FFFFFFFFFFFFFFFF'

and

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?

CodePudding user response：

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

CodePudding user response：

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.