What is the first number assigned to AUTO_INCREMENT?

2024-03-29 02:02:53 Catlena Mysql

In MySQL, when automatic numbering is done with AUTO_INCREMENT, automatic numbering is started from 1 instead of 0. After 1, increment by 2, 3 and 1, and assign numbers sequentially.

For automatic numbering, insert NULL with no value in AUTO_INCREMENT column during INSERT. Let's check it by actually executing it.

/ * Create a table with columns with AUTO_INCREMENT * /
CREATE TABLE ITEM_LIST (
  ITEM_ID INT AUTO_INCREMENT,
  ITEM_NAME VARCHAR (100),
  / * AUTO_INCREMENT column must be set to INDEX * / 
  INDEX (ITEM_ID) 
);

/ * Register data in the above table * /
INSERT INTO ITEM_LIST (ITEM_ID, ITEM_NAME) VALUES (NULL, 'MySQL FAQ');

/ * Check registered data * /
SELECT * FROM ITEM_LIST;


The execution result is as follows. In the execution result of the last SELECT statement, you can see that 1 is assigned to the column with AUTO_INCREMENT specified.


mysql> CREATE TABLE ITEM_LIST (
  -> ITEM_ID INT AUTO_INCREMENT,
  -> ITEM_NAME VARCHAR (100),
  ->   / * INDEX must be set for AUTO_INCREMENT column * /
  -> INDEX (ITEM_ID)
  ->);
Query OK, 0 rows affected (0.08 sec)

mysql> / * Register data in the above table * /
mysql> INSERT INTO ITEM_LIST (ITEM_ID, ITEM_NAME) VALUES (NULL, 'MySQL FAQ');
Query OK, 1 row affected (0.01 sec)

mysql> / * Check registered data * /
mysql> SELECT * FROM ITEM_LIST;
+ --------- + ----------- +
| ITEM_ID | ITEM_NAME |
+ --------- + ----------- +
| 1 | MySQL FAQ |
+ --------- + ----------- +
1 row in set (0.00 sec)

mysql>


This post is submitted by one of our members. You may submit a new post here.

Related Tricks