I seem to recall old versions of MySQL did re-use auto-increment values but this was changed since it's not really supposed to do that ;-)
Cheers Andrew. ----- Original Message ----- From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Joe Wong" <[EMAIL PROTECTED]>; "Egor Egorov" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, June 23, 2004 7:53 PM Subject: Re: question on auto increment field > At 17:33 +0800 6/23/04, Joe Wong wrote: > >Hi Egor, > > > > Thanks for your reply. In addition to this, how I can make MySQL to reuse > >the number which has been deleted? I tried to do a test as follow > > AUTO_INCREMENT columns never automatically generate numbers that are > less that the maximum value currently in the column. If you want to > reuse numbers, you'll have to handle this in your application logic. > > > > > >1. Create a dummy table with a auto increment field 'UID' set to MED INT > >2. Manually insert a record that set UID to Max of MED INT, ie 16777215 > >3. Insert another record without specifying the value of UID > > > >At 3, it failed and said: > >Duplicate entry '16777215' for key 1 > > > >But I have only 1 record in the table. > > > >Regards, > > > >- Wong > > > > > > > >----- Original Message ----- > >From: "Egor Egorov" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Sent: Wednesday, June 23, 2004 4:10 PM > >Subject: Re: question on auto increment field > > > > > >> Scott Haneda <[EMAIL PROTECTED]> wrote: > >> > on 06/23/2004 12:14 AM, Joe Wong at [EMAIL PROTECTED] wrote: > >> > > >> >> If it possible to limit the max value of an auto increment field to > >say, > >> >> 9999,9999 and how to do it? I am using MySQL 4.0.18. > >> > >> No. The maximum value for the auto_increment column can be limited by the > >maximum value of the column type(tinyint, int, mediumint etc.). > >> > >> > > >> > I am not sure, as a 'hack' you could simply insert a blank record with > >the > >> > value set to 9999,9999, once you reach that limit and try to insert a > >> > record, a error would be generated, no new records would be able to be > >> > inserted until this was resolved. > >> > >> It will not work, because if you insert dummy row and set auto_increment > >value to 9999, the next generated auto_increment value will be 10000. > > > -- > Paul DuBois, MySQL Documentation Team > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
