Null and empty strings in Mysql

A few times ago, When I updated the database, I met a new table statement and found that the inserted null values are automatically filled. This is very confusing. Let's talk about the case first. Below is a simulated test:

First create a table: create table ceshi (id int, createDate TimesTamp );

It contains two fields, whose id is 'int' type and 'createdate' is 'timestamp' type. Now we insert a piece of data into this table, where 'createdate' inserts a null value;

Statement: insert into ceshi values (001, null );

 

Mysql> insert into ceshi values (001, null );

Query OK, 1 row affected (0.01 sec)

After successful insertion, You can query the records in this table. The result is as follows:

mysql> select * from ceshi;+------+---------------------+| ID   | createDate          |+------+---------------------+|    1 | 2012-10-08 06:12:34 |+------+---------------------+1 row in set (0.01 sec)

I clearly inserted a null value. Why is the current system time displayed? Then try inserting an empty string directly?

 

mysql> insert into ceshi values(002,'');  Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from ceshi;             +------+---------------------+| ID   | createDate          |+------+---------------------+|    1 | 2012-10-08 06:12:34 ||    2 | 0000-00-00 00:00:00 |+------+---------------------+2 rows in set (0.00 sec)

The current system time is not displayed, but 0000-00-00:00:00.

If the field is not of the time type but of the varchar type, will the result be the same? Change the table structure, add the field name varchar (10), and insert a data entry at the same time:

mysql> insert into ceshi values(003,null,null);Query OK, 1 row affected (0.01 sec)mysql> select * from ceshi;                    +------+---------------------+------+| ID   | createDate          | name |+------+---------------------+------+|    1 | 2012-10-08 06:12:34 | NULL ||    2 | 0000-00-00 00:00:00 | NULL ||    3 | 2012-10-08 06:22:12 | NULL |+------+---------------------+------+3 rows in set (0.00 sec)

A null value is added, and then a ''null string is inserted into the field:

mysql> insert into ceshi(name) values('');Query OK, 1 row affected (0.01 sec)mysql> select * from ceshi;               +------+---------------------+------+| ID   | createDate          | name |+------+---------------------+------+|    1 | 2012-10-08 06:12:34 | NULL ||    2 | 0000-00-00 00:00:00 | NULL ||    3 | 2012-10-08 06:22:12 | NULL || NULL | 2012-10-08 06:23:01 |      |+------+---------------------+------+4 rows in set (0.01 sec)

It seems that there is no problem with the inserts, but when we calculate the data size of the name, there is a problem at this time:

mysql> select count(name) from ceshi;+-------------+| count(name) |+-------------+|           1 |+-------------+1 row in set (0.00 sec)

 

There is only one result, that is, the count function ignores null (null value) and only counts data with name =.

Therefore, for such statements, it is best to use is null or is not null as the condition to filter null values.

In summary, the original null (null) is not necessarily equal to the ''null character, and null (null) is not necessarily empty.

Interpretation: when the previous createDate field inserts a null value, in mysql database, NULL represents a special meaning for some special types of columns, it is not just a null value. For these special types of columns, one is the TimesTamp type and the other is the auto-incrementing column. For other types, the null value is displayed when null is inserted. In addition, when an empty string is inserted in the createDate test for the second time, the value 0000-00-00 00:00:00 is displayed. Because of the non-strict mode of mysql, the input is automatically converted because it is invalid, and give a warning.

For the count statistical function, the null value is automatically ignored. So I thought, why should we do this? It turns out this is also good. If we count the user information of a mobile phone number in a region, we only need to use count (mobile phone number) you can automatically filter out people without a mobile phone.