UUID主键为二进制(16) NULL的MariaDB表

Question

我希望在我的表中使用二进制UUID作为我的主键，但是使用我自己的自定义函数，它松散地基于本文：https://mariadb.com/kb/en/guiduuid-performance/生成优化的UUID。

这里的表结构和两个主要功能是：

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;

不能在列定义中使用自定义函数，如下所示

CREATE TABLE `Test` (
  `Id` BINARY(16) NOT NULL DEFAULT CONVERT_OPTIMISED_UUID_STR_TO_BIN(OPTIMISE_UUID_STR(UUID())),

我得到错误“函数或表达式'OPTIMISE_UUID_STR()‘不能在Id的默认子句中使用”

所以我尝试在触发器中使用相同的方法：

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;

上面的操作非常好，但问题是我不能将Id列定义为主键，因为主键必须不是NULL，而设置这意味着我必须预先生成优化的UUID。我不想这样做，因为我希望DB负责生成优化的UUID。

从上面的触发器定义可以推断，我尝试在Id列上设置一个默认值，如：

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

该默认值将由触发器和分配的正确优化的UUID获取。但这也不起作用，因为DB抱怨说，即使设置了默认值，“列'Id‘也不能为空”。

因此，我的实际问题是:我能否为主键列生成自定义(优化的UUID)二进制值？

Answer 1

简短回答:是

较长的答覆：

PRIMARY KEY几乎可以是任何数据类型，有您可以创建的任何值。

• 不允许使用TEXT或BLOB。甚至连TINYTEXT都没有。
• VARCHAR (etc)不允许超过某种大小(取决于版本和CHARACTER SET)。VARCHAR(191) (或更小的)可以在所有组合中工作。无处不在的VARCHAR(255)在许多情况下都能工作。

MySQL 8.0有内置的函数，用于在二进制和字符串UUID之间进行转换。这还为这样的http://mysql.rjweb.org/doc.php/uuid提供了函数(如您的)

Answer 2

是的，即使没有触发器和/或存储函数，它也是可行的：

MariaDB来自10.6版

使用函数SYS_GUID()，它返回与UUID()相同的结果，但不包含-字符。这个函数的结果可以用UNHEX()函数直接转换成一个16字节的值。

示例：

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 |
+----------------------------------+

来自10.7版本的MariaDB (如danielblack的评论中提到的)：

使用UUID数据类型，它将UUID() (和SYS_GUID())值存储为16个字节：

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 |
+--------------------------------------+

增编:如果您使用的版本< 10.6，并且您的需求与下面的局限性匹配，您也可以使用UUID_SHORT()函数，它生成64位标识符。