Bug #6556 Alternative to max(id) for auto_increment

• Submitted: 10 Nov 2004 17:48
• Modified: 25 Jan 2005 0:47
• Reporter: Winfried
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S4 (Feature request)
• CPU Architecture: Any

[10 Nov 2004 17:48] Winfried

Description:
auto_increment uses the maxium id of a table. In most cases this is what you want. But 
sometimes you want to re-use id's, which have been freed by deleting rows. 

How to repeat:
delete rows from a table 

Suggested fix:
The following statements return the minimum free id greater than 0 on the table www_news. 0 
can be replaced with min(__id)-1 to preserve the starting index. The isnull() clause is never 
true, so that the @cnt variable is always increased. The arithmetic operation (__id-__id) is 
needed to prevent optimization.  
 
select @cnt:=0; 
select @cnt from www_news where not isnull(__id + (@cnt:=@cnt+1) -__id) and (__id-@cnt) > 
0 group by __id limit 1; 
 
The following statements re-order the index  column of the table www_news. 0 can be 
replaced with min(__id)-1 to preserve the starting index. Use with care, this breaks all id-based 
references (e.g. URIs in search engines). Also, you need the order by clause for update to 
guarantee uniqueness on the column (otherwise the update may fail half-way, leaving a big 
mess). 
 
select @cnt:=0; 
update www_news set __id = @cnt where not isnull(__id + (@cnt:=@cnt+1) -__id)  order by 
__id;

[25 Jan 2005 0:47] Paul DuBois

To renumber the column, it's easier to use ALTER TABLE
to drop the column and re-add it.

To reset the counter back down to the next unused value,
use ALTER TABLE tbl_name AUTO_INCREMENT = 1.