我正在尝试创建一个web应用程序，它有一个类似卡片的界面，用于在容器中拖放文档（类似于“asana Jmeter 板”）。

我面临的问题是，我无法找到一种有效的方法来以结构化和易于更新的方式存储这些数据。我以前看过一个链表式的结构，但它是一个噩梦般的查询。我目前使用的解决方案是基于一个额外的位置列，该列在每次插入/更新/删除时都会重新计算，但是这个解决方案在大列表上的速度非常慢：

CREATE TABLE IF NOT EXISTS section (
    id VARCHAR(20) NOT NULL,
    collection_id VARCHAR(20) NOT NULL,
    position SMALLINT UNSIGNED NOT NULL,
    title TINYTEXT NOT NULL,
    description TEXT,
    PRIMARY KEY(id),
    UNIQUE INDEX section_position (collection_id, position)
);

INSERT INTO section (id, position, collection_id, title) VALUES ("0", 0, "1", "Test 1");
INSERT INTO section (id, position, collection_id, title) VALUES ("1", 1, "1", "Test 2");
INSERT INTO section (id, position, collection_id, title) VALUES ("2", 2, "1", "Test 3");
INSERT INTO section (id, position, collection_id, title) VALUES ("3", 3, "1", "Test 4");
INSERT INTO section (id, position, collection_id, title) VALUES ("4", 4, "1", "Test 5");
INSERT INTO section (id, position, collection_id, title) VALUES ("5", 5, "1", "Test 6");
INSERT INTO section (id, position, collection_id, title) VALUES ("6", 6, "1", "Test 7");
INSERT INTO section (id, position, collection_id, title) VALUES ("7", 7, "1", "Test 8");
INSERT INTO section (id, position, collection_id, title) VALUES ("8", 8, "1", "Test 9");
INSERT INTO section (id, position, collection_id, title) VALUES ("9", 9, "1", "Test 10");
INSERT INTO section (id, position, collection_id, title) VALUES ("a", 10, "1", "Test 11");
INSERT INTO section (id, position, collection_id, title) VALUES ("b", 11, "1", "Test 12");

-- Insert
SET @maxPos=(SELECT MAX(position)+1 FROM section WHERE collection_id="1");
UPDATE section SET position=position+1 WHERE collection_id="1" AND position>=0 ORDER BY position DESC;
INSERT INTO section (id, position, collection_id, title) VALUES ("13", IF(@maxPos<0, @maxPos, 0), "1", "Insert Test 1");

-- Remove
SET @oldPos=(SELECT position FROM section WHERE id="6");
DELETE FROM section WHERE id="6";
UPDATE section SET position=position-1 WHERE collection_id="1" AND position>=@oldPos ORDER BY position ASC;

-- Update
SET @maxPos=(SELECT MAX(position) FROM section WHERE collection_id="1");
SET @newPos=IF(@maxPos<10, @maxPos, 10);
SET @oldPos=(SELECT position FROM section WHERE id="2");
SET @shiftingDirection=IF(@oldPos>@newPos, 1, -1);

DELETE FROM section WHERE id="2";
UPDATE section SET position=position+@shiftingDirection WHERE collection_id="1" AND position BETWEEN @oldPos AND @newPos ORDER BY position-IF(@shiftingDirection=1, 0, @maxPos);
INSERT INTO section (id, position, collection_id, title) VALUES ("2", @newPos, "1", "Test 2");

有没有另一种更有效的存储数据的方法，如果是这样的话，有没有一种方法可以用触发器之类的东西来自动化列表管理？
当做
比特菲尼克斯