Set a custom value for AUTO_INCREMENT while creating a table and use ZEROFILL. What will happen now when nothing is inserted while using INSERT statement?

We will see an example and create a table wherein we have set StudentId column as AUTO_INCREMENT = 100 and used ZEROFILL as well −

mysql> create table DemoTable
(
   StudentId int(7) ZEROFILL NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(StudentId)
)AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.48 sec)

Insert some records in the table using insert command. Now, when nothing is inserted, the value would begin from 101 (auto_increment) and rest of the values in the left would be filled with 0 since we have set ZEROFILL while creating the table above −

mysql> insert into DemoTable values();
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.09 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-----------+
| StudentId |
+-----------+
| 0000100   |
| 0000101   |
| 0000102   |
| 0000103   |
+-----------+
4 rows in set (0.00 sec)

AmitDiwan

e

Updated on: 25-Sep-2019

54 Views

Kickstart Your Career

Get certified by completing the course

Get Started