SQL中存储记录顺序的最佳方法是什么？

Question

我有一个用户资料表。每个用户可以拥有多个资料，而用户可以调整它们在网格中的显示顺序。

有两个表：用户和资料（1:M）。

我向用户表添加了一个orderby列，其中将包含类似1、2、3的值。

到目前为止，这似乎还不错。但是当用户将最后一条记录的顺序更改为第一条时，我必须遍历所有记录并对它们的值进行递增+1。这对我来说似乎非常丑陋。

这种情况下是否有更方便的解决方案？

- user137348

Answer 1

在序列中留下空隙或使用十进制而不是整数数据类型。

- Martin Smith

---

使用字符串（varchar，text）。字符串隐式具有无限的“任意精度”。https://dev59.com/3Gkw5IYBdhLWcg3w9vJ-#49956113 - Alexander Bird

Answer 2

最佳解决方案应该是反映功能的简单整数列表。保持列表有序只需要几个SQL语句，并且比其他解决方案（浮点数，间隔整数）更易于理解。

如果您的列表非常大（数万个），则可能需要考虑性能问题，但我认为这些列表不会那么长。

- egrunin

Answer 3

如何在“order by”列中使用浮点数呢？这样，您就可以始终在两个其他值之间挤入一个配置文件，而无需更改这两个值。例如，如果我想将配置文件A放置在配置文件B（ordervalue 1）和C（ordervalue 2）之间，我可以为A分配ordervalue 1.5。要将其置于顶部，在此之前顶部的ordervalue为1，您可以使用ordervalue 0.5。没有理由使用整数进行排序，也没有理由在配置文件的顺序之间增加1。

- iniju

---

假设你有3条记录，你为它们分配编号1、2、3。然后将最后一条记录重新排序，使其位于前两条记录之间，并给它分配1.5的编号。然后重复此过程，每次你都会得到更接近1的数字，如1.25、1.125等，直到达到精度限制，这很快就会发生，大约在50次迭代后。然后你就会遇到一个非常讨厌的错误... :( - Hrissan

Answer 4

如果数据集很小（似乎是这种情况），我更喜欢使用普通的整数列表，并在个人资料获得新位置时批量更新它们。这更好地反映了应用程序功能。

在 Sql Server 中，对于以下表格 User_Profiles (user_id, profile_id, position)，我会有类似以下的内容：

--# The variables are: 
--#   @user_id - id of the user
--#   @profile_id - id of the profile to change
--#   @new_position - new position that the profile will take
--#   @old_position - current position of the profile 

select @old_position = position 
from User_Profiles where 
user_id = @user_id and profile_id = @profile_id

update p set position = pp.new_position
from User_Profiles p join (
  select user_id, profile_id,
    case 
    when position = @old_position then @new_position 
    when @new_position > @old_position then --# move up
      case 
      when @old_position < position and 
           position <= @new_position 
      then position - 1
      else position
      end
    when @new_position < @old_position then --# move down
      case 
      when position < @old_position and 
           @new_position <= position 
      then position + 1
      else position
      end
    else position --# the same
    end as new_position
  from User_Profiles p where user_id = @user_id
) as pp on 
p.user_id = pp.user_id and p.profile_id = pp.profile_id

- Jordão

Answer 5

当用户添加个人资料时，将每个新个人资料的排序编号设置为前一个+1000000。例如，开始时：

p1   1000000
p2   2000000
p3   3000000

当重新排序时，将配置文件的顺序设置为它要插入的两个配置文件的中间位置。

p1   1000000
p2   2000000
p3   1500000

这将得到顺序为p1、p3、p2。

- fredley

Answer 6

我认为，你可以在设计中引入链接列表的概念，而不是仅仅在orderby列中保持顺序。添加一个类似于nextId的列，其中包含链中的下一个个人资料。 当你查询profiles表时，可以在你的代码（Java、C#等）中对个人资料进行排序。

- Alexander

Answer 7

我认为在订单之间留下空隙的想法很有趣，但我不知道它是否是您问题的“更方便”的解决方案。

我认为您最好只更新您的order by列。因为您仍然需要确定状态之间移动了哪些行，以及如果两个状态在位置上交换该怎么办（您是先计算第一个的新排序值还是第二个的新排序值）。如果之间的间隔不够大会发生什么？

只需枚举他们放置的顺序并将每个记录更新到该顺序应该不会占用太多数据。

- msarchet