Skip to content

Instantly share code, notes, and snippets.

@tzal
Last active September 8, 2023 07:32
Show Gist options
  • Save tzal/03ee31e54c75ab177f28 to your computer and use it in GitHub Desktop.
Save tzal/03ee31e54c75ab177f28 to your computer and use it in GitHub Desktop.
MySQL: convert BINARY(16) to GUID (UUID)
# MySQL: convert BINARY(16) to GUID (Microsoft-style UUID)
# 0x11223344556677889900AABBCCDDEEFF → '44332211-6655-8877-9900-AABBCCDDEEFF'
# Usage: CREATE TABLE example (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, guid BINARY(16) NOT NULL UNIQUE);
# SELECT id, uuid_from_bin(guid) FROM example;
# Tested on: MySQL 5.6, 5.7, 8.0
CREATE FUNCTION uuid_from_bin(b BINARY(16))
RETURNS char(36) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE h CHAR(32);
SET h=HEX(b);
RETURN CONCAT(SUBSTRING(h,7,2), SUBSTRING(h,5,2), SUBSTRING(h,3,2), SUBSTRING(h,1,2),
'-', SUBSTRING(h,11,2), SUBSTRING(h,9,2),
'-', SUBSTRING(h,15,2), SUBSTRING(h,13,2),
'-', SUBSTRING(h,17,4),
'-', SUBSTRING(h,21,12));
END
@mattmeye
Copy link

error with maria 10.2.12

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment