MySQL：基于首个主键实现第二主键自增的安全方案问询

需要实现第二主键基于首个主键自增的需求：以orderItems表为例，order_id(INT)和order_item(SMALLINT)是联合主键，order_item作为排序键，插入时要自动取同一order_id下的最大order_item值加1——比如插入order_id=1的首行时order_item=1，插入order_id=2的首行时order_item也为1。

我试过的方案都有问题：

• 直接给order_item设AUTO_INCREMENT：全局自增，不区分order_id；
• 先查MAX(order_item)+1再插入：并发插入时会出现重复值；
• 插入语句里直接用SELECT MAX(order_item)+1 FROM orderItems：MySQL 8.0.30报错You can't specify target table 'orderItems' for update in FROM clause；
• 用SELECT ... FOR UPDATE：没法锁定不存在的order_id记录，并发问题还是解决不了；
• 考虑用INSERT前触发器：担心性能比两次查询还差，拿不准是不是最优解。

想找MySQL原生方案，避开这些瓶颈。

1. 嵌套子查询绕开同表更新限制（单条插入首选） #

针对MySQL禁止同表更新的问题，把查询结果包装成临时表就能解决，再配合事务保证并发安全：

START TRANSACTION;
INSERT INTO orderItems (order_id, order_item, other_column)
SELECT 
    1 AS order_id,
    COALESCE(MAX(order_item), 0) + 1 AS order_item,
    '商品A' AS other_column
FROM (SELECT order_item FROM orderItems WHERE order_id = 1) AS temp;
COMMIT;

• 逻辑：嵌套子查询生成临时表，躲开同表更新的限制；
• 并发保障：事务里的InnoDB行锁会锁住对应order_id的记录，避免重复值；
• 局限性：一次只能生成一个自增值，不适合批量插入同order_id的多条记录。

2. INSERT前触发器（省心可靠，性能不差） #

触发器是MySQL原生支持的方案，性能开销远没你想的大，反而比两次查询加插入的网络往返更高效：
先创建触发器：

DELIMITER //
CREATE TRIGGER before_insert_orderItems
BEFORE INSERT ON orderItems
FOR EACH ROW
BEGIN
    SET NEW.order_item = (
        SELECT COALESCE(MAX(order_item), 0) + 1 
        FROM orderItems 
        WHERE order_id = NEW.order_id
    );
END //
DELIMITER ;

• 优势：应用层不用改逻辑，只要传order_id和其他字段，order_item自动生成；
• 并发安全：触发器执行时，InnoDB会给对应order_id的行加锁，不存在的order_id会触发间隙锁，防止幻读，保证自增正确；
• 性能：单触发器的开销远低于应用层两次请求数据库的开销，跨机房部署时优势更明显。

3. 辅助表维护自增序列（高并发场景首选） #

如果是极高并发的场景，搞个辅助表存每个order_id的当前最大order_item，用INSERT ... ON DUPLICATE KEY UPDATE原子性拿自增值：

1. 先建辅助表：

CREATE TABLE order_item_seq (
    order_id INT PRIMARY KEY,
    next_item SMALLINT NOT NULL DEFAULT 1
);

2. 事务里先更辅助表拿值，再插主表：

START TRANSACTION;
INSERT INTO order_item_seq (order_id) VALUES (1)
ON DUPLICATE KEY UPDATE next_item = next_item + 1;

INSERT INTO orderItems (order_id, order_item, other_column)
VALUES (1, LAST_INSERT_ID(next_item), '商品A');
COMMIT;

• 逻辑：INSERT ... ON DUPLICATE KEY UPDATE是原子操作，绝对不会出现并发冲突；
• 优势：辅助表行锁粒度更小，高并发下比直接操作主表快；
• 注意：要同步主表和辅助表的数据，比如删主表order_id相关记录时，得更辅助表（可以加触发器实现）。

4. MySQL 8.0+窗口函数+CTE（批量插入同order_id的场景） #

如果要批量插同一order_id的多条记录，用CTE和窗口函数能一次性生成连续的order_item：

WITH new_items AS (
    SELECT 
        1 AS order_id,
        '商品A' AS product_name,
        ROW_NUMBER() OVER () AS rn
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS temp -- 模拟3条待插数据
),
max_item AS (
    SELECT COALESCE(MAX(order_item), 0) AS max_val FROM orderItems WHERE order_id = 1
)
INSERT INTO orderItems (order_id, order_item, product_name)
SELECT 
    order_id,
    max_val + rn,
    product_name
FROM new_items, max_item;

• 优势：一次生成批量自增值，不用多次插入；
• 并发保障：同样要放事务里，靠行锁保证安全。

内容的提问来源于stack exchange，提问作者Sercan Samet Savran