使用MySQL和PHP实现用户自定义排序

Question

这是一个方法论问题。假设我有一个表格

id int auto_increment primary key,
name text not null

我有一些条目需要以任意方式排序。我可以创建一个界面，允许我按照它们在某个页面上的出现顺序更改名称的顺序。当我读取表格的条目时，它们将按照我选择的顺序进行排序。我看到三种可能的方法。第一种方法是添加一个字段

order int not null

当我更改顺序时，我必须更新每一行，或者至少更新所有顺序高于我更改的最低顺序的行。这似乎是错误的方法，因为它需要在for循环中执行SQL语句。第二种方法是创建另一个表，并通过id进行链接。

id int not null
order int not null

但是在这里我会遇到同样的问题。如果我添加了一个名字并想把它们放在第一位，我就必须更改每一行中的顺序条目。我可以看到可能有第三种方法，即在单个列或甚至平面文件中存储id和顺序之间的某些关联。我可以看到使用JSON格式来实现这一点。

我的问题是，使用MySQL和PHP，最好的方法是什么？

- buck54321

---

order 是一个保留字。你在使用它时需要引用该字段。但是，无论如何，为了保持这个自定义排序字段的同步，你都需要进行很多更新。 - Marc B

应该能够完成更新而不使用 for 循环。但我不确定是否完全理解了用例。听起来似乎相关的表是正确的选择。您能再详细解释一些吗？ - ficuscr

你可以使用jQuery UI sortable，如果你经常更新顺序，那么可以创建另一个id-order表。 - user1646111

很抱歉，你的第一种选择通常是这样做的方式，尽管通常不打算让用户更新。该列的名称通常称为“SortOrder”或“Sequence”。如果要允许用户进行更新，那么你需要准备好大量的更新工作。 - criticalfix

Answer 1

是的，sorting列可以正常工作。您不需要使用for循环。

插入数据时，请先递增其他排序值。

UPDATE foo
  SET sorting = sorting + 1
  WHERE sorting >= :sorting;

INSERT INTO foo
  SET name = :name, sorting = :sorting;

查看演示

在更新时，将特定记录的sorting列设置为新索引，并增加/减少其他记录的sorting以创建有效的序列。

SELECT @old_sorting:=sorting FROM foo WHERE id = :id;
UPDATE foo
  SET sorting = IF(id = :id, :sorting, sorting + IF(@old_sorting > :sorting, 1, -1))
  WHERE sorting BETWEEN LEAST(@old_sorting, :sorting) AND GREATEST(@old_sorting, :sorting);

查看演示

应由您的mysql库插入:id，:name和:sorting的值

- Arnold Daniels

---

很奇怪，我之前使用过这个确切的方法，但是发帖时就想不起来了！点赞（并删除我没什么用的帖子 =P） - newfurniturey

这太好了。谢谢你。我说更新时使用相同的方法作为插入的第一步，然后只需更新感兴趣的行，这样做会导致序列中出现间隔，我的说法是否正确？ - buck54321

非常有帮助，你无法想象。感谢@buck54321的问题和@Jasny通过使用“排序”列指出解决方案。这将避免未来的麻烦。 - Avatar

Answer 2

你可以使用“weight” int，因为Order是一个保留字。至于避免常量更新，你需要编写一些逻辑来防止错误，但如果你将默认的重量增量设置为1以外的其他值，你可以将物品放在其他物品之间。

例如：

id weight item

1 1 something

2 10 somethingElse 添加一个项目到两者之间：

id weight item

1 1 something

3 5 someOtherThing

2 10 somethingElse 当然，第二个表或更简单的结构会更少出错。

- Martin Sheeks