Resetting Auto-increment
Is there a better way to reset the auto_increment in a table, basically there are several million rows in the database and the field that is auto_increment is very large now and I don't want to exceed the limit of the field description so I want to renumber all the rows starting at 1 again. What I found online was this: For those that are looking to reset the auto_increment, say on a list that has had a few deletions and you want to renumber everything, you can do the following. DROP the field you are auto_incrementing. ALTER the table to ADD the field again with the same attributes. You will notice that all existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1. So is there an easier / better way to do this? Also would the way this is done be different if the table is empty?? I have an empty table that when I add a record, starts at some number based on how many have been entered and deleted. Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message.
Re: Resetting Auto-increment
On Tue, 24 May 2005 [EMAIL PROTECTED] wrote: Is there a better way to reset the auto_increment in a table, basically there are several million rows in the database and the field that is auto_increment is very large now and I don't want to exceed the limit of the field description so I want to renumber all the rows starting at 1 again. What I found online was this: For those that are looking to reset the auto_increment, say on a list that has had a few deletions and you want to renumber everything, you can do the following. DROP the field you are auto_incrementing. ALTER the table to ADD the field again with the same attributes. You will notice that all existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1. So is there an easier / better way to do this? Also would the way this is done be different if the table is empty?? I have an empty table that when I add a record, starts at some number based on how many have been entered and deleted. In the case that the table is empty the information here http://dev.mysql.com/doc/mysql/en/set-option.html should let you do what you want. I don't know the best way to 're-index' existing data. Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Resetting Auto Increment Count
Hello, I need to reset the auto increment value on a database. Can anyone help. Thanks Brian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Resetting Auto Increment Count
At 2:31 PM +0100 8/21/01, Brian Leyland wrote: Hello, I need to reset the auto increment value on a database. Can anyone help. Thanks Brian If it's a MyISAM table and you mean that you want to reset the sequence so that MySQL will next use the smallest currently unused value, do this: ALTER TABLE tbl_name AUTO_INCREMENT = 0 If you mean something else, please specify. -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
