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