UUID()¶
Description¶
UUID() and UUID_V7() generate native UUIDv7 values, as defined in RFC 9562. The native value occupies 16 bytes; clients display 32 hexadecimal digits and four hyphens: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee (36 characters). Use UUID_V1() when a version 1 UUID is required.
UUIDv7 places the Unix timestamp in milliseconds first, followed by version, variant and random bits. This layout provides time ordering at millisecond granularity; it does not guarantee generation order across devices or within the same millisecond. A UUID is an identifier, not a secret or access token.
See UUID Type for column storage and comparisons.
Common syntax¶
UUID()
UUID_V7()
UUID_V1()
UUID(datetime)
UUID_V7(datetime)
UUID(datetime) and UUID_V7(datetime) return a deterministic lower boundary for the supplied time: timestamp bits are set, version and variant bits are retained, and the remaining bits are zero. They use the session time zone and millisecond precision. Repeated calls for the same instant return the same boundary; do not use this overload to generate unique row identifiers. A NULL datetime returns NULL; a timestamp outside UUIDv7’s representable range is rejected.
MySQL compatibility¶
MatrixOne keeps UUID() as native UUIDv7. MySQL returns a version 1 UUID as a character string. Consequently:
CREATE TABLE ... AS SELECT UUID()derives a UUID column in MatrixOne.Native UUID comparisons parse the text operand as a UUID. Arbitrary text such as
'fallback'is invalid and raises an error. UseCAST(UUID() AS CHAR)for ordinary text predicates.Mixed UUID/CHAR/VARCHAR/TEXT value expressions (
CASE,IF,COALESCE,UNION,LEAST,GREATEST) use a character result wide enough for the complete UUID. UUID/NULL-only expressions retain the native type. This does not imply full MySQL collation compatibility.
Examples¶
-- 7
SELECT UUID_EXTRACT_VERSION(UUID());
-- 1
SELECT UUID_EXTRACT_VERSION(UUID_V1());
-- 36
SELECT LENGTH(CAST(UUID() AS CHAR));
-- 0
SELECT CAST(UUID() AS CHAR) IN ('fallback');
SELECT UUID(CAST('2026-01-01 00:00:00' AS DATETIME)) =
-- 1
UUID(CAST('2026-01-01 00:00:00' AS DATETIME));
-- complete UUID, not truncated
SELECT 'fallback' AS value UNION ALL
SELECT CAST('018f0000-0000-7000-8000-000000000001' AS UUID);
SET time_zone = '+00:00';
-- 019b7ca9-8f2e-7000-8000-000000000000
SELECT CAST(UUID(CAST('2026-01-02 03:04:05.678' AS DATETIME(3))) AS CHAR);
-- 018F0000000070008000000000000001
SELECT HEX(UUID_TO_BIN('018f0000-0000-7000-8000-000000000001', NULL));
-- 70000000018F00008000000000000001
SELECT HEX(UUID_TO_BIN('018f0000-0000-7000-8000-000000000001', 1));
-- 018f0000-0000-7000-8000-000000000001
SELECT BIN_TO_UUID(UUID_TO_BIN('018f0000-0000-7000-8000-000000000001', 1), 1);
Each no-argument call generates a fresh value; example results vary. The existing UUID(INTERVAL ...) overload also generates a fresh UUID with its evaluation time shifted by the interval; it is different from a deterministic datetime boundary.
UUID_SHORT()¶
UUID_SHORT() takes no arguments and returns a BIGINT UNSIGNED identifier. It uses MatrixOne’s existing HAKeeper allocator rather than MySQL’s server-ID/startup-time bit layout. Successful calls across CNs and accounts are unique within the same preserved cluster allocator history. Values are not native UUIDs, globally ordered, contiguous, or secret.
Unused reserved IDs and IDs consumed by rolled-back or failed statements are not reused. CN restart preserves uniqueness through durable HAKeeper reservations. Independent clusters, cloned allocator state, or restoration to an earlier allocator snapshot can repeat values; use an external namespace when combining these histories.
An unavailable allocator, cancellation, invalid reservation or exhausted uint64 range causes an error; there is no local or random fallback. Allocation retries are bounded by the query’s earlier deadline or 30 seconds per vector evaluation. Upgrade CNs and all HAKeeper replicas before relying on this function and exhaustion protection. Do not reset its allocator state during rollback or reinstallation.
SELECT UUID_SHORT(), UUID_SHORT();
IS_UUID()¶
Description¶
IS_UUID(str) tests whether a string is a valid UUID. Returns 1 if the string is a valid UUID, 0 otherwise, and NULL if the argument is NULL.
The following UUID formats are recognized:
Standard dashed format:
6ccd780c-baba-1026-9564-5b8c656024dbNo-dash format (32 hex digits):
6ccd780cbaba102695645b8c656024dbBraced format:
{6ccd780c-baba-1026-9564-5b8c656024db}Uppercase format:
6CCD780C-BABA-1026-9564-5B8C656024DB
The function validates the length and hex character composition. Strings with extra whitespace, invalid characters, or incorrect byte lengths return 0.
Syntax¶
> IS_UUID(str)
Arguments¶
Arguments |
Description |
|---|---|
str |
Required. The string to test. |
Examples¶
DROP DATABASE IF EXISTS uuid_test;
CREATE DATABASE uuid_test;
USE uuid_test;
-- Standard UUID format
SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024db');
-- No-dash format
SELECT IS_UUID('6ccd780cbaba102695645b8c656024db');
-- Braced format
SELECT IS_UUID('{6ccd780c-baba-1026-9564-5b8c656024db}');
-- Uppercase variant
SELECT IS_UUID('6CCD780C-BABA-1026-9564-5B8C656024DB');
-- URN format (not recognized as valid)
SELECT IS_UUID('urn:uuid:6ccd780c-baba-1026-9564-5b8c656024db');
-- Invalid: too short
SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024d');
-- Invalid: non-hex character
SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024dz');
-- Invalid: wrong byte count
SELECT IS_UUID('6ccd780c-baba-1026-95645b8c656024db');
-- Empty string
SELECT IS_UUID('');
-- Non-UUID string
SELECT IS_UUID('not-a-uuid');
-- NULL argument
SELECT IS_UUID(NULL);
DROP DATABASE uuid_test;
UUID_TO_BIN()¶
Description¶
UUID_TO_BIN(str [, swap_flag]) converts a UUID string to a 16-byte binary representation. The optional swap_flag controls byte ordering:
swap_flag= 0 (default): Preserves the original byte order as in the UUID string.swap_flag= 1: Moves the time-high field before time-mid and time-low. For version 1 UUIDs, placing the most significant time bits first can improve index locality. UUIDv7 already places its timestamp first: use 0 for v7; swapping does not improve its time ordering.
The swap_flag can be a numeric value or a string that evaluates to a number. Zero disables swapping; any nonzero value, including fractional and negative values, enables it. String flags use their decimal numeric prefix ('1tail' enables swapping); a string with no numeric prefix ('abc') disables swapping. A NULL flag also disables swapping. A NULL str returns NULL; an invalid non-NULL UUID still raises an error even when the flag is NULL.
Syntax¶
> UUID_TO_BIN(str [, swap_flag])
Arguments¶
Arguments |
Description |
|---|---|
str |
Required. A valid UUID string in dashed, no-dash, braced, or uppercase format. |
swap_flag |
Optional. 0 to preserve byte order (default); any nonzero value moves time-high before time-mid and time-low (intended for v1). |
Examples¶
DROP DATABASE IF EXISTS uuid_test;
CREATE DATABASE uuid_test;
USE uuid_test;
-- Default byte order
SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));
-- No-dash UUID
SELECT HEX(UUID_TO_BIN('6ccd780cbaba102695645b8c656024db'));
-- Braced UUID
SELECT HEX(UUID_TO_BIN('{6ccd780c-baba-1026-9564-5b8c656024db}'));
-- Uppercase UUID
SELECT HEX(UUID_TO_BIN('6CCD780C-BABA-1026-9564-5B8C656024DB'));
-- Swapped byte order (swap_flag = 1)
SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1));
-- NULL UUID string
SELECT HEX(UUID_TO_BIN(NULL));
-- NULL swap_flag (same bytes as flag 0)
SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', NULL));
DROP DATABASE uuid_test;
BIN_TO_UUID()¶
Description¶
BIN_TO_UUID(bin [, swap_flag]) converts a 16-byte binary representation back to a UUID string in dashed format (aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee). The optional swap_flag must match the flag used in the corresponding UUID_TO_BIN() call:
swap_flag= 0 (default): Assumes the binary value was produced with the original byte order.swap_flag= 1: Reverses the time-field reordering performed byUUID_TO_BIN(..., 1), restoring the original UUID string order.
The input bin must be exactly 16 bytes. Shorter or longer binary values produce an error. A NULL bin returns NULL. A NULL swap_flag means no swapping and does not bypass the 16-byte input check. String flags follow the same decimal-prefix rules as UUID_TO_BIN().
Syntax¶
> BIN_TO_UUID(bin [, swap_flag])
Arguments¶
Arguments |
Description |
|---|---|
bin |
Required. A 16-byte |
swap_flag |
Optional. Must match the flag used in |
Examples¶
DROP DATABASE IF EXISTS uuid_test;
CREATE DATABASE uuid_test;
USE uuid_test;
-- Round-trip with default byte order
SELECT BIN_TO_UUID(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));
-- Round-trip with swap_flag = 1
SELECT BIN_TO_UUID(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1), 1);
-- Cross-mismatched swap_flag (produces incorrect result)
SELECT BIN_TO_UUID(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1), 0);
-- NULL binary
SELECT BIN_TO_UUID(NULL);
DROP DATABASE uuid_test;