Why it shows 0 instead of empty string whenever I insert an empty string into a MySQL column which is declared as NOT NULL?

It is because inserting an empty string means that we are inserting some value and not NULL. The empty string apparently maps to zero as an integer. In other words, we can say that by inserting empty string we are providing a value to MySQL that has integer representation as INT 0. Consider the following example in which we inserted an empty string and it mapped to 0 by MySQL.

mysql> create table test(id int NOT NULL, Name Varchar(10));
Query OK, 0 rows affected (0.19 sec)

mysql> Insert into test(id, name) values('1', 'Gaurav'),('0','Rahul'),('','Aarav');
Query OK, 3 rows affected, 1 warning (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 1

mysql> Select * from test;
| id | Name   |
| 1  | Gaurav |
| 0  | Rahul  |
| 0  | Aarav  |
3 rows in set (0.00 sec)

Updated on: 14-Feb-2020


Kickstart Your Career

Get certified by completing the course

Get Started