In my application, users can rearrange their favorite books in whatever order they choose.
在我的应用程序中，用户可以按照他们选择的顺序重新排列他们喜爱的书籍。
I have a "books" table in my database with a row for each book. Currently, there's an integer column called "position" that stores the position of each book: 1 for the top book, 2 for the next one, etc.
我的数据库中有一个“书籍”表，每本书都有一行。目前，有一个名为“position”的整数列，用于存储每本书的位置：1为*书籍，2为下一本书，等等。
The problem is that if someone drags a book from, say, position #11000 to position #1, I then have to make 11,000 updates to the database. This seems inefficient. Is there a better way to do this?
问题在于，如果有人将书从位置＃11000拖到位置＃1，那么我必须对数据库进行11,000次更新。这似乎效率低下。有一个更好的方法吗？
One idea I've had would be just to have another table called "book_sort_orderings" or something, with a row for each user. And one column would be a huge text column that stores a sorted list of book ids. Then when the user rearranges the books, I can pull this value out into my code, perform the rearrangement there, and update the database row. Of course, any time a book is added or deleted I'd have to update this array as well. Is this the "right" way to go about things? Or is there something clever I can do to speed things up without changing my current setup?
我曾经有过的一个想法就是让另一个名为“book_sort_orderings”的表或其他东西，每个用户都有一行。一列是一个巨大的文本列，用于存储书籍ID的排序列表。然后，当用户重新排列书籍时，我可以将此值拉出到我的代码中，在那里执行重新排列，并更新数据库行。当然，每当添加或删除一本书时，我都必须更新这个数组。这是处理事情的“正确”方式吗？或者我有什么聪明才能加快速度而不改变我目前的设置？
4 个解决方案
#1
4
It's not really hard to update all those rows in your example with a couple of SQL statements. You don't need to fire 11,000 updates at your DBMS (which I assume is what you were trying to say).
使用几个SQL语句更新示例中的所有行并不是很难。您不需要在DBMS上发出11,000个更新（我认为这是您想要说的）。
First, update all the books that are being shuffled forward one position:
首先，更新所有正在洗牌的书籍：
UPDATE book
SET position = position + 1
WHERE position < 11000
AND position >= 1
...and then set the position of the book you're moving:
...然后设置你正在移动的书的位置：
UPDATE book
SET position = 1
WHERE id = whatever
#2
8
You'd be surprised how fast a decent DBMS can update 11,000 rows, assuming you do it in a "bulk" fashion (as opposed to making a separate database round-trip for each row).
假设您以“批量”方式执行此操作（而不是为每行创建单独的数据库往返），您会惊讶于一个体面的DBMS可以快速更新11,000行。
But if you want to avoid that, use the old BASIC trick (from the time BASIC still had line numbers): leave gaps!
但是如果你想避免这种情况，那就使用旧的BASIC技巧（从BASIC的时候还有行号）：留下空白！
Instead of using positions: 1, 2, 3, 4, 5 etc... use 10, 20, 30, 40, 50 etc....
而不是使用位置：1,2,3,4,5等......使用10,20,30,40,50等....
So when you need to move (say) the first item to the next-to-last place, just modify 10 to 41 and you'll end-up with: 20, 30, 40, 41, 50 etc.... Obviously, you'll need to do some fiddling in case a gap gets completely filled, but this strategy should be able almost eliminate massive UPDATEs.
所以当你需要将第一个项目移动到倒数第二个位置时，只需修改10到41，你最终会得到：20,30,40,41,50等......显然，如果差距完全填满，你需要做一些摆弄，但这个策略应该能够几乎消除大量的UPDATE。
The other possibility is to implement a doubly-linked list: instead of order, keep an ID of the previous and the next item. Reordering can be done by simply "re-linking" the IDs, much as you would in an in-memory list. Unfortunately, you'd also prevent the DBMS from sorting the items directly (at least without awkward and probably inefficient recursive queries) - you'd have to do the sorting at the application level, so I'd recommend agains it
另一种可能性是实现双向链表：而不是订单，保留前一个和下一个项的ID。重新排序可以通过简单地“重新链接”ID来完成，就像在内存列表中一样。不幸的是，您还会阻止DBMS直接对项目进行排序（至少没有笨拙且可能效率低下的递归查询） - 您必须在应用程序级别进行排序，所以我建议再次对它进行排序
And one column would be a huge text column that stores a sorted list of book ids.
一列是一个巨大的文本列，用于存储书籍ID的排序列表。
Please don't do that. You'd be violating the 1NF and there are very good reasons not to do that, including data consistency and performance (you'd have to rewrite the whole field for any single change to any portion of it).
请不要那样做。你违反了1NF并且有很好的理由不这样做，包括数据的一致性和性能（你必须重写整个字段，以便对它的任何部分进行任何单一更改）。
#3
5
Your current solution does not seem to work for multiple user settings. If the book's order is set in the Book table, wouldn't it be permanent for all users?
您当前的解决方案似乎不适用于多个用户设置。如果在Book表中设置了图书的订单，那么它对所有用户都不是永久性的吗？
As others have mentioned, its typically best to keep your data normalized, which would require you to add another table like you are suggesting. So you could have a new BookOrdering table. So it'd have a book_id, a user_id and position column. That way for every user and every book there is an assigned position.
正如其他人所提到的，通常最好将数据标准化，这需要您添加另一个表，就像您所建议的那样。所以你可以有一个新的BookOrdering表。所以它有一个book_id，一个user_id和position列。这样，每个用户和每本书都有一个指定的位置。
So there would be a default ordering (which would not be stored in this table), but users would have the ability to change the order. The table would only record changes from default. When you want to load the user's books, you'd first check this table for a certain user_id, and then shift/adjust the order accordingly.
因此会有一个默认排序（不会存储在此表中），但用户可以更改顺序。该表仅记录默认值的更改。如果要加载用户的书籍，首先要检查此表格中的某个user_id，然后相应地切换/调整顺序。
#4
0
1. Another Idea:
identify a new varchar column (orderCode) in your list table. This column store the order for each item in char format like bellow:
在列表中标识新的varchar列（orderCode）。此列以char格式存储每个项目的顺序，如下：
'1','2','3','4','5','6', '7', '8', '9', '91', '92',....
Now, if you want to insert an item between 1 and 2 , the value of orederCode will be '11'.And the list order will be:
现在，如果要插入1到2之间的项，则orederCode的值将为“11”。列表顺序为：
'1','11','2','3','4','5','6', '7', '8', '9', '91', '92',....
Now lets assume you will insert between 11 and 2, you will insert orderCode value = '12':
现在我们假设您将插入11和2之间，您将插入orderCode value ='12'：
'1','11','12','2','3','4','5','6', '7', '8', '9', '91', '92',....
Finally, if you want to insert item from '11' and '12' the order value will be:'111':
最后，如果要插入“11”和“12”中的项目，则订单值将为：'111'：
'1','11','111','12','2','3','4','5','6', '7', '8', '9', '91', '92',....'99','991,...
you can from time to time run a query to fix the order values to be one char.
您可以不时运行查询以将订单值修复为一个char。
2. Better solution:
use float point data type instead of varchar. So this list:
使用浮点数据类型而不是varchar。所以这个清单：
'1', '11', '111', '12', '2', '3', '4', '5', '6', '7', '8', '9', '91', '92',....
would be like bellow:
就像吼叫：
0.1, 0.11, 0.111, 0,12, 0.3, 0.4, 0.5, 0.6, ......,0.9,0.91,....
defiantly, the floating point sorting is faster than varchar sorting.
反之，浮点排序比varchar排序更快。
#1
4
It's not really hard to update all those rows in your example with a couple of SQL statements. You don't need to fire 11,000 updates at your DBMS (which I assume is what you were trying to say).
使用几个SQL语句更新示例中的所有行并不是很难。您不需要在DBMS上发出11,000个更新（我认为这是您想要说的）。
First, update all the books that are being shuffled forward one position:
首先，更新所有正在洗牌的书籍：
UPDATE book
SET position = position + 1
WHERE position < 11000
AND position >= 1
...and then set the position of the book you're moving:
...然后设置你正在移动的书的位置：
UPDATE book
SET position = 1
WHERE id = whatever
#2
8
You'd be surprised how fast a decent DBMS can update 11,000 rows, assuming you do it in a "bulk" fashion (as opposed to making a separate database round-trip for each row).
假设您以“批量”方式执行此操作（而不是为每行创建单独的数据库往返），您会惊讶于一个体面的DBMS可以快速更新11,000行。
But if you want to avoid that, use the old BASIC trick (from the time BASIC still had line numbers): leave gaps!
但是如果你想避免这种情况，那就使用旧的BASIC技巧（从BASIC的时候还有行号）：留下空白！
Instead of using positions: 1, 2, 3, 4, 5 etc... use 10, 20, 30, 40, 50 etc....
而不是使用位置：1,2,3,4,5等......使用10,20,30,40,50等....
So when you need to move (say) the first item to the next-to-last place, just modify 10 to 41 and you'll end-up with: 20, 30, 40, 41, 50 etc.... Obviously, you'll need to do some fiddling in case a gap gets completely filled, but this strategy should be able almost eliminate massive UPDATEs.
所以当你需要将第一个项目移动到倒数第二个位置时，只需修改10到41，你最终会得到：20,30,40,41,50等......显然，如果差距完全填满，你需要做一些摆弄，但这个策略应该能够几乎消除大量的UPDATE。
The other possibility is to implement a doubly-linked list: instead of order, keep an ID of the previous and the next item. Reordering can be done by simply "re-linking" the IDs, much as you would in an in-memory list. Unfortunately, you'd also prevent the DBMS from sorting the items directly (at least without awkward and probably inefficient recursive queries) - you'd have to do the sorting at the application level, so I'd recommend agains it
另一种可能性是实现双向链表：而不是订单，保留前一个和下一个项的ID。重新排序可以通过简单地“重新链接”ID来完成，就像在内存列表中一样。不幸的是，您还会阻止DBMS直接对项目进行排序（至少没有笨拙且可能效率低下的递归查询） - 您必须在应用程序级别进行排序，所以我建议再次对它进行排序
And one column would be a huge text column that stores a sorted list of book ids.
一列是一个巨大的文本列，用于存储书籍ID的排序列表。
Please don't do that. You'd be violating the 1NF and there are very good reasons not to do that, including data consistency and performance (you'd have to rewrite the whole field for any single change to any portion of it).
请不要那样做。你违反了1NF并且有很好的理由不这样做，包括数据的一致性和性能（你必须重写整个字段，以便对它的任何部分进行任何单一更改）。
#3
5
Your current solution does not seem to work for multiple user settings. If the book's order is set in the Book table, wouldn't it be permanent for all users?
您当前的解决方案似乎不适用于多个用户设置。如果在Book表中设置了图书的订单，那么它对所有用户都不是永久性的吗？
As others have mentioned, its typically best to keep your data normalized, which would require you to add another table like you are suggesting. So you could have a new BookOrdering table. So it'd have a book_id, a user_id and position column. That way for every user and every book there is an assigned position.
正如其他人所提到的，通常最好将数据标准化，这需要您添加另一个表，就像您所建议的那样。所以你可以有一个新的BookOrdering表。所以它有一个book_id，一个user_id和position列。这样，每个用户和每本书都有一个指定的位置。
So there would be a default ordering (which would not be stored in this table), but users would have the ability to change the order. The table would only record changes from default. When you want to load the user's books, you'd first check this table for a certain user_id, and then shift/adjust the order accordingly.
因此会有一个默认排序（不会存储在此表中），但用户可以更改顺序。该表仅记录默认值的更改。如果要加载用户的书籍，首先要检查此表格中的某个user_id，然后相应地切换/调整顺序。
#4
0
1. Another Idea:
identify a new varchar column (orderCode) in your list table. This column store the order for each item in char format like bellow:
在列表中标识新的varchar列（orderCode）。此列以char格式存储每个项目的顺序，如下：
'1','2','3','4','5','6', '7', '8', '9', '91', '92',....
Now, if you want to insert an item between 1 and 2 , the value of orederCode will be '11'.And the list order will be:
现在，如果要插入1到2之间的项，则orederCode的值将为“11”。列表顺序为：
'1','11','2','3','4','5','6', '7', '8', '9', '91', '92',....
Now lets assume you will insert between 11 and 2, you will insert orderCode value = '12':
现在我们假设您将插入11和2之间，您将插入orderCode value ='12'：
'1','11','12','2','3','4','5','6', '7', '8', '9', '91', '92',....
Finally, if you want to insert item from '11' and '12' the order value will be:'111':
最后，如果要插入“11”和“12”中的项目，则订单值将为：'111'：
'1','11','111','12','2','3','4','5','6', '7', '8', '9', '91', '92',....'99','991,...
you can from time to time run a query to fix the order values to be one char.
您可以不时运行查询以将订单值修复为一个char。
2. Better solution:
use float point data type instead of varchar. So this list:
使用浮点数据类型而不是varchar。所以这个清单：
'1', '11', '111', '12', '2', '3', '4', '5', '6', '7', '8', '9', '91', '92',....
would be like bellow:
就像吼叫：
0.1, 0.11, 0.111, 0,12, 0.3, 0.4, 0.5, 0.6, ......,0.9,0.91,....
defiantly, the floating point sorting is faster than varchar sorting.
反之，浮点排序比varchar排序更快。