mysql - MySQL自然排序

背景：
我有一个小组的想法，那是一个项目的集合。每个项都由两个属性定义：它们所属的组及其在组中的位置。
表结构

CREATE TABLE group (
id INT AUTO_INCREMENT,
--other fields
PRIMARY KEY(id)
) ENGINE = MyISAM;

CREATE TABLE items (
group_refid INT NOT NULL, --points to id in group table
group_pos SMALLINT AUTO_INCREMENT,
--other fields
PRIMARY KEY(group_refid, group_pos)
) ENGINE = MyISAM;

所以现在插入条目效果很好。我要做的就是：

INSERT INTO items (group_refid) VALUES (1);

项目将附加到组的末尾。当我想移动其中一个项目时，我的问题就出现了。移动项目的工作方式如下：
例子：
我有三个项目在位置1，2，3，4，但我想移动一个在位置3到位置1。
期望结果：3,1,2,4，但表中它们的group_pos应为1,2,3,4或1,2,3,5
我想这样做是为了保持元素（除了被移动的元素）的顺序。在一个SQL语句中有这样做的好方法吗？我更喜欢一个语句，因为我不能使用事务，所以除了使用表锁之外，一个一次性语句是使它成为原子的唯一方法。

最佳答案

试试这样的方法，将group_pos变成一个float而不是smallint。
试试这个sqlFiddle在小提琴中，我把@position设置为1以获得第二个位置，如果你想它是列表中的第一个，你可以尝试把它设置为0。

SET @position = 1;
UPDATE items SET group_pos =
(SELECT SUM(group_pos)/2 as newGroupPos FROM
   (SELECT 0 as group_pos,0 as rank
    UNION
    SELECT group_pos,@rank:=@rank+1 as rank FROM items,(SELECT @rank:=0)var
    WHERE group_refid=1
    ORDER BY group_pos
   )T1 WHERE rank IN (@position,@position+1)
)
WHERE group_refid=1 AND group_pos=3;

关于mysql - MySQL自然排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22154655/