MySQL 存儲 UUID 最佳實踐：移除橫線與二進制存儲指南

為什麼不能直接存字符串 UUID？

標準 UUID：05ba463f-1dab-471f-81c7-58e0b06f35f0 (36 字符)。

• 索引碎片化：完全隨機值導致 B+Tree 頻繁分頁、隨機 I/O、聚簇索引碎片。
• 緩存失效：邏輯相鄰行物理分散，緩存命中率暴跌。
• 空間浪費：36 bytes vs 16 bytes (二進制)，索引體積膨脹 2.25 倍。

進階方案：二進制存儲 (BINARY(16))

核心思想

UUID 128 位 = 16 字節。移除橫線後 32 字符 Hex → UNHEX() → 16 字節二進制 → 存入 BINARY(16)。

關鍵轉換函數 (MySQL 8.0+ 內置 UUID_TO_BIN()/BIN_TO_UUID()，舊版自建)

-- 字符串 UUID → BINARY(16) (RFC 4122 標準字節序調整)
CREATE FUNCTION `GuidToBinary`($Data VARCHAR(36)) RETURNS BINARY(16)
BEGIN
    DECLARE $Result BINARY(16) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Data = REPLACE($Data, '-', '');
        -- 關鍵：調整 time_low/time_mid/time_hi_and_version 字節序為大端序
        SET $Result = CONCAT(
            UNHEX(SUBSTRING($Data, 7, 2)), UNHEX(SUBSTRING($Data, 5, 2)), 
            UNHEX(SUBSTRING($Data, 3, 2)), UNHEX(SUBSTRING($Data, 1, 2)),
            UNHEX(SUBSTRING($Data, 11, 2)), UNHEX(SUBSTRING($Data, 9, 2)),
            UNHEX(SUBSTRING($Data, 15, 2)), UNHEX(SUBSTRING($Data, 13, 2)),
            UNHEX(SUBSTRING($Data, 17, 16))
        );
    END IF;
    RETURN $Result;
END;

-- BINARY(16) → 標準 UUID 字符串
CREATE FUNCTION `ToGuid`($Data BINARY(16)) RETURNS CHAR(36) CHARSET utf8
BEGIN
    DECLARE $Result CHAR(36) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Result = CONCAT(
            HEX(SUBSTRING($Data, 4, 1)), HEX(SUBSTRING($Data, 3, 1)), 
            HEX(SUBSTRING($Data, 2, 1)), HEX(SUBSTRING($Data, 1, 1)), '-',
            HEX(SUBSTRING($Data, 6, 1)), HEX(SUBSTRING($Data, 5, 1)), '-',
            HEX(SUBSTRING($Data, 8, 1)), HEX(SUBSTRING($Data, 7, 1)), '-',
            HEX(SUBSTRING($Data, 9, 2)), '-', HEX(SUBSTRING($Data, 11, 6))
        );
    END IF;
    RETURN $Result;
END;

-- 簡化版：直接生成二進制 UUID (無字節序調整，適合純主鍵)
CREATE FUNCTION `UUIDTOBIN`() RETURNS BINARY(16)
BEGIN
    DECLARE my_uuid CHAR(36);
    SET my_uuid = UUID();
    RETURN CONCAT(
        UNHEX(LEFT(my_uuid, 8)), UNHEX(MID(my_uuid, 10, 4)), 
        UNHEX(MID(my_uuid, 15, 4)), UNHEX(MID(my_uuid, 20, 4)), 
        UNHEX(RIGHT(my_uuid, 12))
    );
END;

表結構設計

CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,  -- 或 VARBINARY(16)
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

應用層使用模式

-- 插入
INSERT INTO users (id, name) VALUES (GuidToBinary('05ba463f-1dab-471f-81c7-58e0b06f35f0'), 'Alice');

-- 查詢 (應用層轉換或 SQL 轉換)
SELECT ToGuid(id) AS uuid, name FROM users WHERE id = GuidToBinary('...');

-- 或 MySQL 8.0+ 內置函數
SELECT BIN_TO_UUID(id) FROM users;
INSERT INTO users (id) VALUES (UUID_TO_BIN(UUID()));

MySQL 8.0+ 內置函數詳解

效能對比實測 (千萬級數據)

避坑指南

1. 字節序一致性：應用層、數據庫層、讀寫路徑必須統一 swap_flag 設置。
2. 驅動程式支援：JDBC/ODBC/PHP PDO 需正確映射 BINARY(16) 到 byte[]/string，避免編碼損壞。
3. 調試可讀性：生產環境建議建立 View 自動轉換，或工具鏈支援二進制顯示。
4. 避免 VARCHAR(36) 當主鍵：除非極小表，否則必有效能隱患。

總結

UUID 存儲鐵律：移除橫線 → 二進制存儲 → 啟用字節序優化。MySQL 8.0+ 內置函數已成熟，舊版本自建函數同理。這是分佈式系統主鍵設計的「黃金標準」，兼顧全局唯一、寫入效能、存儲空間與查詢速度。