How AUTO INCREMENT property works in MySQL or MariaDB - MySQL Developer Tutorial

What is AUTO INCREMENT Property in MySQL | How Auto Increment works in MySQL


Auto increment in MySQL is the property on column that is used to auto insert the number in column and increment automatically. In below demo we have covered below topics related to Auto increment in MySQL. 


  1. AUTO_INCREMENT starts from 1 if you have not inserted the value
  2. If you have manually added the value then next number will be from that larger value.
  3. Delete does not reset the AUTO_INCREMENT 
  4. Truncate will reset the AUTO_INCREMENT.


How to create table with Auto Increment Property:

You can use below syntax to create the table with Auto increment property. Let's say we need to create the customer table and we want to have "AUTO_INCREMENT" property enable on idcustomer column, we can use below script.

CREATE TABLE `customer` (
  `idcustomer` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL,
  PRIMARY KEY (`idcustomer`)
);




Insert data into table with "Auto_Increment" : 

To insert the data into table which has Auto Increment enable, you don't have to insert any data in column on which auto increment is enabled. It will start with 1 and with each insert, the value will increase. Let's insert data into customer table which has column idcustomer with auto increment enabled.


insert into customer
(firstname,lastname,age,phonenumber,dob,gender)
values
('Aamir3','Shahzad',39,'505-4141969','1980-01-01','M');



Insert auto increment manually :

If you would like to insert the specific value in column which has auto increment enabled, you can do that in MySQL by using insert statement. In this case, I am inserting value 500 in customer table for idcustomer column.


insert into customer
(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(500,'Aamir3','Shahzad',39,'505-4141969','1980-01-01','M');



How to enable Auto Increment on column in MySQL Table 

1 comment: