更新事物列表而不触及每个条目

Question

我在数据库中有一个用户应该能够订购的列表。

itemname|  order value (int)
--------+---------------------         
salad   |  1
mango   |  2
orange  |  3
apples  |  4

从数据库加载时，我只需按 order_value 排序。

通过拖放，他应该能够移动苹果，使其出现在列表的顶部。

itemname|  order value (int)
--------+---------------------         
apples  |  4
salad   |  1
mango   |  2
orange  |  3

好的。所以现在我必须在内部更新每个列表项！如果列表有 20 或 100 个项目，那么对于简单的拖动操作来说，这就是大量更新。

itemname|  order value (int)
--------+---------------------         
apples  |  1
salad   |  2
mango   |  3
orange  |  4

我宁愿只用一次更新来做到这一点。我想到的一种方法是“内部订单”是否是一个 double 值。

itemname|  order value (double)
--------+---------------------         
salad   |  1.0
mango   |  2.0
orange  |  3.0
apples  |  4.0

因此，在拖放操作之后，我指定 apples 的值小于它出现在前面的项目：

itemname|  order value (double)
--------+---------------------         
apples  |  0.5
salad   |  1.0
mango   |  2.0
orange  |  3.0

..并且如果一个项目被拖到中间某处，它的order_value 比它后面出现的那个大。这里我将 orange 移动到 salad 和 mango 之间：

itemname|  order value (double)
--------+---------------------         
apples  |  0.5
salad   |  1.0
orange  |  1.5
mango   |  2.0

关于更好的方法来做到这一点有什么想法吗？

Answer 1

假设 @old 是苹果旧位置的值 4，而 @new 是新位置 1。

set @old = 4;
set @new = 1;

UPDATE Items
SET `order value` = 
  CASE `order value` WHEN @old THEN @new
  ELSE `order value` + SIGN(@old-@new) END
WHERE `order value` BETWEEN LEAST(@old, @new) AND GREATEST(@old, @new);

我使用 MySQL 5.1.52 在示例数据上测试了这一点它有效。如果您需要将较早的条目移至较晚的条目，或将中间的条目移至较晚的条目等，则相同的 SQL 也适用。只需设置 @old 和 @new 的值即可。

Answer 2

我最终使用了 邻接表。我当时并不知道这件事。

Answer 3

我不确定这是否算作一种解决方案，但您实际上并不需要为每一行进行一次更新。如果你将 'foo' 从位置 4 移动到位置 1，

UPDATE table SET position = 1 WHERE itemname = 'foo'
UPDATE table SET position = position + 1 WHERE itemname != 'foo' AND position < 4

即使你从位置 1000 移动到 500，或者从 500 移动到 1000，你也只是做同样数量的更新（尽管你自然需要翻转它） ），您只需将所有受影响的行批量移动加或减一

Answer 4

您可以在单个 Update 语句中执行此操作，如下所示：

Update Table
Set OrderValue = Case
                    When Table.ItemName = 'apples' Then 0
                    Else    (
                            Select Count(*)
                            From Table As T1
                            Where T1.ItemName <> 'apples'
                                And T1.OrderValue < Table.OrderValue
                            ) + 1
                    End + 1

显然，您可以将 apples 替换为所选值。但是，我认为这种类型的排序最好在客户端应用程序中完成，而不是在数据库中完成。

Answer 5

如果您使用的是 SQL Server，您可以使用链接列表表示和 CTE 来完成此操作。我不知道 mysql 是否支持 CTE...

SET NOCOUNT ON
GO

DROP TABLE [Item]
GO

CREATE TABLE [Item]
(
    [ItemId] int NOT NULL PRIMARY KEY,
    [Name] varchar(100) NOT NULL,
    [PreviousId] int NULL
)
GO

INSERT [Item] VALUES (6, 'apples', 3)
INSERT [Item] VALUES (3, 'orange', 36)
INSERT [Item] VALUES (9, 'mango', 100)
INSERT [Item] VALUES (100, 'salad', NULL)
INSERT [Item] VALUES (36, 'banana', 9)
GO

;WITH
[LinkedItem] AS
(
    SELECT
        [Item].*,
        1 AS [OrderValue]
    FROM [Item]
    WHERE [Item].[PreviousId] IS NULL
    UNION ALL
    SELECT
        [Item].*,
        [LinkedItem].[OrderValue] + 1
    FROM [Item]
        INNER JOIN [LinkedItem] ON [LinkedItem].[ItemId] = [Item].[PreviousId]
)
SELECT *
FROM [LinkedItem]
ORDER BY
    [LinkedItem].[OrderValue]

-- Drag orange up two spaces
DECLARE @MovingItemId int
DECLARE @NewPreviousId int
SET @MovingItemId = 3
SET @NewPreviousId = 100

DECLARE @OldPreviousId int
SELECT @OldPreviousId = [PreviousId] FROM [Item] WHERE [ItemId] = @MovingItemId
UPDATE [Item] SET [PreviousId] = @OldPreviousId WHERE [PreviousId] = @MovingItemId
UPDATE [Item] SET [PreviousId] = @MovingItemId WHERE [PreviousId] = @NewPreviousId
UPDATE [Item] SET [PreviousId] = @NewPreviousId WHERE [ItemId] = @MovingItemId

这会产生以下前后结果：

100 salad  NULL    1
9   mango  100 2
36  banana  9   3
3   orange  36  4
6   apples  3   5

100 salad   NULL    1
3   orange  100 2
9   mango   3   3
36  banana  9   4
6   apples  36  5

Answer 6

我想您的表上有一个主键，即 id 列。
这两个语句应该可以。

update table set order_value=0 where itemname='apples';
update 
(select @num := 0 )vars
straight_join 
(select id, @num := @num+1 as ord_value
from table 
order by order_value
)big
inner join table t on t.id = big.id
set t.order_value = big.ord_value;

如果您没有 id，请使用 itemname 代替。

Answer 7

1. 正如之前所建议的，除非您必须向所有用户显示给定用户正在影响的当前订单，否则我建议您首先在客户端中处理这个问题（有很多方法可以解决这个问题），然后，根据用户操作（例如，按“我完成”按钮），您可以使用您选择存储在客户端中的结构中的最终顺序更新数据库中的行。

2. 您可以使客户端中的代码变得尽可能复杂，以尽量减少数据库中需要更新的行数：在某些情况下，您可能只需要插入一行（如果用户插入列表末尾的新项目）；在许多情况下，您可能需要更新两行（如果用户只是交换两个连续的项目）。根据需要更新的行数，最糟糕的情况是所有行（您可以设计一种算法，仅检测需要更新的行并只更新这些行）。您可以选择是否值得这样做或只是发布所有行的更新。

3. 最重要的是，您不需要更新数据库中的所有行，这种情况只是许多可能的情况之一。有些数据库允许批量更新（名称可能因数据库而异），这不会很昂贵。

Answer 8

如果列只是指定行的顺序，我认为使用 0 和负数没有任何问题。