MYSQL Auto Increment

MYSQL Auto Increment is used to declare any field as auto increment. Which means that whenever a new record is inserted into the table this value will gets generated by incrementing its value in previous row. Often it is used as a primary key as it generated unique value for each record. 

Often when we create a new table. We use to keep an Id field thats purpose is to uniquely define each record. So we can keep this record is auto increment primary key as this will get auto generated by an incremented value each time a new record is inserted in it.

Syntax for declaring a column auto increment is

CREATE TABLE table_name (column_name1 auto increment , column_name2 data_type, column_name3 data_type);

By default starting value for auto increment field is 1. If we want to change the starting value for auto increment than syntax for that will be

CREATE TABLE table_name (column_name1 auto increment = 20 , column_name2 data_type, column_name3 data_type);

Let us explore this more with the help of example.

Consider Table student_list which contains details for student

+-----+------------------+--------+-----------+
| sid | name             | year   | hostel_id |
+-----+------------------+--------+-----------+
|   1 | Ankur Rastogi    | First  |         4 |
|   2 | Amit Kumar       | Second |         8 |
|   3 | Ram Kumar        | Second |         8 |
|   4 | Shiv Saxena      | First  |         6 |
|   5 | Aman Aggarwal    | Third  |         3 |
|   6 | Nipun Gupta      | Fourth |         2 |
|   7 | Arpit Gupta      | Third  |         1 |
|   8 | Shantanu Rastogi | Second |         3 |
|   9 | Ramesh Kumar     | Third  |         1 |
|  10 | Neelima Dhingra  | Second |      NULL |
|  11 | Anu Sibbal       | Third  |      NULL |
+-----+------------------+--------+-----------+

Let us see create table command for student_list using auto increment.

CREATE TABLE student_list (sid int(5) NOT NULL AUTO_INCREMENT, name varchar(200) NOT NULL, year varchar(10) NOT NULL, hostel_id tinyint(1) DEFAULT 0, PRIMARY KEY(sid));

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| sid       | int(5)       | NO   | PRI | NULL    | auto_increment |
| name      | varchar(200) | NO   |     | NULL    |                |
| year      | varchar(10)  | NO   |     | NULL    |                |
| hostel_id | tinyint(1)   | YES  |     | 0       |                |
+-----------+--------------+------+-----+---------+----------------+