On inserting ‘NULL’, ‘0’ or No Value to the column, will MySQL assign sequence number for AUTO_INCREMENT column?

MySQLMySQLi Database

MySQL will automatically assign sequence numbers to the AUTO_INCREMENT column even if we insert NULL, 0 or No Value to the column in a table.


mysql> create table test123(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Name Varchar(10));
Query OK, 0 rows affected (0.15 sec)

The query above created a MySQL table named ‘test123’ with columns named ‘id’ and ‘Name’. The column ‘id’ is declared AUTO_INCREMENT. Now, if we insert ‘No Value’, ‘0’ or ‘NULL’ in the ‘Name’ column, MySQL will assign the sequence numbers to column ‘id’. It can be seen from the result queries below −

mysql> Insert Into test123(Name) values(''),('0'),(NULL);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> Select * from test123;
| id | Name |
| 1  |      |
| 2  |    0 |
| 3  | NULL |
3 rows in set (0.00 sec)
Published on 19-Feb-2018 17:28:58