我想使用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值?
1条答案
按热度按时间t2a7ltrp1#
简短回答:是的
道长回答:
PRIMARY KEY
几乎可以是任何数据类型,可以创建任何值。TEXT
或BLOB
。甚至不允许使用TINYTEXT
。VARCHAR
(etc)不允许超过一定的大小(取决于版本和CHARACTER SET
)。VARCHAR(191)
(或更小)在所有组合中工作。普遍存在的VARCHAR(255)
在许多情况下工作。MySQL 8.0有内置的函数可以在二进制和字符串UUID之间进行转换。它还提供了如下函数(就像您的函数一样):UUIDs