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.

/* This script is based on https://gist.github.com/kenji4569/47ce8bbd6bef7b85ba1f97e018f34cf3/forks
# Changes made by mark2016:
-- Original script didn't incorporate milliseconds into the timestamp, now they are included.
-- Original script had a precision rounding error in function ULID_TO_DATETIME where the precision
--   of the calculation resulted in milliseconds being lost, this is corrected.
-- Functions are renamed with "app_" suffix to better differentiate them from native functions
-- These functions are renamed:
--   ULID_FROM_DATETIME => app_ulid_using_datetime
--   ULID_TO_DATETIME   => app_ulid_extract_datetime
-- Added a function to generate a ulid thanks to @markkimsal on github
-- Formatting and indenting changes
-- Tests updated to included milliseconds
-- Tested on 11.4.2-MariaDB
*/

#-------------------------------------------------------------------------------
#- Create a ulid including timestamps, note the function is not deterministic due to the random_bytes

delimiter //

drop function if exists app_ulid//
create function app_ulid () returns char(26)
begin
    return app_ulid_encode(concat(unhex(conv((unix_timestamp(now(3))) * 1000, 10, 16)), random_bytes(10)));
end//

#-------------------------------------------------------------------------------
#- Convert an encoded (human-readable) ulid to a binary ulid
#- Note ulids should be stored in the database in the binary form

drop function if exists app_ulid_decode//
create function app_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//

#-------------------------------------------------------------------------------
#- Convert a binary ulid to an encoded ulid

drop function if exists app_ulid_encode//
create function app_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//

#-------------------------------------------------------------------------------
#- Create ULID given a datetime
#- Incorporates milliseconds

drop function if exists app_ulid_using_datetime//
create function app_ulid_using_datetime (t datetime(6) ) returns char(26)
begin
    return app_ulid_encode(concat(unhex(conv(unix_timestamp( t ) * 1000, 10, 16)), random_bytes(10) ));
end//

#-------------------------------------------------------------------------------
#- Extract the datetime that was used to create the given ulid
#- This version incorporates milliseconds

drop function if exists app_ulid_extract_datetime//
create function app_ulid_extract_datetime (s char(26)) returns datetime(6) deterministic
begin
    -- We need to cast the string output of conv to a decimal of appropriate precision to avoid precision errors 
    declare d_unixvalue decimal(30,10);
    set d_unixvalue = cast(conv(hex(left(app_ulid_decode(s), 6)), 16, 10) as decimal(30,10)) / 1000.0; 
    return from_unixtime(d_unixvalue);
end//

delimiter ;

#-------------------------------------------------------------------------------
#- Tests

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