{"id":10903,"date":"2023-10-10T00:27:03","date_gmt":"2023-10-10T07:27:03","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=10903"},"modified":"2024-03-31T02:56:56","modified_gmt":"2024-03-31T09:56:56","slug":"mysql-auto_increment","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-auto_increment\/","title":{"rendered":"MySQL AUTO_INCREMENT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL AUTO_INCREMENT<\/code> attribute to automatically generate unique integer values for a column.<\/p>\n\n\n\n

Introduction to MySQL AUTO_INCREMENT attribute<\/h2>\n\n\n\n

In MySQL, you use the AUTO_INCREMENT<\/code> attribute to automatically generate unique integer values for a column whenever you insert a new row into the table.<\/p>\n\n\n\n

Typically, you use the AUTO_INCREMENT<\/code> attribute for the primary key<\/a> column to ensure each row has a unique identifier.<\/p>\n\n\n\n

Creating a table with MySQL AUTO_INCREMENT column<\/h3>\n\n\n\n

To create a table<\/a> with an auto-increment column, you use the AUTO_INCREMENT<\/code> attribute:<\/p>\n\n\n

CREATE TABLE table_name(\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    ...\n);<\/code><\/span><\/pre>\n\n\n
For example, the following statement creates a table called contacts to store contact data:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> contacts(\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    email VARCHAR<\/span>(320<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we assign the AUTO_INCREMENT<\/code> attribute to the id<\/code> column to set it as an auto-increment primary key. <\/p>\n\n\n\n
This means that when you insert a new row into the contacts<\/code> table without providing a value for the id<\/code> column, MySQL will automatically generate a unique number.<\/p>\n\n\n\n
Inserting rows with AUTO_INCREMENT column<\/h3>\n\n\n\n
When inserting rows into the table with an AUTO_INCREMENT<\/code> column, you don’t need to specify a value for that column. MySQL will automatically generate the value for you. For example:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> contacts(name<\/span>, email)\nVALUES<\/span>('John Doe'<\/span>, 'john.doe@mysqltutorial.org'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the INSERT<\/code> statement, we don’t specify a value for the id<\/code> column and only provide the values for the name<\/code> and email <\/code>columns. MySQL automatically generated the value 1 for the id<\/code> column:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+----------+----------------------------+<\/span>\n| id | name     | email                      |\n+----+----------+----------------------------+<\/span>\n|  1 | John Doe | john.doe@mysqltutorial.org |\n+----+----------+----------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Retrieving the last auto-increment value<\/h3>\n\n\n\n
To get the AUTO_INCREMENT<\/code> value that MySQL generated for the most recent insert, you use the LAST_INSERT_ID()<\/code> function:<\/p>\n\n\n
SELECT<\/span> LAST_INSERT_ID<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------+<\/span>\n| LAST_INSERT_ID() |\n+------------------+<\/span>\n|                1 |\n+------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query returns the last auto-increment value generated for the ID<\/code> column, which you can use for other purposes such as inserting into a related table.<\/p>\n\n\n\n
Resetting the current auto-increment value<\/h3>\n\n\n\n
To reset the AUTO_INCREMENT<\/code> value, you use the ALTER<\/code> TABLE<\/code> statement:<\/p>\n\n\n
ALTER TABLE table_name \nAUTO_INCREMENT = value;<\/code><\/span><\/pre>\n\n\n
Note that the ALTER<\/code> TABLE<\/code> statement takes effect only if the value<\/code> that you want to reset to is higher than or equal to the maximum value in the AUTO_INCREMENT<\/code> column of the table_name<\/code>.<\/p>\n\n\n\n
For example, the following statement reset the current auto-increment value to 1:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> contacts \nAUTO_INCREMENT = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Alternatively, you can delete all rows from the table and reset the AUTO_INCREMENT<\/code> value simultaneously. To do that, you use the TRUNCATE TABLE<\/a><\/code> statement:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example illustrates how to reset the value in the AUTO_INCREMENT<\/code> column to an invalid value:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> contacts(name<\/span>, email) \nVALUES<\/span>\n   ('John Doe'<\/span>, 'john.doe@mysqltutorial.org'<\/span>),\n   ('Jane Doe'<\/span>, 'jane.doe@mysqltutorial.org'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The contacts<\/code> table now has two rows:<\/p>\n\n\n
+----+----------+----------------------------+<\/span>\n| id | name     | email                      |\n+----+----------+----------------------------+<\/span>\n|  1 | John Doe | john.doe@mysqltutorial.org |\n|  2 | Jane Doe | jane.doe@mysqltutorial.org |\n+----+----------+----------------------------+<\/span>\n2 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you reset the AUTO_INCREMENT<\/code> column to any number that is less than or equal to 2 using the ALTER<\/code> TABLE<\/code> statement, the operation will have no effects. For example:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> contacts \nAUTO_INCREMENT = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, if you insert a new row into the contacts table, MySQL will use the next number 3 for the new row. For example:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> contacts(name<\/span>, email) \nVALUES<\/span>('Bob Climo'<\/span>, 'bob.climo@mysqltutorial.org'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query returns all rows of the contacts<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+-----------+-----------------------------+<\/span>\n| id | name      | email                       |\n+----+-----------+-----------------------------+<\/span>\n|  1 | John Doe  | john.doe@mysqltutorial.org  |\n|  2 | Jane Doe  | jane.doe@mysqltutorial.org  |\n|  3 | Bob Climo | bob.climo@mysqltutorial.org |\n+----+-----------+-----------------------------+<\/span>\n3 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Adding an AUTO_INCREMENT column to an existing table<\/h3>\n\n\n\n
To add an AUTO_INCREMENT<\/code> to an existing table, you use the ALTER<\/code> TABLE<\/code> statement. For example:<\/p>\n\n\n\n
First, create a new table without an AUTO_INCREMENT<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> subscribers(\n   email VARCHAR<\/span>(320<\/span>) NOT<\/span> NULL<\/span> UNIQUE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, add the column id<\/code> to the subscribers<\/code> table as an AUTO_INCREMENT<\/code> column:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> subscribers\nADD<\/span> id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Assign the AUTO_INCREMENT<\/code> attribute to a column to instruct MySQL to automatically generate unique integer values for the column.<\/li>\n<\/ul>\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