MySQL中VARCHAR存储的ULID转BINARY(16)的SQL函数报错求助

错误原因 #

你遇到的SQL Error (1406)是因为函数中拼接的十六进制字符串长度超过32位，UNHEX后生成的二进制数据超出了BINARY(16)的16字节容量限制。

原函数错误地将ULID拆分为6+6+6+8四段，每段转十六进制后分别填充到10、10、10、16位，拼接后总长度达46位，UNHEX后得到23字节的二进制数据，远大于BINARY(16)的存储上限。

正确的转换函数 #

ULID的标准结构为10位base36时间戳（48位/6字节） + 16位base36随机数（80位/10字节），总长度26位。转换时需将这两部分分别转成十六进制，拼接后刚好32位（对应16字节）。

由于MySQL的CONV函数无法直接处理16位base36的超大数值（超过64位整数范围），我们可以将16位随机数拆分为两个8位分段处理：

DELIMITER //

CREATE FUNCTION ConvertULIDToBinary(ulid CHAR(26) CHARACTER SET utf8mb3) 
RETURNS BINARY(16)
DETERMINISTIC
BEGIN
    DECLARE hex_str VARCHAR(32);
    
    -- 处理前10位时间戳：转16进制后补全为12位（对应6字节）
    SET hex_str = LPAD(CONV(SUBSTRING(ulid, 1, 10), 36, 16), 12, '0');
    
    -- 处理中间8位随机数：转16进制后补全为10位（对应5字节）
    SET hex_str = CONCAT(hex_str, LPAD(CONV(SUBSTRING(ulid, 11, 8), 36, 16), 10, '0'));
    
    -- 处理最后8位随机数：转16进制后补全为10位（对应5字节）
    SET hex_str = CONCAT(hex_str, LPAD(CONV(SUBSTRING(ulid, 19, 8), 36, 16), 10, '0'));
    
    -- 确保最终十六进制字符串长度为32位，避免溢出
    SET hex_str = LEFT(hex_str, 32);
    
    RETURN UNHEX(hex_str);
END //

DELIMITER ;

验证函数 #

你可以用以下语句验证转换结果：

-- 测试ULID转换
SELECT ConvertULIDToBinary('01ARZ3NDEKTSV4RRFFQ69G5FAV') AS binary_id;

后续操作注意事项 #

1. 批量更新id_bin列时，建议用事务包裹，避免中途出错导致数据不一致：

   START TRANSACTION;
   UPDATE your_table SET id_bin = ConvertULIDToBinary(id);
   -- 其他表的更新语句
   COMMIT;
   

2. 修改主键和外键时，需先删除所有关联的外键约束，修改完成后重新创建外键。
3. 若表数据量较大，建议分批次更新，避免锁表时间过长影响业务。

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