mysql - 如何在 MySQL 中创建可更新的有序列表结构

Question

我正在尝试创建一个 Web 应用程序，该应用程序具有类似卡片的界面，用于在容器内拖放文档（类似于“Asana Dashboard”）。

我面临的问题是我无法找到一种有效的方法来以结构化且易于更新的方式存储这些数据。我之前看过一个类似链表的结构，但查询起来是一场噩梦。我目前使用的解决方案是基于在每次插入/更新/删除时重新计算的额外位置列，但是这个解决方案在大列表上变得相当慢：

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");

是否有另一种更有效的方式来存储像这样结构化的数据，如果有的话，有没有办法用触发器之类的东西来自动化列表管理？

问候

比特菲尼克斯

标签： mysqldatabaseentity-relationshipquerying