MySQL AUTO_INCRMENT 计数器出现一些问题
我有一些使用带有自动增量功能的主键的MySQL表，这一切似乎都是在第一次插入记录时发生的，但是当我删除中间的任何记录时，它不会重置自动增量计数器，而是继续使用最后一个ID。
例如我已插入具有以下 id 的记录
1,2,3,4,5,6
当我在中间和下一次插入另一行时删除 5,6 时，它会从 7 继续。而我希望它从 5 继续。它只是不会重置 auto_increment 计数器，我尝试通过查询此命令ALTER TABLE Facilities AUTO_INCRMENT = 1;来修改表，但它似乎不起作用。
这是我从 phpMyAdmin Export 获得的其中一张表的示例 DDL。
CREATE TABLE `amenities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
i have some MySQL tables which uses primary key with auto_increment feature, it all seems while inserting the records for the first time, but when i delete any records in between it does not reset the auto_increment counter but continues to use the last id.
for example i have inserted the records with following id's
1,2,3,4,5,6
when i delete 5,6 in between and next time while i insert another row it continues from 7. whereas i want it to continue from 5. it just does not reset the auto_increment counter, i tried modifying the table by querying this command ALTER TABLE amenities AUTO_INCREMENT = 1; still it does not seems to work.
Here is the sample DDL of one of the tables i got from phpMyAdmin Export.
CREATE TABLE `amenities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这不是
AUTO_INCRMENT在 MySQL 或任何其他数据库中的工作方式。AUTO_INCRMENT始终向前。永远不要重新调整自己来填补空白。如果您需要所描述的行为，则需要编写自己的代码来处理该行为。在您的客户端程序中、在数据库中或您认为更好的任何地方。请记住，您需要很好地处理事务，以避免数据库负载较重时出现并发问题。
That's not how
AUTO_INCREMENTworks in MySQL or any other database.AUTO_INCREMENTalways goes forward. Never re accommodates itself to fill the gaps.If you need the behavior you are describing, you need to write your own code to handle that. In your client program, in the database or wherever you feel it's better. Just keep in mind that you will need to handle transactions very well in order to avoid concurrency problems when your database is on heavy load.