Introduction: in the morning, I received a text message with the content of "Dear Hello, XXX". At that time, I smiled.

It's a joke that programmers can Get. It shows that the program doesn't Get my name correctly from the database, and then formats the null value as null.


To get back to business, the reason for this situation is generally caused by the data problem in the database. I dare to guess several scenarios. You can help add in the comment area:

1. There was an error in the first name storage. It failed to fill in my name. MySQL defaults to null value and is formatted as a 'null' string during query.

2. When a null pointer is added to the name of the regular stream, \ r \ nwhen I register the string, it will appear again when I return null pointer.

3. I set the id to 'null' (no, guys, can I be so boring? ~ ~)

   In MySQL, NULL represents unknown data. When designing tables, old drivers often tell us:

   Use NOT NULL instead of NULL as far as possible, unless there are special circumstances!

However, they only give conclusions and do not explain the reasons. Just like drinking chicken soup without a spoon, it is a little diaphragmatic, which makes many students know only one of these conclusions and not the other.
Frankly speaking, the old driver doesn't necessarily know why. Maybe his leader asked him to do so~~
Just like my leader, I remember when I first came to the company, he told me with earnest words: remember to use not null for MySQL table fields, or go away!
Today, I'll take you to find out why. I suggest you use not null in the fields of the table as much as possible!
First look at the NULL mentioned in the MySQL official website document:

NULL columns require additional space in the rowto record whether
their values are NULL. For MyISAM tables, each NULL columntakes one
bit extra, rounded up to the nearest byte.

The interpreter:

The NULL column requires additional space in the row to record whether its value is NULL. For the MyISAM table, each NULL column takes one more bit and is rounded to the nearest byte.

In fact, this is the official euphemism to tell you not to use NULL~~

Let's take a look at how many pits there are in the NULL value. Here, I will combine the NULL field and highlight the sum function, count function and the pits that may be stepped on when the query condition is NULL.
First give our test table:

mysql> select * from demo0527;
+----+------------+-------+------+
| id | name       | money | age  |
+----+------------+-------+------+
|  1 | Welcome to join java Communication junyang 1:  |   100 | NULL |
|  2 | Welcome to join java Communication junyang 2:   |  NULL | NULL |
|  3 | NULL       |   100 | NULL |
+----+------------+-------+------+
3 rows in set (0.00 sec)

--------
Let's look at the following three use cases in combination with the null value of table demo0527 in the database:

Example 1: use the sum function to count the sum of a column with only NULL value, such as SUM(age).

Example 2: select the number of records, and count uses a field that allows NULL, such as COUNT(name).

Example 3: use the = NULL condition to query records with NULL field value, such as the money=null condition.

The test SQL corresponding to the above three examples is as follows:

SELECT SUM(age) from demo0527;
SELECT count(name) from demo0527;
SELECT * FROM demo0527 WHERE money=null;

Query results:

mysql> SELECT SUM(age) from demo0527;
+----------+
| SUM(age) |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(name) from demo0527;
+-------------+
| count(name) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM demo0527 WHERE money=null;
Empty set (0.00 sec)

The results obtained are NULL, 2 and empty List respectively; Obviously, the execution results of these three SQL statements are different from our expectations:

• Although the ages in the table are NULL, the result of SUM(age) should be 0.
• Although the name of the record in the third row is NULL, the total number of rows in the query record should be 3.
• Using money=NULL, no record with id=2 can be queried, and the query criteria are invalid.

The reasons for the three examples are:

1. When the sum function in MySQL does not count any records, it will return null instead of 0. You can use the IFNULL(null,0) function to
   null converted to 0.
2. When count (field) is used in MySQL, null value will not be counted, and COUNT(*) can count all rows.
3. Arithmetic comparison operators such as =, <, > are used in MySQL. The resu lt of comparing NULL is always NULL. This comparison is meaningless and needs to be used
   IS NULL, IS NOT NULL, or ISNULL() function.

Let's modify SQL according to the above reasons:

SELECT IFNULL(SUM(age),0) FROM demo0527;
SELECT COUNT(*) FROM demo0527;
SELECT * FROM demo0527 WHERE age IS NULL;

After modification, the result of our query is what we want:

mysql> SELECT IFNULL(SUM(age),0) FROM demo0527;
+--------------------+
| IFNULL(SUM(age),0) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM demo0527;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM demo0527 WHERE age IS NULL;
+----+------------+-------+------+
| id | name       | money | age  |
+----+------------+-------+------+
|  1 | Welcome to join java Communication junyang 1:   |   100 | NULL |
|  2 | Welcome to join java Communication junyang 2:    |  NULL | NULL |
|  3 | NULL       |   100 | NULL |
+----+------------+-------+------+
3 rows in set (0.00 sec)

In addition, it is worth noting that not only can we not find records with NULL field value under the condition of money=NULL, but when we use select * from demo0527 where money < > 100; When you query the line id=2, you can't find any data.
We often stumble here in our work, resulting in inaccurate statistics. Please Mark.

mysql> SELECT * FROM demo0527 WHERE money <>100;
Empty set (0.02 sec)

It can be seen that the NULL value in MySQL database can easily lead to errors in statistics and query table data.
Some students here may ask whether there is any performance improvement. Is it SQL optimization? In fact, the performance improvement brought by changing NULL column to NOT NULL can be ignored. Unless it is determined that it brings problems, it does not need to be regarded as a priority optimization measure.
[reference documents]