Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
MySQL
AUTO_INCREMENT Keyword
MySQL uses the
AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
The SQL statement defines the “student_id” column to be an auto-increment primary key field in the “student” table:
SQL> DROP TABLE student; SQL> CREATE TABLE student ( 2 student_id INT NOT NULL AUTO_INCREMENT, 3 last_name VARCHAR(255) NOT NULL, 4 first_name VARCHAR(255), 5 age INT, 6 PRIMARY KEY ( student_id ) ); |
To let the
AUTO_INCREMENT sequence start with another value, use the SQL statement:
SQL> ALTER TABLE Persons AUTO_INCREMENT=100; |
When we insert a new record into the “student” table, we do NOT have to specify a value for the “student_id” column (a unique value will be added automatically):
SQL> INSERT INTO student ( first_name, last_name ) VALUES ( 'Lars', 'Monsen' ); |
The SQL statement above would insert a new record into the “student” table. The “student_id” column would be assigned a unique value automatically. The “first_name” column would be set to “Lars” and the “last_name” column would be set to “Monsen.”
Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.
|
Result:
|
The Database includes:
|
|
The Database includes:
|