Which is better to insert NULL or empty string in MySQL?

In innoDB, NULL takes up less space than an empty string. Additionally, the NULL length is null, and the length of an empty string is 0.

To understand this, create a table with the help of create command as shown below As follows -

mysql> CREATE table DemoEmptyAndNULL
-> (
-> Message varchar(100)
-> );
Query OK, 0 rows affected (0.49 sec)

After the table is successfully created, insert an empty record into the table through the following command insert command which is as follows -

mysql> INSERT into DemoEmptyAndNULL values(' ');
Query OK, 1 row affected (0.17 sec)

After inserting records, we can use the select command to display all records which is as is as follows -

mysql> SELECT * from DemoEmptyAndNULL;

After executing the above query, the following output is obtained -

+---------+
| Message |
+---------+
|         |
+---------+
1 row in set (0.00 sec)

The syntax for checking the length of an empty string with the help of the built-in function count is as follows Follow-

select count(column_name) from yourTableName;

The above syntax is used to get the length of an empty string which is given as as follows-

mysql> select count(message) from DemoEmptyAndNULL;

After executing the above query, the output is as follows-

+----------------+
| count(message) |
+----------------+
| 1              |
+----------------+
1 row in set (0.06 sec)

From the above output it is clear that the length of empty string is 1.

Now we will check the length of NULL. First, the records inserted into the table are deleted using delete command as as follows-

mysql> delete from DemoEmptyAndNULL where message=' ';
Query OK, 1 row affected (0.19 sec)

The following query is used to check whether there is no record in the current table

mysql> SELECT * from DemoEmptyAndNULL;
Empty set (0.00 sec)

Now, the record with null value is inserted into the table as 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 a null value in the table. To find its length, execute the following query: Using -

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 count is 0 (null value).

Let's check the MySQL version.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.06 sec)

So it is better for the database to use empty strings, as allowing NULL values ​​forces the system to do this Extra work and doesn't provide the data you're looking for. However, NULL does not throw Any exception occurs while executing count() function.

The above is the detailed content of Which is better to insert NULL or empty string in MySQL?. For more information, please follow other related articles on the PHP Chinese website!