更新事物列表而不点击每个条目

假设 @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);

我使用MySQL5.1.52在您的示例数据上测试了这一点,它工作正常。如果您需要将早期条目移动到较迟的位置,或者移动中间的条目等,相同的SQL也可以工作。只需设置 @旧 和 @新 .

我最终使用了 adjacencies table . 当时我不知道。

我不确定这是否算是一个解决方案,但实际上您不需要对每行进行一次更新。如果你把“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位置,更新的次数也是一样的(当然,你需要翻转它),你只需要大量移动所有受影响的行加或减1

您可以在一个这样的更新语句中执行此操作:

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 使用所选值。但是,我认为这种类型的排序最好在客户机应用程序中完成,而不是在数据库中完成。

如果您使用的是SQL Server,那么UUU可以使用链接列表表示和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

我想你的桌子上有一把主键, 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。

1. 如前所述,除非您必须向所有用户显示给定用户正在影响的当前顺序,否则我建议您首先在客户机中处理此问题(有许多方法可以解决此问题),然后根据用户操作(例如,按“我已完成”按钮),使用您选择存储在客户机中的结构的最终订单。

2. 您可以使客户机中的代码尽可能复杂,以尽量减少需要在数据库中更新的行数:在某些情况下,您可能只需要插入一行(如果用户在列表末尾插入一个新项);在许多情况下,您可能需要更新两行(如果用户只交换两个连续项)。根据需要更新的行数,最糟糕的情况是所有行(您可以设置一个算法,该算法只检测需要更新的行,并只更新这些行)。无论是值得这样做,还是只发布所有行的更新,您都可以选择。

3. 归根结底,您不需要更新数据库中的所有行,这种情况只是许多可能的情况之一。有些数据库允许批量更新(名称可能因数据库而异),这不会非常昂贵。

如果列只是指定了行的顺序,我看不出使用0和负数有什么问题。