- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
MySQL AUTO_INCREMENT with Examples
Let us understand how ATUO_INCREMENT works −
The AUTO_INCREMENT attribute is used to generate a unique identify for new rows. Let us see how this statement works. Before that, consider the below query −
CREATE TABLE tableName ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO tableName (name) VALUES (‘val1’),('val2'),('val3'), ('val4'); SELECT * FROM tableName;
+----+---------+ | id | name | +----+---------+ | 1 | val1 | | 2 | val2 | | 3 | val3 | | 4 | val4 | +----+---------+
In the above query, no value was specified for the ‘AUTO_INCREMENT’ column, hence MySQL assigned a sequence of numbers automatically to the ‘id’ column. A value of 0 can also be explicitly assigned so that the number sequence begins from 0. This can be done only if ‘NO AUTO VALUE ON ZERO’ SQL mode is not enabled.
If a column is declared as ‘NOT NULL’, it is possible to assign NULL to that column to generate a sequence of numbers.
When any value is inserted into an AUTO_INCREMENT column, the column gets set to that value, and the sequence also gets reset so that it generates values automatically, in the sequential range from largest column value.
An existing ‘AUTO_INCREMENT’ column can be updated that will reset the ‘AUTO_INCREMENT’ sequence as well.
The most recent auto-generated ‘AUTO_INCREMENT; value can be retrieved using the ‘LAST_INSERT_ID()’ function in SQL or using the ‘mysql_insert_id()’ which is a C API function.
These functions are connection-specific, which means their return values are not affected by other connections which perform the insert operations.
The smallest integer data type for ‘AUTO_INCREMENT’ column can be used, which would be large enough to hold the maximum sequence value which is required by the user.
When the column reaches the upper range of the data type, another attempt is made to generate a sequence of numbers. But this fails.
Hence, use UNSIGNED attribute if it is possible so as to allow a greater range of values in the column AUTO_INCREMENT values.
- Related Articles
- Set custom Auto Increment with ZEROFILL in MySQL
- Two columns as primary key with auto-increment in MySQL?
- Change the Auto Increment counter in MySQL?
- Passing NULL to MySQL for auto increment?
- How to change auto increment number in MySQL?
- How to make MySQL table primary key auto increment?
- MySQL query to set my auto increment column ( id ) to zero or reset the value of auto increment field?
- How to create a table with auto-increment column in MySQL using JDBC?
- How to auto increment with 1 after deleting data from a MySQL table?
- How to get the next auto-increment id in MySQL?
- How to set initial value and auto increment in MySQL?
- How to change auto increment number in the beginning in MySQL?
- How to handle fragmentation of auto increment ID column in MySQL?
- Set auto increment initial value for MySQL table using ALTER command
- Changing the current count of an Auto Increment value in MySQL?
- Set MySQL int column to auto increment by 1 beginning at 10000?