What role does the data type play when I insert an empty string into a MySQL column that is declared NOT NULL?

When we insert an empty string into a MySQL column declared as NOT NULL, the representation of the empty string in the result set depends on the data type. We know that when inserting an empty string, we provide MySQL with the value represented as an integer as INT 0.

Now, if the column has the INTEGER data type, MySQL will display 0 in the result set as shown below. The empty string is mapped to the integer zero.

Example

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)

But if the column has any other data type (such as VARCHAR), then MySQL will display an empty string in the result set.

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

mysql> Insert into test123(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 test123;
+----+--------+
| id | Name   |
+----+--------+
|  1 | Gaurav |
|  0 | Rahul  |
|    | Aarav  |
+----+--------+
3 rows in set (0.00 sec)

From the above example, we can see what role the data type plays when we insert an empty string into a MySQL column declared as NOT NULL.

The above is the detailed content of What role does the data type play when I insert an empty string into a MySQL column that is declared NOT NULL?. For more information, please follow other related articles on the PHP Chinese website!