ULID (26 characters in Crockford's base32) conversion for MySQL function

ulid_converter.sql

# Define ULID_DECODE and ULID_ENCODE which convert a ulid string to a binary and vice versa.

delimiter //
DROP FUNCTION IF EXISTS ULID_DECODE//
CREATE FUNCTION ULID_DECODE (s CHAR(26)) RETURNS BINARY(16) DETERMINISTIC
BEGIN
DECLARE s_base32 CHAR(26);
SET s_base32 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(s), 'J', 'I'), 'K', 'J'), 'M', 'K'), 'N', 'L'), 'P', 'M'), 'Q', 'N'), 'R', 'O'), 'S', 'P'), 'T', 'Q'), 'V', 'R'), 'W', 'S'), 'X', 'T'), 'Y', 'U'), 'Z', 'V');
RETURN UNHEX(CONCAT(LPAD(CONV(SUBSTRING(s_base32, 1, 2), 32, 16), 2, '0'), LPAD(CONV(SUBSTRING(s_base32, 3, 12), 32, 16), 15, '0'), LPAD(CONV(SUBSTRING(s_base32, 15, 12), 32, 16), 15, '0')));
END//

DROP FUNCTION IF EXISTS ULID_ENCODE//
CREATE FUNCTION ULID_ENCODE (b BINARY(16)) RETURNS CHAR(26) DETERMINISTIC
BEGIN
DECLARE s_hex CHAR(32);
SET s_hex = LPAD(HEX(b), 32, '0');
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(LPAD(CONV(SUBSTRING(s_hex, 1, 2), 16, 32), 2, '0'), LPAD(CONV(SUBSTRING(s_hex, 3, 15), 16, 32), 12, '0'), LPAD(CONV(SUBSTRING(s_hex, 18, 15), 16, 32), 12, '0')), 'V', 'Z'), 'U', 'Y'), 'T', 'X'), 'S', 'W'), 'R', 'V'), 'Q', 'T'), 'P', 'S'), 'O', 'R'), 'N', 'Q'), 'M', 'P'), 'L', 'N'), 'K', 'M'), 'J', 'K'), 'I', 'J');
END//

DROP FUNCTION IF EXISTS ULID_FROM_DATETIME//
CREATE FUNCTION ULID_FROM_DATETIME (t DATETIME) RETURNS CHAR(26) DETERMINISTIC
BEGIN
RETURN ULID_ENCODE(CONCAT(UNHEX(CONV(UNIX_TIMESTAMP(t) * 1000, 10, 16)), binary(10)));
END//

DROP FUNCTION IF EXISTS ULID_TO_DATETIME//
CREATE FUNCTION ULID_TO_DATETIME (s CHAR(26)) RETURNS DATETIME DETERMINISTIC
BEGIN
RETURN FROM_UNIXTIME(CONV(HEX(LEFT(ULID_DECODE(s), 6)), 16, 10) / 1000);
END//
delimiter ;

# Check the hex output should equal to one from other library 
select HEX(ULID_DECODE('01ARZ3NDEKTSV4RRFFQ69G5FAV'));

# Check the all outputs should equal to '1'(true).
select '01ARZ3NDEKTSV4RRFFQ69G5FAV' = ULID_ENCODE(ULID_DECODE('01ARZ3NDEKTSV4RRFFQ69G5FAV'));
select '00000000000000000000000000' = ULID_ENCODE(ULID_DECODE('00000000000000000000000000'));
select '7F000000000000000000000000' = ULID_ENCODE(ULID_DECODE('7F000000000000000000000000'));
select '0V000000000000000000000000' = ULID_ENCODE(ULID_DECODE('0V000000000000000000000000'));
select '000V0000000000000000000000' = ULID_ENCODE(ULID_DECODE('000V0000000000000000000000'));
select '0000V000000000000000000000' = ULID_ENCODE(ULID_DECODE('0000V000000000000000000000'));
select '00000V00000000000000000000' = ULID_ENCODE(ULID_DECODE('00000V00000000000000000000'));
select '000000V0000000000000000000' = ULID_ENCODE(ULID_DECODE('000000V0000000000000000000'));
select '0000000V000000000000000000' = ULID_ENCODE(ULID_DECODE('0000000V000000000000000000'));
select '00000000V00000000000000000' = ULID_ENCODE(ULID_DECODE('00000000V00000000000000000'));
select '000000000V0000000000000000' = ULID_ENCODE(ULID_DECODE('000000000V0000000000000000'));
select '0000000000V000000000000000' = ULID_ENCODE(ULID_DECODE('0000000000V000000000000000'));
select '00000000000V00000000000000' = ULID_ENCODE(ULID_DECODE('00000000000V00000000000000'));
select '000000000000V0000000000000' = ULID_ENCODE(ULID_DECODE('000000000000V0000000000000'));
select '0000000000000V000000000000' = ULID_ENCODE(ULID_DECODE('0000000000000V000000000000'));
select '00000000000000V00000000000' = ULID_ENCODE(ULID_DECODE('00000000000000V00000000000'));
select '000000000000000V0000000000' = ULID_ENCODE(ULID_DECODE('000000000000000V0000000000'));
select '0000000000000000V000000000' = ULID_ENCODE(ULID_DECODE('0000000000000000V000000000'));
select '00000000000000000V00000000' = ULID_ENCODE(ULID_DECODE('00000000000000000V00000000'));
select '000000000000000000V0000000' = ULID_ENCODE(ULID_DECODE('000000000000000000V0000000'));
select '0000000000000000000V000000' = ULID_ENCODE(ULID_DECODE('0000000000000000000V000000'));
select '00000000000000000000V00000' = ULID_ENCODE(ULID_DECODE('00000000000000000000V00000'));
select '000000000000000000000V0000' = ULID_ENCODE(ULID_DECODE('000000000000000000000V0000'));
select '0000000000000000000000V000' = ULID_ENCODE(ULID_DECODE('0000000000000000000000V000'));
select '00000000000000000000000V00' = ULID_ENCODE(ULID_DECODE('00000000000000000000000V00'));
select '000000000000000000000000V0' = ULID_ENCODE(ULID_DECODE('000000000000000000000000V0'));
select '0000000000000000000000000V' = ULID_ENCODE(ULID_DECODE('0000000000000000000000000V'));
select '0123456789JKMNPQRSTVWXYZ01' = ULID_ENCODE(ULID_DECODE('0123456789JKMNPQRSTVWXYZ01'));
select '00123456789JKMNPQRSTVWXYZ0' = ULID_ENCODE(ULID_DECODE('00123456789JKMNPQRSTVWXYZ0'));
select '000000000000JKMNPQRSTVWXYZ' = ULID_ENCODE(ULID_DECODE('000000000000JKMNPQRSTVWXYZ'));
select '00JKMNPQRSTVWXYZ0000000000' = ULID_ENCODE(ULID_DECODE('00JKMNPQRSTVWXYZ0000000000'));
select '01JKMNPQRSTVWXYZ0000000000' = ULID_ENCODE(ULID_DECODE('01JKMNPQRSTVWXYZ0000000000'));
select '00000JKMNPQRSTVWXYZ0000000' = ULID_ENCODE(ULID_DECODE('00000JKMNPQRSTVWXYZ0000000'));
select '000000000JKMNPQRSTVWXYZ000' = ULID_ENCODE(ULID_DECODE('000000000JKMNPQRSTVWXYZ000'));
select '01234567890123456789012345' = ULID_ENCODE(ULID_DECODE('01234567890123456789012345'));
select '0JKMNPQRSTVWXYZJKMNPQRSTVW' = ULID_ENCODE(ULID_DECODE('0JKMNPQRSTVWXYZJKMNPQRSTVW'));
select '0MNPQRSTVWXYZMNPQRSTVWXYZ0' = ULID_ENCODE(ULID_DECODE('0MNPQRSTVWXYZMNPQRSTVWXYZ0'));
select '0ZYXWVTSRQPNMZYXWVTSRQPNM0' = ULID_ENCODE(ULID_DECODE('0ZYXWVTSRQPNMZYXWVTSRQPNM0'));
select '0WVTSRQPNMKJZYXWVTSRQPNMKJ' = ULID_ENCODE(ULID_DECODE('0WVTSRQPNMKJZYXWVTSRQPNMKJ'));
select '0000004JFGTYNCK4CFM2C8EXXS' = ULID_ENCODE(ULID_DECODE('0000004JFGTYNCK4CFM2C8EXXS'));
select '0000004JFGT6RQ0EYX5PEWWJHD' = ULID_ENCODE(ULID_DECODE('0000004JFGT6RQ0EYX5PEWWJHD'));
select '0000004JFGGMVJGZD53ZCSEV76' = ULID_ENCODE(ULID_DECODE('0000004JFGGMVJGZD53ZCSEV76'));
select '0000004JFGGMVJGZD53ZCSEV7B' = ULID_ENCODE(ULID_DECODE('0000004JFGGMVJGZD53ZCSEV7B'));
select '000XA16S41ACTAV9WEVGEMMVR8' = ULID_ENCODE(ULID_DECODE('000XA16S41ACTAV9WEVGEMMVR8'));
                                                              
select '2019-01-01 00:00:00' = ULID_TO_DATETIME(ULID_FROM_DATETIME('2019-01-01 00:00:00'));
select '2020-02-02 02:02:02' = ULID_TO_DATETIME(ULID_FROM_DATETIME('2020-02-02 02:02:02'));

However I now think I have exposed a bug in the encoding:
select ulid_from_datetime(cast('2024-12-20 12:37:10.191' as datetime(3))); -- gives 01JFGVC8HF0000000000000000
(NOTE ignore the last 16 0's as these are where the random string would go; you can replace the random_bytes(10) function with unhex('00000000000000000000') to prove that.)

However if we now attempt to extract the datetime from the above generated ulid:
select ulid_to_datetime('01JFGVC8HF0000000000000000'); -- gives 2024-12-20 12:37:10.190, which is 1ms less than the input!

The error is consistent if you try different timestamps as input, it's always 1ms out.

This bug would be hidden because the current functions as they are defined in the library remove the milliseconds entirely. I think the encoding step is possibly wrong but I've not had time to debug it yet.

Hence the included tests now fail when milliseconds are included:
select '2019-01-01 00:00:00.123' = app_ULID_TO_DATETIME(app_ULID_FROM_DATETIME('2019-01-01 00:00:00.123')); -- 0
select '2020-02-02 02:02:02.123' = app_ULID_TO_DATETIME(app_ULID_FROM_DATETIME('2020-02-02 02:02:02.123')); -- 0

I've forked this repo, incorporated changes to work with milliseconds, and fixed the bugs I discussed above in my fork, available at
ulid_converter.sql