Two Backend Approaches for Drag‑and‑Drop Sorting: Array vs. Doubly Linked List

Background

In many admin panels a drag‑and‑drop ordering feature is needed; a common implementation stores a position column and updates many rows on each move, which can cause performance issues due to row locking.

Solution 1 – Array

Define a table with an integer position column and sort by it:

CREATE TABLE `activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `position` int(11) DEFAULT '0' COMMENT '位置值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When a record is dragged, the client sends new_position and the server compares it with the current old_position . If they differ, all rows with position >= new_position are incremented by 1:

UPDATE activity SET position = position + 1 WHERE position >= #{new_position};

Problems

This approach may update many rows, acquiring an exclusive table lock and degrading concurrency, because inserting an element into an array requires shifting all subsequent elements.

Solution 2 – Doubly Linked List

Instead of a numeric order, store two pointer columns prev_id and sibling_id (the next node) together with a floating‑point position for optional sorting:

CREATE TABLE `activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `prev_id` int(11) DEFAULT NULL COMMENT '前一个元素的ID',
  `sibling_id` int(11) DEFAULT NULL COMMENT '后一个元素的ID',
  `position` FLOAT(6,2) DEFAULT '0.0' COMMENT '顺序位置',
  PRIMARY KEY (`id`),
  KEY `idx_prev_id` (`prev_id`),
  KEY `idx_sibling_id` (`prev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Two operations are described:

moveAfter

The client provides the target record's prev_id . The server updates the surrounding nodes' pointers with a series of UPDATE statements (about six statements total), moving the dragged node after the specified node.

moveBefore

When moving a node to the very first position, the client supplies the sibling_id of the current first node. Only four UPDATE statements are needed.

Overall, the linked‑list method limits updates to at most six rows, uses row‑level locks, and therefore scales better under concurrency than the array method.

Remaining Issues and Improvements

Linked lists are inefficient for range queries because each next node must be fetched individually. To enable sorting and pagination, a floating‑point position can be maintained by setting it to the average of the surrounding nodes' positions during moves.

Yuque's Implementation (Speculative)

Observations of Yuque's API suggest it uses the linked‑list approach, sending a JSON payload like:

{
  "book_id": 10922139,
  "format": "list",
  "action": "moveAfter",
  "target_uuid": "88bJcaqwdyEx2_KN",
  "node_uuid": "yJAhTFEUOfOVEFOo"
}

The response includes prev_uuid and sibling_uuid , confirming the pointer‑based model. Yuque performs full‑list retrieval and sorts in memory, avoiding pagination.

Summary

The article presents two backend strategies for drag‑and‑drop ordering: an array‑style integer position column that is simple but costly to update, and a doubly linked list with pointer columns that reduces update count and lock contention but requires extra handling for sorting and pagination.