跳至主要內容

MySQL使用UUID函数自动生成uuid并插入指定列

SunshineCloud2024/1/22大约 7 分钟运维笔记数据库MySQLUUID数据库设计触发器

MySQL使用UUID函数自动生成UUID并插入指定列

概述

在MySQL数据库中，UUID（Universally Unique Identifier）是一种128位的全局唯一标识符，广泛用于分布式系统中确保记录的唯一性。本文详细介绍如何在MySQL中使用UUID函数自动生成UUID并插入到指定列。

目录

UUID基础知识

什么是UUID

UUID是一个36字符的字符串，格式为 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx，例如：

550e8400-e29b-41d4-a716-446655440000

MySQL中的UUID函数

MySQL提供了以下UUID相关函数：

函数说明示例
UUID()生成标准UUID格式字符串550e8400-e29b-41d4-a716-446655440000
UUID_SHORT()生成64位整数UUID92395783831158784
UUID_TO_BIN()将UUID字符串转换为二进制用于存储优化
BIN_TO_UUID()将二进制UUID转换为字符串用于查询显示

实现方法汇总

方法对比表

方法优点缺点适用场景
触发器自动化程度高，透明增加数据库负担单表简单场景
默认值简单高效MySQL版本限制新建表推荐
程序实现灵活可控需要程序配合复杂业务逻辑
存储过程逻辑集中维护复杂批量操作

触发器实现

基础触发器

-- 创建示例表
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建BEFORE INSERT触发器
DELIMITER //
CREATE TRIGGER tr_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW 
BEGIN
    -- 如果id为空或NULL，则自动生成UUID
    IF NEW.id IS NULL OR NEW.id = '' THEN
        SET NEW.id = UUID();
    END IF;
END //
DELIMITER ;

高级触发器（支持更新）

-- 支持INSERT和UPDATE的触发器
DELIMITER //
CREATE TRIGGER tr_users_before_insert_update
BEFORE INSERT ON users
FOR EACH ROW 
BEGIN
    SET NEW.id = COALESCE(NEW.id, UUID());
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END //

CREATE TRIGGER tr_users_before_update
BEFORE UPDATE ON users
FOR EACH ROW 
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END //
DELIMITER ;

测试触发器

-- 测试插入（不指定id）
INSERT INTO users (username, email) 
VALUES ('john_doe', 'john@example.com');

-- 测试插入（指定id）
INSERT INTO users (id, username, email) 
VALUES ('custom-id-123', 'jane_doe', 'jane@example.com');

-- 查看结果
SELECT * FROM users;

默认值实现

MySQL 8.0+ 表达式默认值

-- MySQL 8.0开始支持表达式作为默认值
CREATE TABLE products (
    id CHAR(36) DEFAULT (UUID()) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 直接插入，id自动生成
INSERT INTO products (name, price) 
VALUES ('iPhone 15', 999.99);

兼容旧版本的方案

-- 对于MySQL 5.7及以下版本
CREATE TABLE orders (
    id CHAR(36) PRIMARY KEY,
    customer_id CHAR(36) NOT NULL,
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 使用应用程序或触发器生成UUID

程序层面实现

PHP实现

<?php
class UUIDHelper {
    /**
     * 生成MySQL兼容的UUID
     */
    public static function generate() {
        return sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            mt_rand(0, 0xffff), mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0x0fff) | 0x4000,
            mt_rand(0, 0x3fff) | 0x8000,
            mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
        );
    }
    
    /**
     * 使用MySQL UUID()函数
     */
    public static function mysqlGenerate($pdo) {
        $stmt = $pdo->query("SELECT UUID() as uuid");
        return $stmt->fetch(PDO::FETCH_ASSOC)['uuid'];
    }
}

// 使用示例
$uuid = UUIDHelper::generate();
$sql = "INSERT INTO users (id, username, email) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$uuid, 'username', 'email@example.com']);
?>

Python实现

import uuid
import mysql.connector

class MySQLUUIDManager:
    def __init__(self, connection):
        self.conn = connection
        self.cursor = connection.cursor()
    
    def generate_uuid(self):
        """生成标准UUID"""
        return str(uuid.uuid4())
    
    def mysql_uuid(self):
        """使用MySQL UUID()函数"""
        self.cursor.execute("SELECT UUID()")
        return self.cursor.fetchone()[0]
    
    def insert_with_uuid(self, table, data):
        """插入数据时自动生成UUID"""
        if 'id' not in data:
            data['id'] = self.generate_uuid()
        
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        
        self.cursor.execute(sql, list(data.values()))
        self.conn.commit()
        return data['id']

# 使用示例
config = {
    'host': 'localhost',
    'user': 'username',
    'password': 'password',
    'database': 'testdb'
}

conn = mysql.connector.connect(**config)
uuid_manager = MySQLUUIDManager(conn)

# 插入用户数据
user_id = uuid_manager.insert_with_uuid('users', {
    'username': 'python_user',
    'email': 'python@example.com'
})
print(f"用户创建成功，ID: {user_id}")

性能对比分析

UUID vs AUTO_INCREMENT性能测试

-- 创建测试表
CREATE TABLE test_uuid (
    id CHAR(36) PRIMARY KEY,
    data VARCHAR(100),
    INDEX idx_data (data)
);

CREATE TABLE test_auto (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(100),
    INDEX idx_data (data)
);

-- 插入性能测试
-- 测试1万条记录的插入时间
SET @start_time = NOW(6);
-- 执行插入操作...
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) as execution_time_microseconds;

性能对比结果

操作类型UUID (CHAR(36))AUTO_INCREMENT (INT)性能差异
插入速度较慢快~20-30%差异
查询速度较慢快~10-15%差异
存储空间36字节4字节9倍差异
索引大小大小显著差异

优化建议

  1. 使用BINARY存储UUID
-- 优化存储方案
CREATE TABLE optimized_uuid (
    id BINARY(16) PRIMARY KEY,
    uuid_readable CHAR(36) GENERATED ALWAYS AS (
        BIN_TO_UUID(id)
    ) VIRTUAL,
    data VARCHAR(100)
);

-- 插入数据
INSERT INTO optimized_uuid (id, data) 
VALUES (UUID_TO_BIN(UUID()), 'test data');
  1. 建立合适的索引
-- 为经常查询的UUID列建立索引
ALTER TABLE users ADD INDEX idx_uuid_prefix (id(8));

最佳实践

1. 选择合适的UUID策略

-- 场景1：分布式系统，需要全局唯一
CREATE TABLE distributed_table (
    id CHAR(36) DEFAULT (UUID()) PRIMARY KEY,
    -- ...
);

-- 场景2：高性能要求，单机部署
CREATE TABLE performance_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) DEFAULT (UUID()) UNIQUE,
    -- ...
);

-- 场景3：存储优化
CREATE TABLE optimized_table (
    id BINARY(16) DEFAULT (UUID_TO_BIN(UUID())) PRIMARY KEY,
    -- ...
);

2. UUID管理工具函数

-- 创建UUID管理存储过程
DELIMITER //
CREATE PROCEDURE sp_generate_short_uuid(OUT short_uuid VARCHAR(8))
BEGIN
    DECLARE full_uuid CHAR(36);
    SET full_uuid = UUID();
    SET short_uuid = SUBSTRING(REPLACE(full_uuid, '-', ''), 1, 8);
END //
DELIMITER ;

-- 使用短UUID
CALL sp_generate_short_uuid(@short_id);
SELECT @short_id;

3. 批量UUID生成

-- 批量生成UUID的存储过程
DELIMITER //
CREATE PROCEDURE sp_batch_insert_with_uuid(
    IN batch_size INT,
    IN table_name VARCHAR(64)
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE sql_stmt TEXT;
    
    WHILE i < batch_size DO
        SET sql_stmt = CONCAT(
            'INSERT INTO ', table_name, 
            ' (id, created_at) VALUES (UUID(), NOW())'
        );
        
        SET @sql = sql_stmt;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

常见问题解答

Q1: UUID vs AUTO_INCREMENT 如何选择？

答：

  • 选择UUID的场景：

    • 分布式系统需要全局唯一性
    • 数据需要在多个数据库间同步
    • 安全性要求高（不可预测的ID）

  • 选择AUTO_INCREMENT的场景：

    • 单机部署，性能要求高
    • 存储空间有限
    • 需要有序的ID

Q2: 如何处理UUID重复问题？

答： UUID重复的概率极低（约1/5.3×10³⁶），但可以通过以下方式进一步保证：

-- 方法1：使用IGNORE关键字
INSERT IGNORE INTO users (id, username) 
VALUES (UUID(), 'test_user');

-- 方法2：使用ON DUPLICATE KEY UPDATE
INSERT INTO users (id, username) 
VALUES (UUID(), 'test_user')
ON DUPLICATE KEY UPDATE username = VALUES(username);

-- 方法3：先检查再插入
INSERT INTO users (id, username)
SELECT uuid_val, 'test_user'
FROM (SELECT UUID() as uuid_val) t
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE id = t.uuid_val
);

Q3: 如何迁移已有的AUTO_INCREMENT表到UUID？

答：

-- 步骤1：添加UUID列
ALTER TABLE existing_table 
ADD COLUMN uuid_id CHAR(36) DEFAULT (UUID());

-- 步骤2：为现有记录生成UUID
UPDATE existing_table SET uuid_id = UUID() WHERE uuid_id IS NULL;

-- 步骤3：添加唯一索引
ALTER TABLE existing_table ADD UNIQUE INDEX idx_uuid (uuid_id);

-- 步骤4：逐步迁移应用程序使用UUID

-- 步骤5：最终删除原有ID列（谨慎操作）
-- ALTER TABLE existing_table DROP COLUMN id;
-- ALTER TABLE existing_table CHANGE uuid_id id CHAR(36) PRIMARY KEY;

Q4: UUID的存储优化技巧？

答：

-- 技巧1：使用BINARY存储，节省空间
CREATE TABLE optimized_storage (
    id BINARY(16) PRIMARY KEY,
    readable_id CHAR(36) GENERATED ALWAYS AS (BIN_TO_UUID(id)) STORED,
    data TEXT
);

-- 技巧2：使用压缩存储
CREATE TABLE compressed_uuid (
    id CHAR(36) PRIMARY KEY,
    data TEXT
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

-- 技巧3：分区表优化
CREATE TABLE partitioned_uuid (
    id CHAR(36) PRIMARY KEY,
    created_date DATE,
    data TEXT
) PARTITION BY RANGE (YEAR(created_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

通过合理使用UUID，可以在保证数据唯一性的同时，满足分布式系统的需求。选择合适的实现方案和优化策略，能够在功能性和性能之间找到最佳平衡点。

SunshineNotes © 2023-present SunshineCloud 重构版
Copyright © 2025 SunshineCloud