Skip to content

Instantly share code, notes, and snippets.

@tzal
tzal / uuid_from_bin.sql
Last active September 8, 2023 07:32
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
@tzal
tzal / uuid_to_bin.sql
Last active September 8, 2023 07:32
MySQL: convert GUID (UUID) to BINARY(16)
# MySQL: convert GUID (Microsoft-style UUID) to BINARY(16)
# '11223344-5566-7788-9900-AABBCCDDEEFF' → 0x44332211665588779900AABBCCDDEEFF
# Usage: CREATE TABLE example (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, guid BINARY(16) NOT NULL UNIQUE);
# INSERT INTO example (guid) VALUES (uuid_to_bin(UUID()));
# Tested on: MySQL 5.6, 5.7, 8.0
CREATE FUNCTION uuid_to_bin(s CHAR(36))
RETURNS binary(16)
DETERMINISTIC
RETURN UNHEX(CONCAT(