Posted In: Database, MySQL

MySQL AUTO_INCREMENT column

In case you worked most of life on Oracle DB you must be wondering where is the SEQUENCE. AUTO_INCREMENT column in MySQL is used for primary key column. For each insert MySQL will automatically update the column to next max value. For each new row, MySQL increments the value automatically and persist it to table.

 

How to reset MySQL AUTO_INCREMENT column

Please remember this reset is very heavy operation and should be run carefully.

Case 1 – Developer wants to truncate the table and then reset back auto increment value.


TRUNCATE TABLE `javausecase_db`.`user_master`;
AUTO_INCREMENT will be automatically reset

Case 2 – Developer wants to delete the table data and then reset back auto increment value.


DELETE TABLE `javausecase_db`.`user_master`;
ALTER TABLE table_name AUTO_INCREMENT = 1;

Case 3 – Developer wants to drop the table and then recreate table


DROP TABLE `javausecase_db`.`user_master`;
CREATE TABLE `user_master`

Case 4 – Reset AUTO_INCREMENT column to max + 1 value currently used in data. If value is low they query may not work.

ALTER TABLE table_name AUTO_INCREMENT = 1;

 

How to find MySQL AUTO_INCREMENT column value


SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'javausecase_db'
AND   TABLE_NAME   = 'user_master';

 

How to insert in AUTO_INCREMENT column to generate new ID


INSERT INTO user_master (GENERATE_ID) VALUES (NULL)

 

How to get last AUTO_INCREMENT ID that was inserted. This is executed after value is inserted in AUTO_INCREMENT


SELECT LAST_INSERT_ID()

by , on August 24th, 2017

  • Categories