What role data type plays when I insert an empty string into a MySQL column which is declared as NOT NULL?

The representation of an empty string in result set depends on data type when we insert an empty string into a MySQL column which is declared as NOT NULL. As we know that on inserting empty string we are providing value to MySQL that has integer representation as INT 0.

Now, if that column is having INTEGER data type then MySQL would show 0 in the result set as that empty string has been mapped to zero as an integer.

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 that column is having any other data type say VARCHAR then MySQL would show 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 examples, we can see that what role data type plays when we insert an empty string into a MySQL column which is declared as NOT NULL.

V Jyothi

Updated on 22-Jun-2020 11:43:04

• Related Questions & Answers
• Why it shows 0 instead of empty string whenever I insert an empty string into a MySQL column which is declared as NOT NULL?
• How it is possible to insert a zero or an empty string into a MySQL column which is defined as NOT NULL?
• Insert default into not null column if value is null in MySQL?
• Empty string in not-null column in MySQL?
• Which one is better to insert NULL or empty string in MySQL?
• What happens when I insert the value ‘NULL’ in an AUTO_INCREMENT MySQL column?
• How to insert NULL keyword as a value in a character type column of MySQL table having NOT NULL constraint?
• Which one is better in MySQL - NULL or empty string?
• Insert NULL value into database field with char(2) as type in MySQL?
• Insert NULL value into INT column in MySQL?
• Which PHP function is used to insert data into an existing MySQL table?
• How do I check if a column is empty or null in MySQL?
• Check if a String is not empty ("") and not null in Java
• What would happen if we insert empty values in a table with a column set as type TIMESTAMP CURRENT_TIMESTAMP?
• How should I store data into my Mysql database, what type should I assign to a column storing salt values?