重新排序 MySQL 表中的 id 列? [英] Re-sorting id column in a MySQL table?

问题描述

我有一个列名 id 和 PRIMARY KEY 和 AUTO INCREMENT 设置的表.当在表格中途删除一行时，即第 1 - 70 行中的第 29 行，id 29 将直接消失.是否有可能使行向上移动，但 id 保留在那里，即删除 70 而不是 29，以便具有 id 30 的行现在将采用 29、31 将采用 30 等?

I have a table with the column name id and the settings of both PRIMARY KEY and AUTO INCREMENT. When a row is deleted mid-way through the table, i.e. row 29 in rows 1 - 70, id 29 will simply disappear. Is it possible to have it so that the rows shift up, but the id remains there, i.e. deleting 70 instead of 29, so that the row that had id 30 will now adopt 29, 31 will adopt 30 etc?

如果可能的话，在我的情况下，这将使在表中插入新数据时容易得多.

This will make it a lot easier when inserting new data into the table in my case if this is possible.

我真的希望在创建新值之前先用完这些值，所以要么这样做会很棒，要么寻找缺失值并更新它们.

I'm really looking to have the values used up before a new one is created, so it's either this which would be great, or looking for missing values and updating them.

更新:

我想说的是，获取某一行上方的行，并且如果所有 id 列都按直值顺序(没有间隙)，那么我就可以相对轻松地做到这一点.

I wanted this to say, get the row above a certain row, and if all the id columns were in straight value order (no gaps) then I'd be able to do it relatively easily.

推荐答案

对已编辑问题的回答

以前的 ID:

SELECT id FROM $mytable WHERE id < $id ORDER BY ID DESC LIMIT 1; 

下一个 ID:

SELECT id FROM $mytable WHERE id > $id ORDER BY ID ASC LIMIT 1;

这篇关于重新排序 MySQL 表中的 id 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！