MySQL の UUID

-- This works but has problems at scale
CREATE TABLE orders (
    order_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    user_id  INT NOT NULL
);

CREATE TABLE orders (
    order_id BINARY(16) PRIMARY KEY,
    user_id  INT NOT NULL
);

-- Store: convert string UUID to binary
INSERT INTO orders (order_id, user_id)
VALUES (UUID_TO_BIN(UUID()), 101);

-- Retrieve: convert back to readable format
SELECT BIN_TO_UUID(order_id), user_id FROM orders;

INSTALL EXTENSION vsql_uuid;

-- Native UUID type — no UUID_TO_BIN/BIN_TO_UUID needed
CREATE TABLE orders (
    order_id UUID PRIMARY KEY,
    user_id  INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- UUID_V7 inserts always append to the index — no page splits
INSERT INTO orders (order_id, user_id) VALUES (UUID_V7(), 101);
INSERT INTO orders (order_id, user_id) VALUES (UUID_V7(), 102);

-- Sorts chronologically by creation order
SELECT user_id, created_at FROM orders ORDER BY order_id;

-- Inspect the timestamp embedded in a v7 UUID (as datetime or Unix epoch)
SELECT UUID_TIMESTAMP(order_id), UUID_EPOCH(order_id) FROM orders LIMIT 1;

SELECT UUID_V5(UUID_NS_DNS(), 'example.com');
-- cfbff0d1-9375-5685-968c-48ce8b15ae17

SELECT UUID_IS_VALID('550e8400-e29b-41d4-a716-446655440000');  -- 1
SELECT UUID_IS_VALID('nope');                                  -- 0

-- Reject malformed IDs at the schema level
CREATE TABLE staging (
  raw VARCHAR(40),
  CHECK (UUID_IS_VALID(raw) = 1)
);

CREATE TABLE audit_log (
  id uuid NOT NULL,
  ver INT AS (UUID_VERSION(id)) STORED,  -- derive the version into a column
  CHECK (UUID_VERSION(id) IN (4, 7))     -- accept only v4 or v7 IDs
);