您的位置: 首页  >  技术问答  >  我可以自动调整数据库中自动递增字段的值吗?

我可以自动调整数据库中自动递增字段的值吗?

分类: 技术问答 • 2022-03-29 14:56:36

我可以自动调整数据库中自动递增字段的值吗?

问题描述：

我可以自动调整数据库中自动递增字段的值吗?

Can i adjust the value of an auto-incremented field in the database automatically?

我有一个名为"post"的表，该表具有一个名为"pid"的字段，该字段设置为自动递增.用户可以稍后删除该表中的帖子，但不会调整自动增加的值.每次删除帖子时，有没有办法调整pid字段?

I have a table called "post" which has a field called "pid" which is set to auto-increment. Posts from this table may be deleted by the user at a later time, but the auto- incremented value will not be adjusted. Is there a way to adjust the pid field everytime posts are deleted?

例如:如果我有4个条目:pid = 1,2,3,4(pid-auto-increment)现在，如果我删除2，是否有办法将3更新为2，将4更新为3，依此类推?

for eg:If i have 4 entries: pid=1,2,3,4(pid-auto-increment) Now if i delete 2, is there a way to update 3 to 2 and 4 to 3 and so on ?

答

从结尾删除

您可以通过以下方式将表的AUTO_INCREMENT手动设置为指定值:

Deletion from end

You can manually set AUTO_INCREMENT of a table to a specified value via

ALTER TABLE tbl AUTO_INCREMENT = val;

请参见在MySQL中使用AUTO_INCREMENT 手册.

这解决了从结尾删除的问题-在添加新行之前，将AUTO_INCREMENT设置为0，它将自动设置为当前最大值加1.新插入的行将与已删除的行占据相同的ID.

This solves deletion from end – before adding new rows, set AUTO_INCREMENT to 0 and it will be automatically set to current maximum plus one. Newly inserted rows will occupy the same IDs as the deleted ones.

可以手动指定具有AUTO_INCREMENT的字段的值.AUTO_INCREMENT被忽略.如果指定已经使用的值，则唯一约束将中止查询.如果您指定的值大于当前最大值，则AUTO_INCREMENT会自动设置为此一加一.

It is possible to manually specify value of the field having AUTO_INCREMENT. AUTO_INCREMENT is ignored them. If you specify a value already used, unique constraint will abort the query. If you specify a value that is bigger than the current maximum, AUTO_INCREMENT automatically set to this one plus one.

如果不想手动重新编号记录，请为此编写脚本，也不要弄乱用户定义的变量:

If you do not want to manually renumber the records, write a script for that, nor mess with stored procedures, you can use user-defined variables:

SET @id = 0;
UPDATE tbl SET id = @id := @id + 1 ORDER BY id;
SET @alt = CONCAT('ALTER TABLE tbl AUTO_INCREMENT = ', @id + 1);
PREPARE aifix FROM @alt;
EXECUTE aifix;
DEALLOCATE PREPARE aifix;

示例用法

有关更多信息，请参见我对相关问题的回答.

For more info see my answer to a related question.

通常不需要重新编号记录.实际上，这可能是有害的，因为您可能在某处(可能在数据库外部)悬挂了对旧记录的引用，并且它们现在又重新变得有效，这可能会引起混乱.这就是为什么删除行后表的AUTO_INCREMENT属性不会递减的原因.

Usually there is no need to renumber the records. Actually it may be harmful as you may have dangling references to the old record somewhere (possibly outside the DB) and they now become valid again, which could cause confusion. This is why AUTO_INCREMENT attribute of the table is not decremented after a row is deleted.

您应该删除不需要的记录，而不必担心漏洞.它们只是记录的编号，纯粹是逻辑上的，从物理上讲，它们不需要存在于存储中.从长远来看，不会浪费任何空间.一段时间以来，存储确实有漏洞.您可以通过 来使数据库引擎摆脱它们.优化表tbl 或 ALTER TABLE tbl ORDER BY列 .

You should just delete the unwanted records and stop worrying about the holes. They are just in the numbering of the records, purely logical, physically they don’t need to exist in the storage. No space wasted in the long time perspective. For some time the storage really has holes. You can let the DB engine get rid of them by OPTIMIZE TABLE tbl or ALTER TABLE tbl ORDER BY column.

相关推荐