MySQL - UUID

MySQL - Using UUIDs for Unique Identifiers

The MySQL UUID() Function

The UUID() function in MySQL generates a Universal Unique Identifier (UUID) according to RFC 4122 (Version 1). These values are designed to be unique across all servers and all time. A UUID is generated using a combination of the current timestamp, the server’s unique MAC address, and a random number, making collisions extraordinarily unlikely.

Understanding the UUID Format

The UUID() function returns a 128-bit number formatted as a UTF-8 string, consisting of five hexadecimal segments separated by hyphens.

The standard format is: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee.

Generating a UUID

Executing the function is straightforward:

SELECT UUID();

Output

Each call generates a new, unique value.

UUID()
5c6f7b1e-7517-11ee-8a39-0242ac130003

UUIDs as Primary Keys: Pros and Cons

Using UUIDs as primary keys is common in distributed systems but comes with trade-offs:

• Pros:
  • Globally Unique: Records can be generated on different systems without ID conflicts, ideal for microservices or offline data synchronization.
  • Secure: They don’t expose sequential information. A user can’t guess /users/124 from /users/123.
  • Stateless Generation: An application can generate its own IDs without querying the database.
• Cons:
  • Storage Size: A UUID string (VARCHAR(36)) takes 36 bytes, far more than a 4-byte INT or 8-byte BIGINT.
  • Performance: UUIDs are not sequential. Inserting new random values into an index (especially a clustered index like InnoDB’s primary key) causes page splits and index fragmentation, leading to slower writes and reads.
  • Readability: They are difficult for humans to read, remember, or compare.

Best Practice: Storing UUIDs as BINARY(16)

To mitigate the storage and performance issues, the modern best practice is to store UUIDs in a BINARY(16) column. This stores the 128-bit value directly, using only 16 bytes. MySQL 8.0+ provides functions to make this easy: UUID_TO_BIN() to convert a UUID string to binary, and BIN_TO_UUID() to convert it back for display.

Creating a Table with a BINARY UUID

Here we create a Logins table where the id is a BINARY(16) primary key.

CREATE TABLE Logins (
    id BINARY(16) PRIMARY KEY,
    user_id INT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45)
);

Inserting and Retrieving UUIDs

When inserting, you must convert the UUID string to binary. When selecting, you convert it back to a readable string.

-- Generate a new UUID and store it in a variable for clarity
SET @new_uuid = UUID();

-- Insert the record using UUID_TO_BIN()
INSERT INTO Logins(id, user_id, ip_address)
VALUES (UUID_TO_BIN(@new_uuid), 101, '192.168.1.100');

-- Retrieve the record using BIN_TO_UUID()
SELECT BIN_TO_UUID(id) as login_id, user_id, login_time
FROM Logins
WHERE user_id = 101;

The output displays the human-readable UUID:

login_id	user_id	login_time
your-generated-uuid-here	101	2023-10-27 10:30:00

Deprecated Practice: Storing UUIDs as VARCHAR

While simple, storing UUIDs in a VARCHAR(36) column is highly inefficient. It uses more than double the storage space and suffers from poor indexing performance. It should be avoided in new applications.

Example (For reference only)

-- ANTI-PATTERN: Inefficient storage
CREATE TABLE OldOrders (
    order_id VARCHAR(36) PRIMARY KEY,
    product_name VARCHAR(100)
);

-- Insertion is direct but performance suffers
INSERT INTO OldOrders (order_id, product_name)
VALUES (UUID(), 'Legacy Keyboard');

Recommendation: Always prefer BINARY(16) for storing UUIDs in modern MySQL databases. For applications needing both performance and the benefits of UUIDs, consider time-sorted UUIDs (like UUIDv7) which can be implemented at the application level to improve index locality.