MySQL AUTO_INCREMENT with Examples

Details

What is auto increment?

Auto Increment is a function that operates on numeric data types. It automatically generates sequential numeric values every time that a record is inserted into a table for a field defined as auto increment.

When use auto increment?

In the lesson on database normalization, we looked at how data can be stored with minimal redundancy, by storing data into many small tables ,related to each other using primary and foreign keys.

Auto increment syntax

Let's now look at the script used to create the movie categories table.

 
CREATE TABLE `categories` (
  `category_id` int(11) AUTO_INCREMENT,
  `category_name` varchar(150) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
);

 

Notice the "AUTO_INCREMENT" on the category_id field. This causes the category Id to be automatically generated every time a new row is inserted into the table. It is not supplied when inserting data into the table, MySQL generates it.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record

Let's examine the current contents of the categories table. 

SELECT * FROM `categories`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results.

Executing the above script against the myflixdb in MySQL workbench gives us the following results shown below.

Note we didn't supply the category id. MySQL automatically generated it for us because the category id is defined as auto increment.

If you want to get the last insert id that was generated by MySQL, you can use the LAST_INSERT_ID function to do that. The script shown below gets the last id that was generated. 

SELECT LAST_INSERT_ID();

Executing the above script gives the last Auto increment number generated by the INSERT query. The results are shown below.

YOU MIGHT LIKE: