{"id":495,"date":"2012-12-14T12:53:15","date_gmt":"2012-12-14T12:53:15","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=495"},"modified":"2023-12-31T02:22:53","modified_gmt":"2023-12-31T09:22:53","slug":"mysql-reset-auto-increment-value","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-reset-auto-increment-value\/","title":{"rendered":"MySQL Reset Auto Increment Values"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, we\u00a0 will show you various ways to reset the auto-increment values of AUTO_INCREMENT<\/code> columns in MySQL.<\/p>\n\n\n\n

MySQL provides you with a useful feature called auto-increment<\/a>. You can assign the AUTO_INCREMENT<\/code> attribute to a column of a table to generate a unique identity for the new row. Typically, you use the AUTO_INCREMENT<\/code> attribute for the primary key<\/a> column of the table.<\/p>\n\n\n\n

Whenever you insert a new row into a table<\/a>, MySQL automatically\u00a0assigns a sequence number to the AUTO_INCREMENT<\/code> column.<\/p>\n\n\n\n

For example, if the table has eight rows and you insert a new row without specifying the value for the auto-increment column, MySQL will automatically insert a new row with id<\/code> value 9.<\/p>\n\n\n\n

Sometimes, you may need to reset the value of the auto-increment column so that the first record’s identity that you insert into the table starts from a specific number.<\/p>\n\n\n\n

In MySQL, you can reset auto-increment values in various ways.<\/p>\n\n\n\n

MySQL reset auto-increment value examples<\/h2>\n\n\n\n

First, create a table<\/a> named items<\/code> and\u00a0assign the\u00a0AUTO_INCREMENT<\/code> attribute to the\u00a0id<\/code> \u00a0primary key column.<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> items (\n  id<\/span> INT<\/span> AUTO_INCREMENT,\n  name<\/span> VARCHAR<\/span>(45<\/span>) NOT<\/span> NULL<\/span>,\n  PRIMARY KEY<\/span> (id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> items(name<\/span>)\nVALUES<\/span>('Item 1'<\/span>),\n      ('Item 2'<\/span>),\n      ('Item 3'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * FROM<\/span> items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+--------+\r\n| id | name   |\r\n+----+--------+\r\n|  1<\/span> | Item 1<\/span> |\r\n|  2<\/span> | Item 2<\/span> |\r\n|  3<\/span> | Item 3<\/span> |\r\n+----+--------+\r\n3<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
We have three rows with values of ID column 1, 2, and 3. Perfect! It is time to practice resetting the auto-increment value of the ID column.<\/p>\n\n\n\n
1) Using ALTER TABLE statement<\/h3>\n\n\n\n
You can reset the auto-increment value by using the ALTER TABLE<\/a><\/code> statement. The syntax of the ALTER TABLE<\/code> \u00a0statement to reset the auto-increment value\u00a0is as follows:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name \nAUTO_INCREMENT = value<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the table name after the ALTER TABLE<\/code> clause and the value<\/code> which\u00a0you want to reset to in the expression AUTO_INCREMENT=value<\/code>.<\/p>\n\n\n\n
Notice that the value<\/code> must be greater than or equal to the current maximum value of the auto-increment column.<\/p>\n\n\n\n
DELETE<\/span> FROM<\/span> items\nWHERE<\/span> id<\/span> = 3<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you insert a new row, MySQL will assign 4 to the\u00a0id<\/code> column\u00a0of the new row. However, you can reset the number generated by MySQL to 3.<\/p>\n\n\n\n
Second, reset the auto-increment value to 3 using the ALTER TABLE<\/code> \u00a0statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> items AUTO_INCREMENT = 3<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, insert a new row into the items<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> items(name<\/span>)\nVALUES<\/span> ('Items 4'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, retrieve data from the items table:<\/p>\n\n\n
SELECT * FROM items;<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+---------+\r\n| id | name    |\r\n+----+---------+\r\n|  1<\/span> | Item 1<\/span>  |\r\n|  2<\/span> | Item 2<\/span>  |\r\n|  3<\/span> | Items 4<\/span> |\r\n+----+---------+\r\n3<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
We have three rows with the last auto-increment value is 3 instead of 4, which is what we expected.<\/p>\n\n\n\n
2) Using the TRUNCATE TABLE statement<\/h3>\n\n\n\n
The TRUNCATE TABLE<\/a> statement removes all the data from a table and resets the auto-increment value to zero.<\/p>\n\n\n\n
The following illustrates the syntax of the TRUNCATE TABLE<\/code>  statement:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
By using the TRUNCATE TABLE<\/code> \u00a0statement, you delete all data from the table permanently and reset the auto-increment value to zero.<\/p>\n\n\n\n
For example:<\/p>\n\n\n
TRUNCATE TABLE items;<\/code><\/span><\/pre>\n\n\n
3) Using DROP \u00a0TABLE and CREATE TABLE statements<\/h3>\n\n\n\n
You can use a pair of statements: DROP TABLE<\/a> and CREATE TABLE<\/a> to reset the auto-increment column. Note that this method deletes all data from the table permanently.<\/p>\n\n\n\n
Like the TRUNCATE TABLE<\/code> \u00a0statement, those statements drop the\u00a0table and recreate it, therefore, the value of the auto-increment is reset to zero. For example:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> items;\n\nCREATE<\/span> TABLE<\/span> items (\n  id<\/span> INT<\/span> AUTO_INCREMENT,\n  name<\/span> VARCHAR<\/span>(45<\/span>) NOT<\/span> NULL<\/span>,\n  PRIMARY KEY<\/span> (id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned various methods to reset auto-increment values in MySQL. The ALTER TABLE AUTO_INCREMENT = value<\/code> statement is preferable as it is the easiest and has no side effects.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
First, delete<\/a> the last record in the items<\/code> table with id<\/code>\u00a0value 3:<\/p>\n\n\n
Third, query<\/a> the items<\/code> \u00a0table to verify the insert operation:<\/p>\n\n\n
Second, insert<\/a> some sample data into the items<\/code> \u00a0table:<\/p>\n\n\n