mysql 多字段自增长

基础概念

MySQL中的自增长（AUTO_INCREMENT）属性通常用于整数类型的字段，以便在插入新记录时自动为该字段生成唯一的数字。默认情况下，一个表只能有一个自增长字段。然而，通过一些技巧，可以实现多字段的自增长。

类型与应用场景

单表多字段自增长

虽然MySQL不直接支持单表内的多字段自增长，但可以通过触发器或程序逻辑来实现。

应用场景：例如，在电商系统中，可能需要为每个订单生成唯一的订单号和子订单号。

分表分库中的多字段自增长

在分布式系统中，可能需要在多个表或多个数据库实例中实现多字段自增长。

应用场景：例如，在大型社交网络中，用户ID和帖子ID需要在不同的服务器上独立生成。

遇到的问题及解决方法

问题1：MySQL不支持多字段自增长

原因：MySQL的自增长属性只能应用于单个字段。

解决方法：

使用触发器或程序逻辑来模拟多字段自增长。

示例代码：

DELIMITER //
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE new_order_id INT;
    SET new_order_id = (SELECT IFNULL(MAX(order_id), 0) + 1 FROM orders);
    SET NEW.order_id = new_order_id;
    SET NEW.sub_order_id = (SELECT IFNULL(MAX(sub_order_id), 0) + 1 FROM orders WHERE order_id = new_order_id);
END;
//
DELIMITER ;

问题2：分布式系统中的多字段自增长冲突

原因：在分布式系统中，多个实例可能同时生成相同的ID。

解决方法：

使用全局唯一ID生成器，如UUID、Snowflake算法等。

示例代码（使用Snowflake算法）：

import time
import random

class SnowflakeIDGenerator:
    def __init__(self, machine_id):
        self.machine_id = machine_id
        self.sequence = 0
        self.last_timestamp = -1

    def generate_id(self):
        timestamp = int(time.time() * 1000)
        if timestamp < self.last_timestamp:
            raise Exception("Clock moved backwards. Refusing to generate id for %d milliseconds" % (self.last_timestamp - timestamp))
        if timestamp == self.last_timestamp:
            self.sequence = (self.sequence + 1) & 4095
            if self.sequence == 0:
                timestamp = self.til_next_millis(self.last_timestamp)
        else:
            self.sequence = random.randint(0, 4095)
        self.last_timestamp = timestamp
        return ((timestamp - 1288834974657) << 22) | (self.machine_id << 12) | self.sequence

    def til_next_millis(self, last_timestamp):
        timestamp = int(time.time() * 1000)
        while timestamp <= last_timestamp:
            timestamp = int(time.time() * 1000)
        return timestamp

# 示例使用
generator = SnowflakeIDGenerator(machine_id=1)
new_id = generator.generate_id()
print(new_id)