MySql: Auto_Increment Column: All About Insert Values

Let's say we want to create a table in which each row will get unique id while inserting new row, means while we're inserting new row that particular column-value will be automatically inserted with unique integer value.
In this scenario we can use "AUTO_INCREMENT" column type.

-- drop table if exists
DROP TABLE IF EXISTS `TEST_AUTO_INCREMENT`

-- create a dummy table
CREATE TABLE TEST_AUTO_INCREMENT
  (
     Id INT AUTO_INCREMENT PRIMARY KEY
     , FirstName CHAR(40)
  )

-- AUTO_INCREMENT is applicable only with following data-types (integral data-types)
  TINYINT
  SMALLINT
  MEDIUMINT
  INT
  BIGINT

  DOUBLE

-- we can insert auto-increment values in different ways

-- If want to use the database generated vaules

  either don't include the AUTO_INCREMENT column into the insert statement
 
INSERT INTO TEST_AUTO_INCREMENT (FirstName) VALUES ('SriRam');

  or if we're considering that column too then use (0, '', NULL, DEFAULT) key-word or SET qualifier

INSERT INTO TEST_AUTO_INCREMENT (Id, FirstName) VALUES ('','Ram');
INSERT INTO TEST_AUTO_INCREMENT (Id, FirstName) VALUES (DEFAULT,'Shyam');
INSERT INTO TEST_AUTO_INCREMENT (Id, FirstName) VALUES (NULL,'Mohan');
INSERT INTO TEST_AUTO_INCREMENT (Id, FirstName) VALUES (0,'Sohan');

INSERT INTO TEST_AUTO_INCREMENT SET FirstName='ShyamMohan-1';

-- If we want to use the self-specified values
INSERT INTO TEST_AUTO_INCREMENT VALUES (11,'Krishna');
INSERT INTO TEST_AUTO_INCREMENT SET Id = 20, FirstName='ShyamMohan-2';

After executing all these statements we can see the result like this.

  

In next section we'll discuss how to reset the increment value, should we use LAST_INSERT_ID()
or not, how does auto_increment behave after restarting MySql instance, what happen if column's max size got exceeded (e.g. if we're using tinyint as column data-type and trying to insert values as 234).