Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Which one is better to insert NULL or empty string in MySQL?
In innoDB, NULL occupies less space as compared to empty string. Also, the NULL length is null while length of the empty string is 0.
To understand this, a table is created with the help of create command which is given as follows −
mysql> CREATE table DemoEmptyAndNULL -> ( -> Message varchar(100) -> ); Query OK, 0 rows affected (0.49 sec)
After creating the table successfully, an empty record is inserted into the table with the help of insert command which is as follows −
mysql> INSERT into DemoEmptyAndNULL values(' ');
Query OK, 1 row affected (0.17 sec)After inserting the record, we can display all the records with the help of the select command which is as follows −
mysql> SELECT * from DemoEmptyAndNULL;
After executing the above query, following output is obtained −
+---------+ | Message | +---------+ | | +---------+ 1 row in set (0.00 sec)
The syntax to check the length of an empty string with the help of a built in function count is as follow −
select count(column_name) from yourTableName;
The above syntax is used to get the length of an empty string which is given as follows −
mysql> select count(message) from DemoEmptyAndNULL;
After executing the above query, the following is the output −
+----------------+ | count(message) | +----------------+ | 1 | +----------------+ 1 row in set (0.06 sec)
From the above output it is clear that the length of the empty string is 1.
Now we will check the length of NULL. First, the record that was inserted into the table is deleted using delete command as follows −
mysql> delete from DemoEmptyAndNULL where message=' '; Query OK, 1 row affected (0.19 sec)
The following query is used to check that there is no record in the table right now
mysql> SELECT * from DemoEmptyAndNULL; Empty set (0.00 sec)
Now, the record with null value is inserted into the table as follows −
mysql> INSERT into DemoEmptyAndNULL values(); Query OK, 1 row affected (0.18 sec)
The record is displayed using the select command and the output obtained is as follows −
+---------+ | Message | +---------+ | NULL | +---------+ 1 row in set (0.00 sec)
Now, there is only one record with null value in the table. To find its length following query is used −
mysql> select count(message) from DemoEmptyAndNULL;
The following is the output of the above query −
+----------------+ | count(message) | +----------------+ | 0 | +----------------+ 1 row in set (0.00 sec)
The above output means that count is 0 for null value.
Let us check the MySQL version.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.06 sec)
So it is better to use empty string for database as allowing NULL value forces the system to do extra work and does not give the data that you are looking for. However, NULL does not throw any exception when the count() function is executed.
2K+ Views
