mariadb UUID helper functions
Add to liquibase
a changeset that registers UUID_TO_BIN()
and BIN_TO_UUID()
. These two allow for querying the database with string UUIDs.
DROP FUNCTION IF EXISTS BIN_TO_UUID;
DROP FUNCTION IF EXISTS UUID_TO_BIN;
DELIMITER $$
CREATE FUNCTION BIN_TO_UUID(b BINARY(16))
RETURNS CHAR(36)
DETERMINISTIC
BEGIN
DECLARE hexStr CHAR(32);
SET hexStr = HEX(b);
RETURN LOWER(CONCAT(
SUBSTR(hexStr, 1, 8), '-',
SUBSTR(hexStr, 9, 4), '-',
SUBSTR(hexStr, 13, 4), '-',
SUBSTR(hexStr, 17, 4), '-',
SUBSTR(hexStr, 21)
));
END$$
CREATE FUNCTION UUID_TO_BIN(_uuid CHAR(36))
RETURNS BINARY(16)
DETERMINISTIC
BEGIN
RETURN UNHEX(CONCAT(
SUBSTR(_uuid, 1, 8),
SUBSTR(_uuid, 10, 4),
SUBSTR(_uuid, 15, 4),
SUBSTR(_uuid, 20, 4),
SUBSTR(_uuid, 25)
));
END$$
DELIMITER ;
Example
select id, latitude, longitude, tileIndex, tileIndex3min from acce A where A.uuid in (
UUID_TO_BIN('4d57a8f7-14ec-45d9-b415-7e7dd1dc2885'),
UUID_TO_BIN('c6034fee-d616-4262-9433-2a1f2db621d2')
)