在数据库中存储有序列表的最佳方法？

在数据库中存储“有序列表”以便轻松更新它们（添加、删除和更改条目顺序）的最佳方法是什么？

考虑一个数据库，其中有一个用户和电影表。每个用户都有一个最喜欢的电影列表。

由于许多用户可能喜欢同一部电影，因此我将用户和电影分开表，并使用第三个表 usermovies 来连接它们。

usermovies 包含用户的 ID 和电影以及“订单号”。订单号用于为用户排序电影列表。

例如，用户 Josh 可能有以下列表：

1. 普罗米修斯
2. 黑衣人3
3. 独裁者

用户 Jack 可能有一个类似的列表：

1. 独裁者
2. 普罗米修斯
3. 战列舰
4. 白雪公主与猎人

因此，他们有一些共同的最爱，但顺序不一定相同。

我可以使用查询获取每个用户的电影 ID 列表：

SELECT movie_id FROM usermovies WHERE user_id =? ORDER BY order_number

然后，通过有序的 movie_ids，我可以使用另一个查询获取电影列表

SELECT name FROM movies WHERE id in (?,?,?) ORDER BY FIELD (id, ?,?,?)

因此，查询有效，但更新列表现在看起来非常复杂 - 是否有更好的方法来存储此信息，以便轻松获取用户 x 的电影列表、添加电影、删除电影以及更改列表的顺序？

如果您不是在寻找“上移/下移”之类的解决方案，然后默认添加到列表底部，这里还有一些提示：

可以像这样将新行插入到特定位置：（在位置 3 处插入）

UPDATE usermovies SET order_number = ordernumber + 1 
   WHERE ordernumber > 3 and user_id = ?;
INSERT INTO usermovies VALUES (?, 3, ?);

你可以用类似的方式删除：（删除位置6）

DELETE usermovies WHERE order_numer = 6 and user_id=?;
UPDATE usermovies SET order_number = ordernumber - 1 
   WHERE ordernumber > 6 and user_id = ?;