What is Auto Increment In MySQL?
Build an AI-First Career, Master the Complete Skillset
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
AI Forward Deployed Engineer Program
Full-stack engineering, production AI and client-facing consulting
+1000 moreWhat is Auto Increment in MySQL?
Auto Increment is a feature in MySQL that automatically assigns a unique value to a field each time a new record is inserted into a table. The AUTO_INCREMENT attribute applies to a numeric column, and it guarantees that each new record will have a unique, sequential value that is one greater than the previous value. This feature is particularly useful when creating primary keys for tables, as it simplifies the process of generating unique values for each new record.
This article will explore how to create sequences using auto increment in MySQL and its syntax and provide examples to illustrate its usage. We will also discuss how to set the starting value for the auto-incremented sequence.
How to Create Sequences Using the AUTO_INCREMENT?
To create a sequence using Auto Increment in MySQL, you must define a column with a numeric data type, such as INT or BIGINT, and set the AUTO_INCREMENT attribute on that column. When inserting a new record into the table, the database automatically generates a unique value for that column by incrementing the previous value by one.
Syntax
The syntax for creating a table with an auto-incremented column is as follows:
Syntax :
In this syntax, table_name is the name of the table you wish to create, column1 is the name of the column you wish to make auto-incremented, and datatype is the data type of the column. You can include additional columns in the table by listing them after column1, separated by commas.
For example, let's say we want to create a table named employees with an auto-incremented column named id and additional columns for first_name, last_name, and email. We would use the following query:
Query to create employees table with auto increment property
This creates a new table named employees with four columns: id, first_name, last_name, and email. The id column is set to AUTO_INCREMENT, which means that each time a new record is inserted into the employee's table, MySQL will automatically generate a unique value for the id column.
How Scaler Transformed Careers in Different Fields
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreExamples
Let's look at some examples of how to use auto increment in MySQL.
Example 1: Inserting Records into a Table with an Auto-Incremented Primary Key
Suppose we want to insert some records into the employees table we created in the previous example. To insert a new record, we would use the following syntax:
Query :
This statement inserts a new record into the employees table with the values 'Vineet' for first_name, 'Bansal' for last_name, and 'vineet@gmail.com' for email. The id column is automatically generated by MySQL, based on the previous value of id.
To view the contents stored in the employees table, use the following query:
Query :
Output:
| id | first_name | last_name | |
|---|---|---|---|
| 1 | Vineet | Bansal | vineet@gmail.com |
If we were to insert another record without specifying a value for id, MySQL would automatically generate a new, unique value for id:
To view the contents stored in the employees table, use the following query:
Query:
Output:
| id | first_name | last_name | |
|---|---|---|---|
| 1 | Vineet | Bansal | vineet@gmail.com |
| 2 | Deepak | Goyal | deepak@gmail.com |
In this case, MySQL would assign a value of 2 to the id column since it is one greater than the previous value of 1.
Turn Learning into Career Growth
Example 2: Retrieving the Auto-Generated Value for a Newly Inserted Record
If you need to retrieve the auto-generated value for a newly inserted record, you can use the LAST_INSERT_ID() function in MySQL. This function returns the value of the auto-incremented column for the most recent INSERT statement performed on the current connection.
For example, if we wanted to retrieve the id value for the most recently inserted record in the employee's table, we would use the following syntax:
Query:
Output:
This statement returns the value of the id column for the most recent record inserted into the employee's table. You can use this value to perform additional operations on the record, such as updating or deleting it.
Set Starting Value for Auto Increment in MySQL
By default, auto-increment in MySQL starts the sequence at 1 and increments by 1 for each new record. However, using the syntax discussed below, you can set a custom starting value for the auto-incremented sequence.
For example, if we wanted to set the starting value of the id column in the employees table to 100, we can use the following bunch of queries:
Queries:
Let us add a few records to the employees table to test whether the value of the id column starts from 100 or not. Insert a new record into the employees table using the following syntax:
Query:
This statement inserts a new record into the employees table with the values 'Rahul' for first_name, 'Bansal' for last_name, and 'rahul@scaler.com' for email. The id column will take the value 100 in this case which is the new default value which we have set.
To view the contents stored in the employees table, use the following query:
Query:
Output:
| id | first_name | last_name | |
|---|---|---|---|
| 100 | Rahul | Bansal | rahul@scaler.com |
If we were to insert another record without specifying a value for id, MySQL would automatically generate a new, unique value for id:
To view the contents stored in the employees table, use the following query:
Query:
Output:
| id | first_name | last_name | |
|---|---|---|---|
| 100 | Rahul | Bansal | rahul@scaler.com |
| 101 | Harsh | Gupta | harsh@scaler.com |
In this case, MySQL would assign a value of 101 to the id column since it is one greater than the previous value of 100.
Ques: Can you guess what would be the output of the LAST_INSERT_ID() function in this case?
Output:
Conclusion
- Auto increment in MySQL is a powerful feature that simplifies the process of generating unique values for numeric columns, such as primary keys.
- By setting the AUTO_INCREMENT attribute on a column, you can ensure that each new record has a unique, sequential value that is one greater than the previous value.
- Auto increment in MySQL is particularly useful when working with large datasets, as it allows you to easily manage primary keys and perform operations on individual records.
