Menu

MySQL UUID_TO_BIN() Function

Updated on

MySQL UUID_TO_BIN() converts a string UUID to a 16-byte binary value (VARBINARY(16)). It is commonly used to store UUIDs in BINARY(16) columns instead of keeping their 36-character text form.

Syntax

UUID_TO_BIN(string_uuid [, swap_flag])
  • string_uuid: A UUID string accepted by MySQL.
  • swap_flag: Optional integer, 0 or 1; the default is 0.

The function returns NULL when string_uuid is NULL. Invalid UUID strings or an invalid flag cause an error.

With swap_flag = 0, MySQL keeps the UUID fields in their original order. With swap_flag = 1, MySQL swaps the first (time-low) and third (time-high-and-version) groups. This rearrangement is intended for UUID version 1 values, such as those returned by UUID(), and may improve index locality. It provides no benefit for other UUID versions.

Example: convert and restore a UUIDv1

Use the same swap flag when converting back with BIN_TO_UUID():

SET @uuid = '6ccd780c-baba-1026-9564-5b8c656024db';
SET @binary_uuid = UUID_TO_BIN(@uuid, 1);

SELECT HEX(@binary_uuid) AS stored_bytes,
       BIN_TO_UUID(@binary_uuid, 1) AS restored_uuid;

The restored_uuid value is 6ccd780c-baba-1026-9564-5b8c656024db. If you encode with flag 1, decoding with flag 0 does not restore the original UUID; keep the flag consistent wherever the value is written and read.

Store UUIDs in a binary column

CREATE TABLE events (
    event_id BINARY(16) PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL
);

INSERT INTO events (event_id, event_name)
VALUES (UUID_TO_BIN(UUID(), 1), 'account_created');

SELECT BIN_TO_UUID(event_id, 1) AS event_id, event_name
FROM events;

The swap_flag = 1 optimization is for version 1 UUIDs only. Do not use it for UUIDv4 values or for values from another UUID version with a different layout.

See MySQL’s official UUID_TO_BIN() documentation for the complete argument and conversion rules.