如何为MariaDB主键列生成自定义优化二进制UUID？

可以实现自定义优化的二进制UUID作为主键，核心是调整表结构和触发器逻辑，确保满足主键的非空约束，同时由数据库自动生成UUID。

步骤1：修正表结构 #

将Id列设为NOT NULL PRIMARY KEY，并设置一个占位默认值（如全0的二进制值），触发器会在插入前替换这个值：

CREATE TABLE `Test` (
  `Id` BINARY(16) NOT NULL PRIMARY KEY DEFAULT X'0000000000000000',
  `Data` VARCHAR(100)
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

步骤2：保留自定义UUID优化函数 #

你的两个自定义函数可以继续使用，无需修改：

CREATE DEFINER = 'user'@'%' FUNCTION `OPTIMISE_UUID_STR`(`_uuid` VARCHAR(36))
    RETURNS VARCHAR(32) CHARACTER SET utf8mb4
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
BEGIN
/*
将标准UUID格式：
AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE
转换为优化后的无分隔符格式：
CCCCBBBBAAAAAAAADDDDEEEEEEEEEEEE
*/
RETURN UCASE(CONCAT(
        SUBSTR(_uuid, 15, 4),
        SUBSTR(_uuid, 10, 4),
        SUBSTR(_uuid,  1, 8),
        SUBSTR(_uuid, 20, 4),
        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
BEGIN
/*
将优化后的UUID字符串转换为二进制格式
*/
RETURN UNHEX(_hexstr);
END;

步骤3：调整触发器逻辑 #

修改触发器，确保当插入时Id为默认的全0值或显式传入NULL时，自动生成优化后的UUID：

CREATE DEFINER = 'user'@'%' TRIGGER `Test_before_ins_tr1` BEFORE INSERT ON `Test`
  FOR EACH ROW
BEGIN
  -- 替换默认全0值或NULL为优化后的UUID
  IF new.Id IS NULL OR new.Id = X'0000000000000000' THEN
    SET new.Id = CONVERT_OPTIMISED_UUID_STR_TO_BIN(OPTIMISE_UUID_STR(UUID()));
  END IF;   
END;

验证方法 #

插入数据时无需指定Id，数据库会自动生成优化后的二进制UUID：

INSERT INTO Test(Data) VALUES('测试数据');
SELECT HEX(Id) AS Optimised_UUID, Data FROM Test;

查询结果会显示类似CCCCBBBBAAAAAAAADDDDEEEEEEEEEEEE格式的优化UUID。

关键说明 #

• 主键必须设置NOT NULL，这是InnoDB主键的强制约束；设置占位默认值是为了避免插入时触发非空错误，触发器会在插入前替换这个值。
• 自定义函数的逻辑是基于UUID的时间部分重排，目的是提升InnoDB聚簇索引的插入性能，避免随机IO问题。

内容的提问来源于stack exchange，提问作者Miguel Revera