MySql，删除后更新计数器，无间隙

I have a mySql table like this (simplified)

我有一个这样的mySql表(简化)

Id*| Text |  Pos (integer)
-----------
A  | foo  |  0
B  | bar  |  1
C  | baz  |  2
D  | qux  |  3

Now, after I delete a row, I want to update the Pos value on the remaining rows so that no "holes" or gaps are to be found.

现在，在删除一行之后，我想要更新其余行的Pos值，这样就不会找到“漏洞”或空白。

For example if I row with Id='C' is deleted, the remaing table should be:

例如，如果删除Id='C'的行时，remaing表应为:

Id*| Text |  Pos (integer)
-----------
A  | foo  |  0
B  | bar  |  1
D  | qux  |  2

Is this possible in a single query?

这在单个查询中可行吗?

UPDATE Based on the accepted answer this was the solution to my problem:

更新基于公认的答案这是我的问题的解决方案:

START TRANSACTION;
SELECT @A:=pos FROM table_name WHERE Id= 'C';
DELETE FROM table_name WHERE Id = 'C';
UPDATE table_name SET Pos = Pos - 1 WHERE Pos > @A;
COMMIT;

2 个解决方案

#1

You can achieve this by creating a AFTER DELETE TRIGGER on table,

您可以通过在表上创建一个AFTER DELETE触发器来实现这一点，

or by using transactions:

或通过使用事务:

START TRANSACTION;

SELECT Pos 
INTO @var_pos
FROM table_name
WHERE id = 'C';

DELETE 
FROM table_name
WHERE id = 'C';

UPDATE table_name
SET Pos = Pos - 1
WHERE Pos > @var_pos;

COMMIT;

#2

I think this should work, (I haven't tested it) you can run this statement after any delete

我认为这应该是可行的(我还没有测试它)您可以在任何删除之后运行这个语句

update t set t.Pos=a.iterator
from tablename t 
join(
     SELECT @i:=@i+1 AS iterator, t.id
     FROM tablename t,(SELECT @i:=0) r)a
on a.id=t.id

#1