MySQL 列表排序實戰：上移/下移/置頂的 Rank 交換算法與邊界處理

MySQL 列表排序實戰：上移/下移/置頂的 Rank 交換算法與邊界處理

後台管理常見的列表拖拽排序、上移/下移/置頂按鈕，本質是相鄰記錄 rank 值交換。本文提供基於 id 定位上下鄰居的標準 SQL、邊界條件（首尾記錄）處理邏輯、事務包裝防併發衝突的完整實現方案。

表結構

CREATE TABLE `user` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `rank` INT NOT NULL DEFAULT 0,
  `name` VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

核心查詢：取上一條 / 下一條

START TRANSACTION;
-- 1. 鎖定當前行與目標行
SELECT rank FROM `user` WHERE id IN (3, 2) FOR UPDATE;
-- 2. 交換 rank
UPDATE `user` SET rank = CASE id WHEN 3 THEN @r2 WHEN 2 THEN @r3 END WHERE id IN (3,2);
COMMIT;

完整存儲過程封裝

DELIMITER //
CREATE PROCEDURE move_rank(IN p_id INT, IN p_dir ENUM('up','down','top'))
BEGIN
  DECLARE v_rank INT;
  DECLARE v_target_id INT;
  DECLARE v_target_rank INT;

  SELECT rank INTO v_rank FROM `user` WHERE id = p_id FOR UPDATE;

  CASE p_dir
    WHEN 'up' THEN
      SELECT id, rank INTO v_target_id, v_target_rank
      FROM `user` WHERE id < p_id ORDER BY id DESC LIMIT 1;
      IF v_target_id IS NULL THEN
        SELECT id, rank INTO v_target_id, v_target_rank
        FROM `user` ORDER BY id ASC LIMIT 1; -- 循環到尾部
      END IF;
    WHEN 'down' THEN
      SELECT id, rank INTO v_target_id, v_target_rank
      FROM `user` WHERE id > p_id ORDER BY id ASC LIMIT 1;
      IF v_target_id IS NULL THEN
        SELECT id, rank INTO v_target_id, v_target_rank
        FROM `user` ORDER BY id DESC LIMIT 1; -- 循環到頭部
      END IF;
    WHEN 'top' THEN
      SELECT id, rank INTO v_target_id, v_target_rank
      FROM `user` ORDER BY rank ASC LIMIT 1;
  END CASE;

  IF v_target_id IS NOT NULL AND v_target_id != p_id THEN
    UPDATE `user` SET rank = CASE id WHEN p_id THEN v_target_rank WHEN v_target_id THEN v_rank END
    WHERE id IN (p_id, v_target_id);
  END IF;
END//
DELIMITER ;

併發安全建議

• 所有讀寫在同一事務中，使用 SELECT ... FOR UPDATE 加排他鎖。
• rank 列加唯一索引 UNIQUE KEY uk_rank (rank) 防止並發交換產生重複值。
• 高併發場景可改用「間隔數字」策略（1000, 2000, 3000）僅更新單行，無需交換。

前端調用示例

// 上移
fetch('/api/move', {method:'POST', body: JSON.stringify({id: 3, dir: 'up'})})
// 下移
fetch('/api/move', {method:'POST', body: JSON.stringify({id: 3, dir: 'down'})})
// 置頂
fetch('/api/move', {method:'POST', body: JSON.stringify({id: 3, dir: 'top'})})