1. Anatomy of the 128-Bit UUID (RFC 4122)
A Universally Unique Identifier (UUID) consists of 128 bits (16 octets), represented canonically as a 36-character string comprising 32 hexadecimal digits and four hyphens in the familiar 8-4-4-4-12 grouping:
Specifies the generation algorithm (e.g., 1 for Timestamp + MAC, 4 for Cryptographic Randomness, 5 for SHA-1 Hashing).
The 2 to 3 most significant bits of this octet define the variant. Standard RFC 4122 UUIDs use 10xx in binary, resulting in hex characters 8, 9, a, or b.
2. Bit Layout Field Specification Table
🔑 Instant UUID v4 Generator
| Field Name | Byte Offset | Bit Width | Description |
|---|---|---|---|
| time_low | 0 – 3 | 32 bits | Low 32 bits of the timestamp (or random bits in v4). |
| time_mid | 4 – 5 | 16 bits | Middle 16 bits of the timestamp. |
| time_hi_and_version | 6 – 7 | 16 bits | 4-bit version code in MSB, followed by high 12 bits of timestamp. |
| clock_seq_hi_and_res / seq_low | 8 – 9 | 16 bits | 2-3 bit variant code in MSB, followed by clock sequence. |
| node | 10 – 15 | 48 bits | 48-bit IEEE 802 MAC address (or random bits in v4). |
3. Database Optimization: MySQL `BINARY(16)` and B-Tree Page Splits
Storing UUIDs as 36-character VARCHAR(36) strings in SQL databases causes significant performance penalties:
- Storage bloat:
VARCHAR(36)consumes 36 bytes (plus length byte), whereas raw 128-bit storage requires only 16 bytes. - Index fragmentation: Completely random UUIDv4 keys insert haphazardly into InnoDB B-Tree clustered indexes, causing expensive disk page splits and cache evictions.
In MySQL 8.0+, developers can use UUID_TO_BIN(uuid, 1) to reorder timestamp bits chronologically and pack the 32 hex digits into a compact BINARY(16) column:
-- Storing packed ordered UUID in MySQL 8.0:
INSERT INTO orders (id, customer_id, total)
VALUES (UUID_TO_BIN(UUID(), 1), UUID_TO_BIN('4a8f9c2d-1e0b-3a5f-6e7d-8c9b0a1f2e3d', 1), 249.99);
-- Reading back into canonical RFC 4122 string:
SELECT BIN_TO_UUID(id, 1) AS id, total FROM orders;
IETF & Industry References
- Leach P, Mealling M, Salz R: IETF RFC 4122: A Universally Unique IDentifier (UUID) URN Namespace. Standards Track, July 2005. rfc-editor.org/rfc/rfc4122
- Oracle MySQL Documentation: Miscellaneous Functions: UUID_TO_BIN() and BIN_TO_UUID(). MySQL 8.0 Reference Manual.
- International Organization for Standardization: ISO/IEC 9834-8: Information technology — Procedures for the operation of object identifier registration authorities: Generation of universally unique identifiers (UUIDs).