mysql auto_increment: reset to 1?

[somaBoy MX]

I'd like to know how I can reset an "INT unsigned auto_increment" column to
start counting from 1.

I've done a couple of inserts to test the application, now I want to start
"clean".

.soma

[Tom Thackrey]

delete from <tablename>

should reset the auto_increment value.

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to james...@willglen.net (it's reserved for spammers)

[Andy Hassall]

On Sun, 09 Nov 2003 18:30:48 GMT, "Tom Thackrey" <use.si...@nospam.com>
wrote:

>
>On 9-Nov-2003, "somaBoy MX" <no...@nonesuch.net> wrote:
>
>> I'd like to know how I can reset an "INT unsigned auto_increment" column
>> to
>> start counting from 1.
>>
>> I've done a couple of inserts to test the application, now I want to start
>> "clean".
>
>delete from <tablename>
>
>should reset the auto_increment value.

Should and does; there's also another way to do it with ALTER TABLE:

mysql> create table autoinc (id int unsigned not null auto_increment primary
key);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into autoinc values (null), (null), (null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> show table status like 'autoinc'
-> \G
*************************** 1. row ***************************
Name: autoinc
Type: MyISAM
Row_format: Fixed
Rows: 3
Avg_row_length: 5
Data_length: 15
Max_data_length: 21474836479
Index_length: 2048
Data_free: 0
Auto_increment: 4 [ <--- here's the next value ]
Create_time: 2003-11-09 18:37:08
Update_time: 2003-11-09 18:37:19
Check_time: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> delete from autoinc;
Query OK, 0 rows affected (0.00 sec)

mysql> show table status like 'autoinc'
-> \G
*************************** 1. row ***************************
Name: autoinc
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 21474836479
Index_length: 1024
Data_free: 0
Auto_increment: 1 [ <--- the delete reset it ]
Create_time: 2003-11-09 18:37:40
Update_time: 2003-11-09 18:37:40
Check_time: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

mysql> insert into autoinc values (null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from autoinc;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

mysql> alter table autoinc auto_increment=100;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into autoinc values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from autoinc;
+-----+
| id |
+-----+
| 1 |
| 100 |
+-----+
2 rows in set (0.00 sec)

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

[somaBoy MX]

Thanks for the info, guys!

.soma

[Michael Fuhr]

Andy Hassall <an...@andyh.co.uk> writes:

> On Sun, 09 Nov 2003 18:30:48 GMT, "Tom Thackrey" <use.si...@nospam.com>
> wrote:
>
> >delete from <tablename>
> >
> >should reset the auto_increment value.
>
> Should and does; there's also another way to do it with ALTER TABLE:

This answer should be qualified: MySQL 4.x servers do NOT reset the
AUTO_INCREMENT value after "DELETE FROM tablename". They do, however
reset the value after "TRUNCATE TABLE tablename". Behold:

mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 4.0.16 |
+---------------+--------+

1 row in set (0.00 sec)

mysql> create table autoinc (id int unsigned not null auto_increment primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into autoinc values (null), (null), (null);

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from autoinc;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

mysql> delete from autoinc;
Query OK, 3 rows affected (0.02 sec)

mysql> insert into autoinc values (null), (null), (null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from autoinc;
+----+
| id |
+----+
| 4 |
| 5 |
| 6 |
+----+
3 rows in set (0.01 sec)

mysql> truncate table autoinc;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into autoinc values (null), (null), (null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from autoinc;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.01 sec)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/