MySQL实现单表内分组自增字段的方法咨询

完全可以通过SQL实现你的需求，下面分两种常见场景给出具体方案，同时解决重复录入的问题：

场景1：需要将SHOP_CUSTOMER_ID存储到表中 #

如果这个分组编号需要被其他业务逻辑引用，建议用触发器配合唯一索引来实现自动生成：

1. 创建带约束的表 #

首先建表时给(SHOP_ID, SHOP_CUSTOMER_ID)添加唯一索引，从根源避免重复：

CREATE TABLE customers (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    SHOP_ID INT NOT NULL,
    SHOP_CUSTOMER_ID INT NOT NULL,
    NAME VARCHAR(100) NOT NULL,
    UNIQUE KEY idx_shop_customer (SHOP_ID, SHOP_CUSTOMER_ID)
);

2. 编写插入触发器 #

触发器会在插入数据时自动计算当前SHOP_ID对应的最大SHOP_CUSTOMER_ID，加1后赋值；若该SHOP_ID还没有数据则默认设为1：

DELIMITER //
CREATE TRIGGER trg_generate_shop_customer_id
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    SET NEW.SHOP_CUSTOMER_ID = (
        SELECT COALESCE(MAX(SHOP_CUSTOMER_ID), 0) + 1
        FROM customers
        WHERE SHOP_ID = NEW.SHOP_ID
    );
END //
DELIMITER ;

插入数据时，你只需要传入SHOP_ID和NAME，SHOP_CUSTOMER_ID会自动生成：

INSERT INTO customers (SHOP_ID, NAME) VALUES (1, '张三');
INSERT INTO customers (SHOP_ID, NAME) VALUES (1, '李四');
INSERT INTO customers (SHOP_ID, NAME) VALUES (2, '王五');

执行后SHOP_CUSTOMER_ID会按SHOP_ID分组从1开始递增。

场景2：仅查询展示时生成编号（无需存储） #

如果这个编号只是用来展示给用户，不需要持久化存储，用窗口函数更高效，完全避免PHP计算的性能问题：

SELECT
    ID,
    SHOP_ID,
    ROW_NUMBER() OVER (PARTITION BY SHOP_ID ORDER BY ID) AS SHOP_CUSTOMER_ID,
    NAME
FROM customers;

这个查询会按SHOP_ID分组，每个组内按ID排序生成从1开始的编号，每次查询实时计算，不需要维护存储字段，也不会有重复问题。

关键注意事项 #

• 不管用哪种方案，(SHOP_ID, SHOP_CUSTOMER_ID)的唯一索引必须加上，强制保证数据唯一性，杜绝重复录入错误。
• 触发器方案要注意高并发场景：如果同一SHOP_ID同时有大量插入，可能出现主键冲突（因为触发器查询和插入之间有时间差），这种情况可以结合事务或者用INSERT ... ON DUPLICATE KEY UPDATE处理，也可考虑乐观锁机制。

内容的提问来源于stack exchange，提问作者szef0