UUID主键为BINARY(16)NOT NULL的MariaDB表

yqlxgs2m  于 2022-12-04  发布在  其他
关注(0)|答案(1)|浏览(127)

我想使用BINARY UUID作为我的表中的主键,但是使用我自己的自定义函数,该函数根据本文松散地生成优化的UUID:https://mariadb.com/kb/en/guiduuid-performance/
表的结构和两个主要功能如下:

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())),

我收到错误消息“无法在Id的DEFAULT子句中使用函数或运算式'OPTIMISE_UUID_STR()'”
因此,我尝试在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;

上面的代码运行得很好,但问题是我不能将Id列定义为PRIMARY KEY,我想这样做是因为PRIMARY KEY必须为NOT NULL,而设置这意味着我必须预先生成优化的UUID。我不想这样做,因为我希望DB负责生成优化的UUID。
通过查看上面的Trigger定义,您可能已经推断出了这一点,我尝试在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'不能为空”,即使已经设置了一个DEFAULT值。
所以我真正的问题是:是否可以为PRIMARY KEY列生成自定义(优化的UUID)BINARY值?

t2a7ltrp

t2a7ltrp1#

简短回答:是的
道长回答:
PRIMARY KEY几乎可以是任何数据类型,可以创建任何值。

  • 不允许使用TEXTBLOB。甚至不允许使用TINYTEXT
  • VARCHAR(etc)不允许超过一定的大小(取决于版本和CHARACTER SET)。VARCHAR(191)(或更小)在所有组合中工作。普遍存在的VARCHAR(255)在许多情况下工作。

MySQL 8.0有内置的函数可以在二进制和字符串UUID之间进行转换。它还提供了如下函数(就像您的函数一样):UUIDs

相关问题