如何为playlist_songs表实现按playlistID+userID分组自增的orderID？

这个需求完全可以仅通过SQL实现，不需要依赖上层业务逻辑做额外计算，具体实现步骤如下：

1. 表结构定义 #

你选择的(playlistID, userID, orderID)复合主键完全符合需求，建表参考SQL（以MySQL为例，其他数据库仅需调整语法细节）：

CREATE TABLE playlist_songs (
    playlistID INT NOT NULL COMMENT '歌单ID',
    userID INT NOT NULL COMMENT '用户ID',
    songID INT NOT NULL COMMENT '歌曲ID',
    orderID INT NOT NULL COMMENT '歌单内排序号',
    -- 外键约束按需关联你的用户表、歌单表、歌曲表即可
    PRIMARY KEY (playlistID, userID, orderID),
    FOREIGN KEY (playlistID) REFERENCES playlists(id),
    FOREIGN KEY (userID) REFERENCES users(id),
    FOREIGN KEY (songID) REFERENCES songs(id)
);

2. 分组自增orderID自动生成 #

两种实现方式，都可以完全用SQL完成：

• 插入时直接计算，不需要额外触发器：
  插入新歌曲到指定用户的指定歌单时，直接取当前分组最大orderID+1作为新排序号，SQL示例：

  INSERT INTO playlist_songs (playlistID, userID, songID, orderID)
  SELECT 
      1001, -- 替换为目标歌单ID
      2001, -- 替换为操作用户ID
      3001, -- 替换为要插入的歌曲ID
      COALESCE(MAX(orderID), 0) + 1
  FROM playlist_songs
  WHERE playlistID = 1001 AND userID = 2001;
  

• 触发器封装（更省心，业务层插入无需传orderID）：
  新增前置插入触发器，自动计算orderID，MySQL示例：

  DELIMITER //
  CREATE TRIGGER auto_generate_order_id BEFORE INSERT ON playlist_songs
  FOR EACH ROW
  BEGIN
      SELECT COALESCE(MAX(orderID), 0) + 1 INTO NEW.orderID
      FROM playlist_songs
      WHERE playlistID = NEW.playlistID AND userID = NEW.userID;
  END //
  DELIMITER ;
  

3. 调整歌曲排序的批量更新实现 #

调整排序的逻辑仅需两条SQL即可完成，以用户2001调整歌单1001内的歌曲排序为例：

场景1：把歌曲从旧排序old_order往前调到new_order（比如从5调到2） #

1. 先把new_order到old_order-1之间的所有歌曲排序号+1，腾出位置：

UPDATE playlist_songs
SET orderID = orderID + 1
WHERE playlistID = 1001 
  AND userID = 2001 
  AND orderID BETWEEN 2 AND 4;

2. 再把目标歌曲的排序号改为目标值：

UPDATE playlist_songs
SET orderID = 2
WHERE playlistID = 1001 
  AND userID = 2001 
  AND songID = 3001; -- 也可以用原排序号作为条件：AND orderID = 5

场景2：把歌曲从旧排序old_order往后调到new_order（比如从2调到5） #

1. 先把old_order+1到new_order之间的所有歌曲排序号-1，腾出位置：

UPDATE playlist_songs
SET orderID = orderID - 1
WHERE playlistID = 1001 
  AND userID = 2001 
  AND orderID BETWEEN 3 AND 5;

2. 再把目标歌曲的排序号改为目标值：

UPDATE playlist_songs
SET orderID = 5
WHERE playlistID = 1001 
  AND userID = 2001 
  AND songID = 3001;

补充说明 #

• 并发冲突问题：复合主键本身自带唯一约束，就算高并发场景下出现orderID重复，插入会直接报错，只需重试即可；也可以通过调整事务隔离级别为可重复读避免冲突。
• 性能问题：复合主键遵循最左匹配原则，(playlistID, userID)作为前缀的查询、更新速度极快，单歌单哪怕有数千首歌，批量更新的耗时也可以忽略不计。

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