我在MySQL数据库中创建一个向上/向下移动记录的函数时遇到了一个问题。我当前的表格看起来像:
Id UNSIGNED INT AI PRIMARY | Some other columns | Sort UNSIGNED INT INDEX
假设我有一些数据:
1 | FirstRecord | 1
2 | Second | 2
3 | Third | 3
4 | 4th | 4
Much more data...
在对"Id=4"执行"上移"操作后,我希望"Id=4%"获得"Id@above"的"排序",并希望"Id@above"获得"Id=4"的"分类"
我一开始有什么:
- 记录Id我想移动
- 我想移动的方向(向上/向下)
我最终想要什么
- 我的记录向上或向下移动(交换了"排序"值)
- "排序"列中的连续数字
我想怎么做
- 因为我使用的是php5.4,所以我可以使用这种语言中的一些代码,而不仅仅是纯sql
- 我必须为高负载和同时请求做好准备
- 我不想创建MySQL过程或函数。最好的方法是在一个简单的查询中完成
我尝试过的
- 最简单的方法是使用php和:
- 获取当前Id的"排序"(第一个查询)
- 查找上/下列的Id(第二个查询)
- 获取此列的"排序"(第三个查询)
- 交换"排序"值(第4个和第5个查询)
优点是这种方法非常简单,我认为仅此而已。
缺点是两行暂时具有相同的"排序"值。另一种情况是,当两个脚本同时运行时,结果可能是不可预测的。同样,删除任何记录后,在计算上也会出现漏洞。
- 更复杂,也使用php
- 将"排序"更改为FLOAT(在开始时,而不是每次)
- 从记录中减去或加1.5
- 在SQL查询中更新或记录以使数字连续
优点:无需阅读有关下一条/上一条记录的任何内容。每次移动后,我们确信,"排序"数字将是好的。我们可以在排序上使用UNIQUE。
缺点:在巨大的表中,分配新的排序号需要大量的CPU时间。我说得对吗?我们也有不同于Id类型的"排序"…
- 与以前类似
- 从要移动的记录中减去/加1
- 使用我们的Id作为第二个Sort参数,通过查询分配连续的数字
优点:同上,但无法使用UNIQUE。但我们有与Id 相同类型的"排序"
缺点:在一段时间内,两条记录都有相同的"排序",高cpu使用率(?)
到目前为止,这些都是我所知道的最好的方法。你们中有人认识其他人吗,把所有的优点联系起来,没有缺点?
谢谢你的重播。
您需要对要移动的记录进行1次查询才能获得排序索引。然后,您应该从排序UI中输入新的排序索引。
如果你只是交换两个项目,你可以在一个查询中完成:
UPDATE table SET sort_index = IF(id === ?id?, ?new_sort_index?, ?old_sort_index?) WHERE id = ?id? OR sort_index = ?new_sort_index?
使用IF
可以同时更新两条记录。
如果您可以将一个项目移动到多个位置,则需要进行范围更新。首先弄清楚方向:向上/向下。然后做一个类似的查询:
UPDATE table SET sort_index = sort_index + 1 WHERE sort_index >= ?new_sort_index? AND sort_index < ?old_sort_index?
这在?new_sort_index?并在?旧的排序索引?
然后是最后一个查询,为移动的记录设置新的排序索引。
后一种方法有效,即使你的索引是稀疏的(即有洞),前一种方法也不起作用,所以要记住这一点。