AUTO INCREMENT a Field in MySQL

Very often, we want to make columns as auto increment in Database. By specifying AUTO_INCREMENT to the primary key field, we can make column value as auto incrementing in MySQL.

The below code create a simple table “Employees” with a single auto increment column named as “ID”.

CREATE TABLE Employees
(
ID int NOT NULL AUTO_INCREMENT,
Name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
);

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

If we want to start AUTO_INCREMENT column with another value, we can use the following SQL statement.

ALTER TABLE table_name AUTO_INCREMENT= [New Number];

The AUTO INCREMENT interval value is controlled by the MySQL Server variable auto_increment_increment and applies globally. To change this to a number different from the default of 1, we can use the following command in MySQL.

SET @@auto_increment_increment = [interval number];

To insert a new record into the “Employees” table, we will NOT have to specify a value for the “ID” column (a unique value will be added automatically):

INSERT INTO Employees (Name,City)
VALUES ('Blue','zzzzz');

The SQL statement above will insert a new record into the “Employees” table. The “ID” column would be assigned a unique value.

Recent Posts

1. Automating Code Linting with GitHub Actions for WordPress Plugins
2. Comprehensive Guide to Linting PHP, JavaScript, and CSS in WordPress Plugins Using Composer
3. The Ultimate Guide to Indexing in Database Design
4. Understanding 'update_meta_cache' in WordPress - When to Use It, When Not to, and Its Impact on Database Queries
5. A Guide to Configuring JavaScript and SCSS Paths in WordPress Plugins with @wordpress/scripts

This article was published on February 13, 2014

Your Questions / Comments

If you found this article interesting, found errors, or just want to discuss about it, please get in touch.