MySQL での主キー戦略の選択

CREATE TABLE orders (
    id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id      BINARY(16) PRIMARY KEY,
    user_id INT NOT NULL,
    placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert: convert string UUID to binary at write time
INSERT INTO orders (id, user_id)
VALUES (UUID_TO_BIN(UUID(), 1), 101);

-- Read: convert back to readable format
SELECT BIN_TO_UUID(id, 1), user_id FROM orders;

INSTALL EXTENSION vsql_uuid;

CREATE TABLE orders (
    id      UUID PRIMARY KEY,
    user_id INT NOT NULL,
    placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- UUID_V7 embeds a Unix timestamp in the high bits —
-- new values are always larger, so inserts always append to the index
INSERT INTO orders (id, user_id) VALUES (UUID_V7(), 101);
INSERT INTO orders (id, user_id) VALUES (UUID_V7(), 102);

-- ORDER BY id is roughly chronological — v7 embeds a millisecond timestamp
SELECT user_id, placed_at FROM orders ORDER BY id;

-- The timestamp is recoverable as a datetime or Unix epoch
SELECT UUID_TIMESTAMP(id), UUID_EPOCH(id), user_id FROM orders;

-- Deterministic UUID from a namespace + name (useful for idempotent imports)
-- UUID_NS_DNS() returns the RFC 9562 DNS namespace, so no literal is needed
SELECT UUID_V5(UUID_NS_DNS(), 'user@example.com');