What is Auto Increment In MySQL?

What 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.

Examples

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:

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:

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.

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:

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:

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: