autoincrement reset

[Eric Kincl]

Hey,
I was wondering if it's possible to reset the "auto_increment " feature in
MySQL. For example, if I have a database with a list that will change
every year (the old list be completely flushed out, deleted) and then I
insert a new list, it will very quickly reach the max INDEX length... Does
it just wrap itself? Is there a way to reset "auto_increment " so that I
can flush all the rows, reset auto_increment, and start from scratch?

(Since it currently isn't very important I just go in with phpMyAdmin, copy
the table structure to a new table, delete the old table, and rename the
new table to what the old table was. Like I said, it's really not that
important of an application, but I like my INDEXes to start from 1 :-) )


-Eric Kincl

[Andy Hassall]

Re: autoincrement reset

On Fri, 21 Nov 2003 18:22:50 +0000, Eric Kincl <Eric@Kincl.net _NO_SPAM_> wrote:
[color=blue]
>I was wondering if it's possible to reset the "auto_increment " feature in
>MySQL.[/color]

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&c2coff=1&th=f9dc5216b247183b&seekm=3faefe38%241_2%40omega.dimensional.com&frame=off

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Alan Little]

Re: autoincrement reset

Carved in mystic runes upon the very living rock, the last words of Eric
Kincl of comp.lang.php make plain:
[color=blue]
> length... Does it just wrap itself? Is there a way to reset
> "auto_increment " so that I can flush all the rows, reset
> auto_increment, and start from scratch?[/color]

TRUNCATE [tablename]

will delete all rows and reset the auto_increment column. DELETE FROM will
not reset it.

--
Alan Little
Phorm PHP Form Processor

http://www.phorm.com/

[Matthew Crouch]

Re: autoincrement reset

It's true..do not Delete From

in phpMyAdmin, it's "Empty" to truncate

"Eric Kincl" <Eric@Kincl.net _NO_SPAM_> wrote in message
news:3fbe57fa@n ews.gvsu.edu...[color=blue]
> Hey,
> I was wondering if it's possible to reset the "auto_increment " feature in
> MySQL. For example, if I have a database with a list that will change
> every year (the old list be completely flushed out, deleted) and then I
> insert a new list, it will very quickly reach the max INDEX length... Does
> it just wrap itself? Is there a way to reset "auto_increment " so that I
> can flush all the rows, reset auto_increment, and start from scratch?
>
> (Since it currently isn't very important I just go in with phpMyAdmin,[/color]
copy[color=blue]
> the table structure to a new table, delete the old table, and rename the
> new table to what the old table was. Like I said, it's really not that
> important of an application, but I like my INDEXes to start from 1 :-) )
>
>
> -Eric Kincl[/color]

[Michael Fuhr]

Re: autoincrement reset

Alan Little <alan@n-o-s-p-a-m-phorm.com> writes:
[color=blue]
> Carved in mystic runes upon the very living rock, the last words of Eric
> Kincl of comp.lang.php make plain:
>[color=green]
> > length... Does it just wrap itself? Is there a way to reset
> > "auto_increment " so that I can flush all the rows, reset
> > auto_increment, and start from scratch?[/color]
>
> TRUNCATE [tablename]
>
> will delete all rows and reset the auto_increment column. DELETE FROM will
> not reset it.[/color]

This is true in MySQL 4.x, but in 3.x "DELETE FROM tablename" does
reset the table's Auto_increment value (at least it does on the
3.23.x servers I checked). See this recent thread for examples:

http://groups.google.com/groups?threadm=3fae83dc%240%2432180%24ba620e4c%40reader3.news.skynet.be

--
Michael Fuhr

http://www.fuhr.org/~mfuhr/

[Guille]

Re: autoincrement reset

You can reset the Auto_Increment value using ALTER TABLE.

ALTER TABLE [Table Name] AUTO_INCREMENT = 0