mysql - 如何将用户订购的数据存储在 MySQL 数据库中？

Question

在特定的应用程序中，用户可以将小部件存储在收藏夹列表中。用户和小部件都有自己的 ID，因此我设计了一个 Favorites 表来存储用户的收藏夹:

+-----------+------------------+
|   Field   |       Type       |
+-----------+------------------+
| userid    | int(11) unsigned |
| widgetid  | int(11) unsigned |
| dateadded | int(11) unsigned |
+-----------+------------------+

一切都很好，但现在我希望用户能够手动重新排序他们收藏夹中的小部件。突然间，dateadded 列没有用了，因为更新它只会让用户将小部件撞到列表的前面。

因此，我似乎需要一个索引列:

+-----------+------------------+
|   Field   |       Type       |
+-----------+------------------+
| userid    | int(11) unsigned |
| widgetid  | int(11) unsigned |
| index     | int(11) unsigned |
+-----------+------------------+

这样，为了对列重新排序，我可以像处理数组一样操作数据库表——拉出一行，对其他行重新编号以“释放”一个槽，然后重新插入该行。例如，要将索引 13 处的对象移动到索引 5，我将执行以下操作:

SELECT * FROM Favorites WHERE `index` = 13;
DELETE * FROM Favorites WHERE `index` = 13;

UPDATE Favorites
SET `index` = `index` + 1
WHERE `index` >= 5 AND `index` < 13;

INSERT INTO Favorites (userid, widgetid, index) VALUES (<data from select>, 5);

但是，这感觉非常 hacky 并且非常不符合 SQL，而且我也无法想象性能会很棒。

在 MySQL 数据库中存储可任意重新排序的数据的“正确”解决方案是什么？

Answer 1

我会在某些步骤中使用索引值，例如10、20、30、40。

然后，当我想将索引 40 移动到 10 和 20 之间时，我首先将索引更新为 15，其中索引 = 40。然后根据下面的选择再次更新所有用户的记录，恢复 10、20、30、40 的顺序。

select f.*, 
       @new_index:=@new_index+10 as new_index
from Favorites f, (select @new_index:=0) as sess_var
where f.user_id=:theUserId
order by f.index

查询将返回所有具有新索引值的旧记录。调整它以在您的第二次更新中使用。

更新:

update Favorites f
      join (the query above) sub on
    f.user_id = sub.user_id and f.widget_id= sub.widget_id
set index=sub.new_index