假设PHP / MySQL，但我不一定需要实际的code，我在它背后的理论只是有兴趣。

Assume PHP/MYSQL but I don't necessarily need actual code, I'm just interested in the theory behind it.

一个很好的用例将是Facebook的照片库页。你可以拖放照片的网页上，它将触发一个Ajax事件保存新的排序顺序。我采取一些非常相似。

A good use-case would be Facebook's photo gallery page. You can drag and drop a photo on the page, which fires an Ajax event to save the new sort order. I'm implementing something very similar.

例如，我有一个数据库表照片大约一万条记录：

For example, I have a database table "photos" with about a million records:

照片 ID：INT， 用户名：INT， ALBUMID：INT， 排序顺序：INT， 文件名：VARCHAR， 标题：VARCHAR

photos id : int, userid : int, albumid : int, sortorder : int, filename : varchar, title : varchar

让我们说我有100张照片的相册。我拖/放照片到一个新的位置和AJAX事件触发了保存在服务器上。

Let's say I have an album with 100 photos. I drag/drop a photo into a new location and the Ajax event fires off to save on the server.

我应该通过照片的整个阵列的ID返回到服务器和更新每个记录？假设通过输入验证其中userid = loggedin_id ，所以恶意用户不仅可以用自己的排序顺序乱照片

Should I be passing the entire array of photo ids back to the server and updating every record? Assume input validation by "WHERE userid=loggedin_id", so malicious users can only mess with the sort order of their own photos

我应该传递照片的身份证，它的previous排序顺序指数及其新排序顺序的索引，检索这两个指数之间的所有记录，对它们进行排序，然后更新他们的订单？

Should I be passing the photo id, its previous sortorder index and its new sortorder index, retrieve all records between these 2 indices, sort them, then update their orders?

如果有数千张照片在一个画廊，排序顺序被改变会发生什么情况？

What happens if there are thousands of photos in a single gallery and the sort order is changed?

推荐答案

怎么样只用整数列定义的顺序？默认情况下分配号码* 1000，如1000，2000，3000 ....如果你1000和2000之间移动3000将其更改为1500。因此，在你不需要更新其他号码都大多数情况下。我使用这种方式，效果很好。你也可以使用双但你没有对precision和舍入误差控制，所以宁可不使用它。

What about just using an integer column which defines the order? By default you assign numbers * 1000, like 1000, 2000, 3000.... and if you move 3000 between 1000 and 2000 you change it to 1500. So in most cases you don't need to update the other numbers at all. I use this approach and it works well. You could also use double but then you don't have control about the precision and rounding errors, so rather don't use it.

所以算法看起来像：说你将B移到位置之后答：首先执行选择查看记录的顺序旁边A.如果它比至少为+2高为了对B的，那么你只设置以适应之间。但是，如果它只是+1较高（有一个后无空格），选择B的接壤记录，看看有多少空间这一边，除以2，然后将此值添加到A之间的所有记录的顺序和B就是这样！

So the algorithm would look like: say you move B to position after A. First perform select to see the order of the record next to A. If it is at least +2 higher than the order of A then you just set order of B to fit in between. But if it's just +1 higher (there is no space after A), you select the bordering records of B to see how much space is on this side, divide by 2 and then add this value to the order of all the records between A and B. That's it!

（请注意，您应该使用事务/锁定任何算法，其中包含多单的查询，所以这也适用于该情况下，最简单的方法是使用InnoDB的事务。）

(Note that you should use transaction/locking for any algorithm which contains more than a single query, so this applies to this case too. The easiest way is to use InnoDB transaction.)