Differentiate between null value and empty character ('') in MySQL

In daily development, database addition, deletion, modification and query are generally involved, so it is inevitable to encounter NULL and empty characters in Mysql.
The empty character ('') and the null value (null) appear to be empty, but there are some differences:

definition:

• The length of the null value (NULL) is NULL, and it is uncertain how much storage space is occupied, but the storage space occupied
• The length of an empty string ('') is 0 and does not take up space.
  

In layman's terms:

An empty string ('') is like a vacuum cup, containing nothing.
A NULL value is like a cup filled with air, but it contains something.
Although both appear to be empty and transparent, there are essential differences between them.

the difference:

1. When counting a column using count(), null values ​​will be ignored, but empty characters will be counted. However, count(*) will be optimized to directly return the total number of rows, including null values.
2. To judge whether a null is present, use is null or is not null . SQL can use the ifnull() function to handle this. To judge whether a null character is present, use ='' or !='' .
3. For the timestamp data type, inserting a null value will result in the current system time; inserting a blank character will result in 0000-00-00 00:00:00

Examples:

• Create a new table test_ab and insert 4 rows of data.

CREATE TABLE test_ab (id int,
	col_a varchar(128),
	col_b varchar(128) not null
);

insert test_ab(id,col_a,col_b) values(1,1,1);
insert test_ab(id,col_a,col_b) values(2,'','');
insert test_ab(id,col_a,col_b) values(3,null,'');
insert test_ab(id,col_a,col_b) values(4,null,1);

mysql> select * from test_ab;
+------+-------+-------+
| id | col_a | col_b |
+------+-------+-------+
| 1 | 1 | 1 |
| 2 | | |
| 3 | NULL | |
| 4 | NULL | 1 |
+------+-------+-------+
4 rows in set (0.00 sec)

• First, let's compare the differences between the empty character ('') and the empty value (null) query methods:

mysql> select * from test_ab where col_a = '';
+------+-------+-------+
| id | col_a | col_b |
+------+-------+-------+
| 2 | | |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> select * from test_ab where col_a is null;
+------+-------+-------+
| id | col_a | col_b |
+------+-------+-------+
| 3 | NULL | |
| 4 | NULL | 1 |
+------+-------+-------+
2 rows in set (0.00 sec)

It can be seen that the query methods for null and '' are different. And the comparison characters '=' '>' '<' '<>' cannot be used to query null.
If you need to query for null values, use is null and is not null.

• The second comparison involves operation

mysql> select col_a+1 from test_ab where id = 4;
+---------+
| col_a+1 |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

mysql> select col_b+1 from test_ab where id = 4;
+---------+
| col_b+1 |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

It can be seen from this that null values ​​cannot participate in any calculations, because null values ​​are empty when participating in any calculations.
Therefore, special attention should be paid when there is calculation in the program business.
If it must be included in the calculation, you need to use the ifnull function to convert null to '' for normal calculation.

• The third comparison is statistical quantity

mysql> select count(col_a) from test_ab;
+--------------+
| count(col_a) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(col_b) from test_ab;
+--------------+
| count(col_b) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)

It can be seen from this that when counting the number. Null values ​​are not counted as valid values.
Similarly, when sum() is used to calculate the sum, null will not be counted, so you can understand it.
Why is the result empty when calculating null, but the result is normal when sum() is used to sum?

in conclusion:

Therefore, when setting the default value, try not to use null as the default value. If the field is of int type, the default value is 0; if it is of varchar type, an empty string ('') would be better as the default value. Default values ​​with null can still be indexed, but the efficiency will be affected. Of course, if you are sure that the field will not be indexed, you can set it to null.

When setting a field, you can set it to not null, because the concept of not null does not conflict with the default value. When we set the default value to (''), although we avoid the null situation, it is possible that the field is directly assigned a null value, so null will still appear in the database, so it is strongly recommended to add not null to the field.

Something like this:

mysql> alter table test_ab modify `col_b` varchar(128) NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test_ab;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| col_a | varchar(128) | YES | | NULL | |
| col_b | varchar(128) | NO | | | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Although it may not be worse than the null character in terms of storage space and index performance, in order to avoid its particularity and bring uncertainty to the project, it is recommended not to use NULL as the default value.

The above is the details of distinguishing between null values ​​and empty characters ('') in MySQL. For more information about MySQL null values ​​and empty characters, please pay attention to other related articles on 123WORDPRESS.COM!