OldBlogger

Here are three ways to reset auto_increment in MySQL:

1) Drop the table and recreate it.
‘DROP TABLE myTable;’
‘CREATE TABLE myTable( … etc …’

2) Alter the auto_increment to a specified index
‘ALTER TABLE myTable AUTO_INCREMENT = 1;’

3) Truncate the table to delete all rows and reset auto_increment
‘TRUNCATE TABLE myTable’

The method you use would depend on exactly what you are trying to achieve, be warned if you use the wrong method for your situation you may end up deleting data you did not want to.

auto_increment is just there as a unique index value it does not matter if the first value used is 1 or 1000, so there is no need from MySQL’s perspective to reset it to 1 if you DELETE all the rows in a table, unless of course your application needs to or you want to.

Some MySQL tips:

Running a shell script from within MySQL:

mysql> \! /user/myscript

Running a script of MySQL commands from within MySQL:

mysql> source /user/myscript;

Running MySQL from within a shell script and running commands to create a database and user:

mysql -u$ROOTUSER -p$ROOTPASSWD << CREATE_DB
CREATE DATABASE $DBNAME;
GRANT ALL PRIVILEGES ON $DBNAME.* TO "$DBUSER"@"localhost" IDENTIFIED BY "$DBPASSWD";
FLUSH PRIVILEGES;
CREATE_DB

Running MySQL from shell script or command line and passing a single command

mysql -u root -p -e STATUS | grep -i uptime