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.