
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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.
- Related Articles
- 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?
- What happens when I insert the value ‘NULL’ in an AUTO_INCREMENT MySQL column?
- Which one is better to insert NULL or empty string in MySQL?
- How to insert NULL keyword as a value in a character type column of MySQL table having NOT NULL constraint?
- Insert NULL value into INT column in MySQL?
- Insert NULL value into database field with char(2) as type in MySQL?
- Which one is better in MySQL - NULL or empty string?
- 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
- Which PHP function is used to insert data into an existing MySQL table?
- 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?

Advertisements