MySQL – Reset Auto-Increment


MySQL – Reset Auto-Increment


Table of content


”;


Previous

Next

Most of the tables in MySQL use sequential values to represent records, like serial numbers. Instead of manually inserting each value one by one, MySQL uses the “AUTO_INCREMENT” to handle this automatically.

AUTO-INCREMENT in MySQL

AUTO_INCREMENT in MySQL is used to generate unique numbers in ascending order automatically as you add new records to a table. It is very useful for applications that require each row to have a distinct value.

When you define a column as an AUTO_INCREMENT column, MySQL takes care of the rest. It starts with the value 1 and increments it by 1 for each new record you insert, creating a sequence of unique numbers for your table.

Example

The following example demonstrates the usage of AUTO_INCREMENT on a column in database table. Here, we are creating a table named ”insect” with AUTO_INCREMENT applied to the ”id” column.


CREATE TABLE insect (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (id),
   name VARCHAR(30) NOT NULL,
   date DATE NOT NULL,
   origin VARCHAR(30) NOT NULL
);

Now, you don”t need to manually specify values for the ”id” column when inserting records. Instead, MySQL handles it for you, starting with 1 and incrementing by 1 for each new record. To insert values in other columns of the table, use the following query −


INSERT INTO insect (name,date,origin) VALUES
(''housefly'',''2001-09-10'',''kitchen''),
(''millipede'',''2001-09-10'',''driveway''),
(''grasshopper'',''2001-09-10'',''front yard'');

The insect table displayed is as follows. Here, we can see that the ”id” column values are automatically generated by MySQL −






id name date origin
1 housefly 2001-09-10 kitchen
2 millipede 2001-09-10 driveway
3 grasshopper 2001-09-10 front yard

The MySQL RESET Auto-Increment

The default AUTO_INCREMENT values on a table start from 1, i.e., the values being inserted usually start from 1. However, MySQL also has a provision to reset these AUTO-INCREMENT values to another number, enabling the sequence to start inserting from the specified reset value.

You can reset the AUTO_INCREMENT value in three ways: using ALTER TABLE, TRUNCATE TABLE, or dropping and recreating the table.

RESET using ALTER TABLE Statement

The ALTER TABLE statement in MySQL is used to update a table or make any alterations in it. Hence, using this statement to reset an AUTO_INCREMENT value is perfectly valid choice.

Syntax

Following is the syntax to reset autoincrement using ALTER TABLE −


ALTER TABLE table_name AUTO_INCREMENT = new_value;

Example

In this example, we are using the ALTER TABLE statement to reset the AUTO_INCREMENT value to 5. Note that the new AUTO_INCREMENT value be greater than the number of records already present in the table −


ALTER TABLE insect AUTO_INCREMENT = 5;

Following is the output obtained −


Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, let us insert another value into the table ”insect” created above and check the new result-set, using the following queries −


INSERT INTO insect (name,date,origin) VALUES 
(''spider'', ''2000-12-12'', ''bathroom''),
(''larva'', ''2012-01-10'', ''garden'');

We get the result as shown below −


Query OK, 2 row affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

To verify whether the new records you inserted will start with the AUTO_INCREMENT value set to 5, use the following SELECT query −


SELECT * FROM insect;

The table obtained is as shown below −








id name date origin
1 housefly 2001-09-10 kitchen
2 millipede 2001-09-10 driveway
3 grasshopper 2001-09-10 front yard
5 spider 2000-12-12 bathroom
6 larva 2012-01-10 garden

RESET using TRUNCATE TABLE Statement

Another way to reset auto-incrementing column to the default value is by using the TRUNCATE TABLE command.
This will delete the existing data of a table, and when you insert new records, the AUTO_INCREMENT column starts from the beginning (usually 1).

Example

Following is an example to reset the AUTO_INCREMENT value to default, i.e. ”0”. For that, firstly truncate the ”insect” table created above using the TRUNCATE TABLE Command as follows −


TRUNCATE TABLE insect;

The output obtained is as follows −


Query OK, 0 rows affected (0.04 sec)

To verify whether the records of the table is deleted, use the following SELECT query −


SELECT * FROM insect;

The result produced is as follows −


Empty set (0.00 sec)

Now, insert values again using the following INSERT statement.


INSERT INTO insect (name,date,origin) VALUES
(''housefly'',''2001-09-10'',''kitchen''),
(''millipede'',''2001-09-10'',''driveway''),
(''grasshopper'',''2001-09-10'',''front yard''),
(''spider'', ''2000-12-12'', ''bathroom'');

After executing the above code, we get the following output −


Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

You can verify whether the records in the table have been reset using the following SELECT query −


SELECT * FROM insect;

The table displayed is as follows −







id name date origin
1 housefly 2001-09-10 kitchen
2 millipede 2001-09-10 driveway
3 grasshopper 2001-09-10 front yard
4 spider 2000-12-12 bathroom




Resetting Auto-Increment Using Client Program

We can also reset auto-increment using client program.

Syntax


PHP
NodeJS
Java
Python

To reset auto-increment through a PHP program, we need to execute the “ALTER TABLE” statement using the mysqli function query() as follows −


$sql = "ALTER TABLE INSECT AUTO_INCREMENT = 5";
$mysqli->query($sql);

To reset auto-increment through a JavaScript program, we need to execute the “ALTER TABLE” statement using the query() function of mysql2 library as follows −


sql = "ALTER TABLE insect AUTO_INCREMENT = 5";
con.query(sql)

To reset auto-increment through a Java program, we need to execute the “ALTER TABLE” statement using the JDBC function execute() as follows −


String sql = "ALTER TABLE insect AUTO_INCREMENT = 5";
statement.execute(sql);

To reset auto-increment through a Python program, we need to execute the “ALTER TABLE” statement using the execute() function of the MySQL Connector/Python as follows −


reset_auto_inc_query = "ALTER TABLE insect AUTO_INCREMENT = 5"
cursorObj.execute(reset_auto_inc_query)

Example

Following are the programs −


PHP
NodeJS
Java
Python 


$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$db = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
//lets create a table
$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
if($mysqli->query($sql)){
    printf("Insect table created successfully....!n");
}
//now lets insert some records
$sql = "INSERT INTO insect (name,date,origin) VALUES (''housefly'',''2001-09-10'',''kitchen''), (''millipede'',''2001-09-10'',''driveway''), (''grasshopper'',''2001-09-10'',''front yard'')";
if($mysqli->query($sql)){
    printf("Records inserted successfully....!n");
}
//display table records
$sql = "SELECT * FROM INSECT";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Date: %s, Origin: %s", $row[''id''], $row[''name''], $row[''date''], $row[''origin'']);
        printf("n");
    }
}
//lets reset the autoincrement using alter table statement...
$sql = "ALTER TABLE INSECT AUTO_INCREMENT = 5";
if($mysqli->query($sql)){
    printf("Auto_increment reset successfully...!n");
}
//now lets insert some more records..
$sql = "INSERT INTO insect (name,date,origin) VALUES (''spider'', ''2000-12-12'', ''bathroom''), (''larva'', ''2012-01-10'', ''garden'')";
$mysqli->query($sql);
$sql = "SELECT * FROM INSECT";
if($result = $mysqli->query($sql)){
    printf("Table records(after resetting autoincrement): n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Date: %s, Origin: %s", $row[''id''], $row[''name''], $row[''date''], $row[''origin'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as shown below −


Insect table created successfully....!
Records inserted successfully....!
Table records: 
Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard
Auto_increment reset successfully...!
Table records(after resetting autoincrement):
Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard
Id: 5, Name: spider, Date: 2000-12-12, Origin: bathroom
Id: 6, Name: larva, Date: 2012-01-10, Origin: garden    




var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL);"
    con.query(sql);

    sql = "INSERT INTO insect (name,date,origin) VALUES (''housefly'',''2001-09-10'',''kitchen''),(''millipede'',''2001-09-10'',''driveway''),(''grasshopper'',''2001-09-10'',''front yard'');"
    con.query(sql);

    sql = "SELECT * FROM insect;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Records of INSECT Table:**");
      console.log(result);
      console.log("--------------------------");
    });

    sql = "ALTER TABLE insect AUTO_INCREMENT = 5";
    con.query(sql);

    sql = "INSERT INTO insect (name,date,origin) VALUES (''spider'', ''2000-12-12'', ''bathroom''), (''larva'', ''2012-01-10'', ''garden'');"
    con.query(sql);

    sql = "SELECT * FROM insect;"
    con.query(sql, function(err, result){
      console.log("**Records after modifying the AUTO_INCREMENT to 5:**");
      if (err) throw err
      console.log(result);
    });
});

Output

The output obtained is as shown below −

 
Connected!
--------------------------
**Records of INSECT Table:**
[
  {id: 1,name: ''housefly'',date: 2001-09-09T18:30:00.000Z,origin: ''kitchen''},
  {id: 2,name: ''millipede'',date: 2001-09-09T18:30:00.000Z,origin: ''driveway''},
  {id: 3,name: ''grasshopper'',date: 2001-09-09T18:30:00.000Z,origin: ''front yard''}
]
--------------------------
**Records after modifying the AUTO_INCREMENT to 5:**
[
  {id: 1,name: ''housefly'',date: 2001-09-09T18:30:00.000Z,origin: ''kitchen''},
  {id: 2,name: ''millipede'',date: 2001-09-09T18:30:00.000Z,origin: ''driveway''},
  {id: 3,name: ''grasshopper'',date: 2001-09-09T18:30:00.000Z,origin: ''front yard''},
  {id: 5,name: ''spider'',date: 2000-12-11T18:30:00.000Z,origin: ''bathroom''},
  {id: 6,name: ''larva'',date: 2012-01-09T18:30:00.000Z,origin: ''garden''}
]    



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ResetAutoIncrement {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
            st.execute(sql);
            System.out.println("Table insect created successfully....!");
            //lets insert some records into it
            String sql1 = "INSERT INTO insect (name,date,origin) VALUES (''housefly'',''2001-09-10'',''kitchen''), (''millipede'',''2001-09-10'',''driveway''), (''grasshopper'',''2001-09-10'',''front yard'')";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //let print table records
            String sql2 = "SELECT * FROM insect";
            rs = st.executeQuery(sql2);
            System.out.println("Table records(before resetting auto-increment): ");
            while(rs.next()) {
                String name = rs.getString("name");
                String date = rs.getString("date");
                String origin = rs.getString("origin");
                System.out.println("Name: " + name + ", Date: " + date + ", Origin: " + origin);
            }
            //lets reset auto increment using ALTER table statement...
            String reset = "ALTER TABLE INSECT AUTO_INCREMENT = 5";
            st.execute(reset);
            System.out.println("Auto-increment reset successsfully...!");
            //lets insert some more records..
            String sql3 = "INSERT INTO insect (name,date,origin) VALUES (''spider'', ''2000-12-12'', ''bathroom''), (''larva'', ''2012-01-10'', ''garden'')";
            st.execute(sql3);
            System.out.println("Records inserted successfully..!");
            String sql4 = "SELECT * FROM insect";
            rs = st.executeQuery(sql4);
            System.out.println("Table records(after resetting auto-increment): ");
            while(rs.next()) {
                String name = rs.getString("name");
                String date = rs.getString("date");
                String origin = rs.getString("origin");
                System.out.println("Name: " + name + ", Date: " + date + ", Origin: " + origin);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Output

The output obtained is as shown below −


Table insect created successfully....!
Records inserted successfully...!
Table records(before resetting auto-increment): 
Name: housefly, Date: 2001-09-10, Origin: kitchen
Name: millipede, Date: 2001-09-10, Origin: driveway
Name: grasshopper, Date: 2001-09-10, Origin: front yard
Auto-increment reset successsfully...!
Records inserted successfully..!
Table records(after resetting auto-increment): 
Name: housefly, Date: 2001-09-10, Origin: kitchen
Name: millipede, Date: 2001-09-10, Origin: driveway
Name: grasshopper, Date: 2001-09-10, Origin: front yard
Name: spider, Date: 2000-12-12, Origin: bathroom
Name: larva, Date: 2012-01-10, Origin: garden     



import mysql.connector
# Establishing the connection 
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object 
cursorObj = connection.cursor()
# Creating the ''insect'' table
create_table_query = ''''''
CREATE TABLE insect (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id),
    name VARCHAR(30) NOT NULL,
    date DATE NOT NULL,
    origin VARCHAR(30) NOT NULL
);
''''''
cursorObj.execute(create_table_query)
print("Table ''insect'' is created successfully!")
# Inserting records into the ''insect'' table
insert_query = "INSERT INTO insect (Name, Date, Origin) VALUES (%s, %s, %s);"
values = [
    (''housefly'', ''2001-09-10'', ''kitchen''),
    (''millipede'', ''2001-09-10'', ''driveway''),
    (''grasshopper'', ''2001-09-10'', ''front yard'')
]
cursorObj.executemany(insert_query, values)
print("Values inserted successfully!")
# Displaying the contents of the ''insect'' table
display_table_query = "SELECT * FROM insect;"
cursorObj.execute(display_table_query)
results = cursorObj.fetchall()
print("ninsect Table:")
for result in results:
    print(result)
# Resetting the auto-increment value of the ''id'' column
reset_auto_inc_query = "ALTER TABLE insect AUTO_INCREMENT = 5;"
cursorObj.execute(reset_auto_inc_query)
print("Auto-increment value reset successfully!")
# Inserting additional records into the ''insect'' table
insert_query = "INSERT INTO insect (name, date, origin) VALUES (''spider'', ''2000-12-12'', ''bathroom'');"
cursorObj.execute(insert_query)
print("Value inserted successfully!")
insert_again_query = "INSERT INTO insect (name, date, origin) VALUES (''larva'', ''2012-01-10'', ''garden'');"
cursorObj.execute(insert_again_query)
print("Value inserted successfully!")
# Displaying the updated contents of the ''insect'' table
display_table_query = "SELECT * FROM insect;"
cursorObj.execute(display_table_query)
results = cursorObj.fetchall()
print("ninsect Table:")
for result in results:
    print(result)
# Closing the cursor and connection
cursorObj.close()
connection.close()

Output

The output obtained is as shown below −


Table ''insect'' is created successfully!
Values inserted successfully!

insect Table:
(1, ''housefly'', datetime.date(2001, 9, 10), ''kitchen'')
(2, ''millipede'', datetime.date(2001, 9, 10), ''driveway'')
(3, ''grasshopper'', datetime.date(2001, 9, 10), ''front yard'')
Auto-increment value reset successfully!
Value inserted successfully!
Value inserted successfully!

insect Table:
(1, ''housefly'', datetime.date(2001, 9, 10), ''kitchen'')
(2, ''millipede'', datetime.date(2001, 9, 10), ''driveway'')
(3, ''grasshopper'', datetime.date(2001, 9, 10), ''front yard'')
(5, ''spider'', datetime.date(2000, 12, 12), ''bathroom'')
(6, ''larva'', datetime.date(2012, 1, 10), ''garden'')

Leave a Reply

Your email address will not be published. Required fields are marked *