php - Mysql查询重新索引项目

Question

我有一个如下表，它有一个 ID 和项目名称、顺序和类型。

  +--------+----------------+-----------+---------+
  |  ID    |  ITEM          |   ORDER   |  TYPE   |
  +--------+----------------+-----------+---------+
  |  1     |  Banana        |    2      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  2     |  Apple         |    1      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  3     |  Orange        |    4      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  4     |  Lemon         |    3      |  Fruit  |
  +--------+----------------+-----------+---------+

如果我要删除 say Lemon，则 ORDER 将为 1,2,4。有没有一种方法可以重新索引剩余的项目以获得以下结果？

  +--------+----------------+-----------+---------+
  |  ID    |  ITEM          |   ORDER   |  TYPE   |
  +--------+----------------+-----------+---------+
  |  1     |  Banana        |    2      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  2     |  Apple         |    1      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  3     |  Orange        |    3      |  Fruit  |
  +--------+----------------+-----------+---------+

Answer 1

在删除 Lemon 行之前 - 检查它的 ORDER 列值是什么。

在那之后执行

UPDATE tbl SET `ORDER` = `ORDER` - 1 WHERE `ORDER` > value_lemon_had