MySQL PhpMyAdmin：更改 AUTO_INCRMENT 和/或 INSERT_ID
我有一个发票表，其中存储每张发票的一条记录，其中 id 列 (int AUTO_INCRMENT) 为主键，同时也是发票参考号。
现在，不幸的是，我不得不手动迁移旧系统上生成的一些发票，这些发票具有五位数字的 ID，而不是当前系统使用的四位数字。
但是，即使当我通过 PhpMyAdmin（表操作）将 AUTO_INCRMENT 重置回下一个四位 id 时，它仍然会插入一个五位数字 1，即表中当前较高的 id 加一。
通过搜索，我似乎实际上需要更改 insert_id 以及 AUTO_INCRMENT ？我尝试执行 ALTER TABLE Invoices SET insert_id=8125 以及 ALTER TABLE Invoices insert_id=8125 ，但这些命令似乎都无效。
谁能解释一下我可以重置 AUTO_INCRMENT 的正确方法，以便它将插入 id 为 8125 的记录，然后当它达到 10962 时> 它将跳过我手动添加的四个记录，并继续从 10966 开始的顺序 ID。如果它不会跳过 10962 - 10966 那么这并不重要，因为公司每年不会生成那么多发票，因此这会发生在因此希望明年不会造成问题。
我真的很感谢任何帮助我解决这个棘手的情况！非常感谢
I have an invoices table which stores a single record for each invoice, with the id column (int AUTO_INCREMENT) being the primary key, but also the invoice reference number.
Now, unfortunately I've had to manual migrate some invoices generated on an old system which have a five digit id, instead of a four digit one which the current system uses.
However, even when I reset the AUTO_INCREMENT through PhpMyAdmin (Table Operations) back to the next four digit id, it still inserts a five digit one being the higher id currently in the table plus one.
From searching around, it would seem that I actually need to change the insert_id as well as the AUTO_INCREMENT ? I've tried to execute ALTER TABLE invoices SET insert_id=8125 as well as ALTER TABLE invoices insert_id=8125 but neither of these commands seem to be valid.
Can anyone explain the correct way that I can reset the AUTO_INCREMENT so that it will insert records with id's 8125 onwards, and then when it gets to 10962 it will skip over the four records I've manually added and continue sequential id's from 10966 onwards. If it won't skip over 10962 - 10966 then this doesn't really matter, as the company doesn't generate that many invoices each year so this will occur in a subsequent year hence not causing a problem hopefully.
I would really appreciate any help with this sticky situation I've found myself in! Many Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我建议的第一件事是放弃 PHPMyAdmin，因为它是有史以来用于 MySQL 的最糟糕的“应用程序”之一。获取合适的 GUI。我最喜欢的是 SQLYog。
现在来解决这个问题。永远不要篡改主键，不要尝试按照您所说的那样“重置”它或更新具有数据库生成的整数的列。至于为什么，这个话题很广泛，可以在另一个问题中讨论，只是一旦你设置了它，就永远不要碰主键。
第二件事是有人删除了发票记录，因此自动增量现在为 10k+ 而不是 8k+。这并不是一件坏事，但如果您需要发票的连续值（例如发票 1 和发票 5 之间不能有间隙），则使用名为“sequence_id”或“invoice_ref”的额外字段，并使用触发器来计算该数字。不要依赖 auto_increment 功能，它会重用通过 DELETE 操作丢失的数字。
或者，您可以做的是导出您一直在使用的数据库，找到发票表的
CREATE TABLE定义，然后找到“AUTO_INCRMENT = [some number]”所在的行并删除那个声明。导入到新数据库中，自动增量将从最新发票继续。您可以使用 ALTER TABLE 执行相同操作，但重新导入更安全。First thing I'll suggest is to ditch PHPMyAdmin because it's one of the worst "applications" ever made to be used to work with MySQL. Get a proper GUI. My favourite is SQLYog.
Now on to the problem. Never, ever tamper with the primary key, don't try to "reset" it as you said or to update columns that have an integer generated by the database. As for why, the topic is broad and can be discussed in another question, just never, ever touch the primary key once you've set it up.
Second thing is that someone was deleting records of invoices hence the autoincrement is now at 10k+ rather than at 8k+. It's not a bad thing, but if you need sequential values for your invoices (such as there can't be a gap between invoices 1 and 5) then use an extra field called sequence_id or invoice_ref and use triggers to calculate that number. Don't rely on auto_increment feature that it'll reuse numbers that have been lost trough DELETE operation.
Alternatively, what you can do is export the database you've been using, find the
CREATE TABLEdefinition for the invoices table, and find the line where it says "AUTO_INCREMENT = [some number]" and delete that statement. Import into your new database and the auto_increment will continue from the latest invoice. You could do the same by using ALTER TABLE however it's safer to re-import.