===============
| id | name |
===============
| 1 | Ali |
| 2 | ahmd |
| 3 | jada |
| 4 | nisr |
| 5 | sara |
| 6 | mona |
| 7 | dana |
===============
我想在删除任何排这样的行时重新订购ID,然后像那样=>
===============
| id | name |
===============
| 1 | Ali |
| 2 | ahmd |
| 3 | jada |
| 4 | nisr |
| 5 | mona |
| 6 | dana |
===============
如果我插入具有ID = 2名称= yafa的行,如何在不重复任何值的情况下重新订购表,并且像那个
| id | name |
===============
| 1 | Ali |
| 2 | yafa |
| 3 | ahmd |
| 4 | jada |
| 5 | nisr |
| 6 | sara |
| 7 | mona |
| 8 | dana |
===============
这是假设您始终知道要添加或删除的'ID'。(另外,您总是一次仅使用一排)。
用于删除:
DELETE FROM tableName WHERE id = 5;
UPDATE tableName
SET id = id-1
WHERE id > 5;
插入:
UPDATE tableName
SET id = id + 1
WHERE id >= 2;
INSERT INTO tableName (id, name) VALUES (2, 'yafa');
顺便说一句,您为什么要这样做。将" ID"列设置为自动插入字段,并让数据库处理。您是否在乎您是否要跳过数字?