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'));

really thank you for this.

@kenji4569

This is super useful. However, when I use any of the ULID libraries i get a different kind of ULID that what this produces.

For E.g:- For the same date time.

01G2V2W6DTAY06GA54RXT1TRN6 --> using https://github.com/f4b6a3/ulid-creator
01G2V2RC7S64R0000000000000 --> using this function.

I don't see trailing 0's in any of the other ULID generators.

Sounds cool at first glance, but unfortunately does not produce really unique ids whenever a date is passed.

mysql> SELECT a, b, a = b AS same FROM (SELECT ULID_FROM_DATETIME('2023-02-17 00:00:00') AS a, ULID_FROM_DATETIME('2023-02-17 00:00:00') AS b) AS derived;
+----------------------------+----------------------------+------+
| a                          | b                          | same |
+----------------------------+----------------------------+------+
| 01GSE79FC064R0000000000000 | 01GSE79FC064R0000000000000 |    1 |
+----------------------------+----------------------------+------+
1 row in set (0,00 sec)

Change this binary(10):

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//

to this RANDOM_BYTES(10):

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)), RANDOM_BYTES(10)));
END//

And here's the missing ULID() which uses current timestamp + milliseconds. (also incorporates random_bytes() fix)

delimiter //
DROP FUNCTION IF EXISTS ULID//
CREATE FUNCTION ULID () RETURNS CHAR(26) DETERMINISTIC
BEGIN
RETURN ULID_ENCODE(CONCAT(UNHEX(CONV((UNIX_TIMESTAMP() + (NOW(4)+0 - NOW()+0)) * 1000, 10, 16)), RANDOM_BYTES(10)));
END//

It does encode milliseconds, but you can't decode them with these functions. ULID_TO_DATETIME seems to strip away the fractional seconds, but doing your own conversion w/o functions does seem to work.

select
DATE_FORMAT(FROM_UNIXTIME(CONV(HEX(LEFT(ULID_DECODE(ULID()), 6)), 16, 10) / 1000),  '%Y-%m-%d %H:%i:%s.%f')

I found that you need to redefine ulid_to_datetime to tell the datetime type how much precision you want, this will now show you the milliseconds stored:
CREATE FUNCTION ulid_to_datetime (s CHAR(26)) RETURNS DATETIME(6) DETERMINISTIC BEGIN RETURN FROM_UNIXTIME(CONV(HEX(LEFT(ulid_decode(s), 6)), 16, 10) / 1000); END;

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