MySQL手动递增BINARY类型UUID主键生成测试数据报错解决

问题背景 #

需要为experimental_held_orders表生成测试数据，要求BINARY(16)类型的OrderId为连续递增的十六进制值（如0x00000000000000000000000000000000、0x00000000000000000000000000000001），且不使用AUTO_INCREMENT。初始存储过程执行时触发[22001][1292]数据截断错误，修改后的代码生成的OrderId不符合预期。

问题分析 #

1. 初始代码错误原因：直接对BINARY(16)类型变量执行+1操作时，MySQL会将二进制值视为字符串处理，数值溢出时触发数据截断错误。
2. 修改后代码问题：使用CONV函数转换时，BIGINT仅支持64位，无法容纳BINARY(16)对应的128位数值，会丢失高位数据；同时CONV返回的字符串未补全前导零，导致生成的二进制值格式不符合要求。

解决方法 #

使用DECIMAL(38,0)类型变量跟踪递增数值（支持128位范围），通过LPAD补全十六进制字符串长度，再用UNHEX转换为BINARY(16)类型，确保生成连续且格式正确的OrderId。

正确的存储过程代码 #

DROP PROCEDURE IF EXISTS doiterate;
CREATE PROCEDURE doiterate()
BEGIN
    DECLARE v_max INT UNSIGNED DEFAULT 10;
    DECLARE v_counter INT UNSIGNED DEFAULT 0;
    -- 用DECIMAL存储递增数值，支持128位范围
    DECLARE orderId_dec DECIMAL(38, 0) DEFAULT 0;
    DECLARE heldUntil DATETIME DEFAULT '2023-03-31 08:36:35';
    DECLARE orderId_hex VARCHAR(32);

    WHILE v_counter <= v_max DO
        SET v_counter = v_counter + 1;
        -- 将十进制数转为32位十六进制字符串（补前导零），再转成BINARY(16)
        SET orderId_hex = LPAD(CONV(orderId_dec, 10, 16), 32, '0');
        INSERT INTO experimental_held_orders (OrderId, HeldUntilUtc)
        VALUES (UNHEX(orderId_hex), heldUntil);

        SET orderId_dec = orderId_dec + 1;
        SET heldUntil = heldUntil + INTERVAL 1 MINUTE;
    END WHILE;
END;
CALL doiterate();

代码说明 #

• orderId_dec：用DECIMAL(38,0)存储递增数值，避免64位BIGINT的范围限制，支持最大到10^38-1，完全覆盖BINARY(16)的128位数值范围。
• LPAD(CONV(orderId_dec, 10, 16), 32, '0')：将十进制数转为十六进制字符串后，补全前导零到32个字符，确保转换为BINARY(16)时长度正确。
• UNHEX(orderId_hex)：将32位十六进制字符串转换为BINARY(16)类型，符合表字段的类型要求。

验证方法 #

执行存储过程后，查询验证OrderId是否连续：

SELECT HEX(OrderId) AS OrderId_Hex, HeldUntilUtc FROM experimental_held_orders;

返回结果中OrderId_Hex应显示为连续的32位十六进制字符串，如00000000000000000000000000000000、00000000000000000000000000000001等。

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