null and empty string space problem in MySQL

Some children who have just come in contact with MySQL often mistakenly assume that Null is the same as the empty string '. This may seem like an unimportant thing, but in MySQL, the two are completely different. Null means that there is no value, and "' means that the value exists, but it is a null value ...

This is equivalent to the factory to the staff of the House, a total of only a set, Lao Zhang and Old Lee want, but the manager told Lao Zhang, the next time you have your house, but Lao Li has no one to speak to him. So Lao Zhang's house is "empty" because it is a blank cheque, but after all the manager has opened his mouth, and Lao Li's house is null, because no one has considered him.

The difference between the two is small, but if not carefully, it may limit us to the dilemma. Because null values cannot be used in the primary key field. We should remember that.

Start by creating a table: Create TABLE Ceshi (ID INT, CreateDate TimesTamp);

Contains 2 fields, the ID is int type, createdate is timestamp type, now inserts a data into this table, where CreateDate type inserts a null value NULL;

Statement: INSERT into Ceshi values (001,null);

The code is as follows

mysql> INSERT into Ceshi values (001,null);

Query OK, 1 row affected (0.01 sec)

Insert succeeded, now to query the following records in this table, see the following results:

The code is as follows

Mysql> select * from Ceshi;

+------+---------------------+

| ID | CreateDate |

+------+---------------------+

| 1 | 2012-10-08 06:12:34 |

+------+---------------------+

1 row in Set (0.01 sec)

I was inserting a null value. NULL, why is the current time displayed when the system is displayed? Then try inserting the empty string directly?

The code is as follows

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 display is not the system current time, but the 0000-00-00 00:00:00.

So if the field is not a time type but a varchar type, will the result be the same, change the table structure, add the field name varchar (10), and insert a piece of data:

The code is as follows

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)

Adds a null value, and then inserts an ' empty string ' into the field:

The code is as follows

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 looks like the inserts are all fine, but when we go to count the number of data in the name, there's a problem:

The code is as follows

Mysql> Select COUNT (name) from Ceshi;

+-------------+

| COUNT (name) |

+-------------+

| 1 |

+-------------+

1 row in Set (0.00 sec)

Simply say empty string = = empty string; is ture and NULL = = NULL is false;

Null means nothing at all. Empty after all is empty ~ still have something just it's this thing is "empty"

Execute the following statement in MSSQL:

Print null+ ' null have value? ';

Print "+ ' empty string have value? ';