NULL and empty string spaces in MySQL

Some children who are new to MySQL often mistakenly think that NULL is the same as a NULL string. This seems unimportant, but in MySQL, the two are completely different. NULL indicates that there is no value, but it indicates that the value exists, but it is a NULL value .. This is equivalent to dividing the factory into houses for employees. There is only one set. Lao Zhang and Lao Li

Some children who are new to MySQL often mistakenly think that NULL is the same as the NULL String. This seems unimportant, but in MySQL, the two are completely different. NULL indicates that there is no value, while ''indicates that the value exists, but it is a NULL value .. This is equivalent to dividing the factory into houses for employees. There is only one set. Lao Zhang and Lao Li

Some children who are new to MySQL often mistakenly think that NULL is the same as the NULL String. This seems unimportant, but in MySQL, the two are completely different. NULL indicates that there is no value, while ''indicates that the value exists, but it is a NULL value ..

This is equivalent to dividing the factory into houses for the employees. There is only one house in total. Both Lao Zhang and Lao Li wanted it, but the manager told Lao Zhang that there will be your house next time, but no one will take care of him at all. So Lao Zhang's house is "empty", because it is a blank check, but after all, the manager zhangkou, and Lao Li's house is NULL, because no one has considered him.

The difference between the two is not big, but if we don't take it into consideration, we may be limited to difficulties. Because NULL values cannot be used in the primary key field. Remember this.

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 );

The Code is as follows:

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:

The Code is as follows:

Mysql> select * from ceshi;

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

| ID | createDate |

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

| 1 | 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?

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 | 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:

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 | 06:12:34 | NULL |

| 2 | 0000-00-00 00:00:00 | NULL |

| 3 | 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:

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 | 06:12:34 | NULL |

| 2 | 0000-00-00 00:00:00 | NULL |

| 3 | 06:22:12 | NULL |

| NULL | 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:

The Code is as follows:

Mysql> select count (name) from ceshi;

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

| Count (name) |

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

| 1 |

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

1 row in set (0.00 sec)

To put it simply, NULL String = NULL string; true and NULL = NULL is false;

NULL is meaningless. Null, after all, is null ~ There is something, but its stuff is "empty"

Execute the following statement in mssql:

Print null + 'null has a value? ';

Print "+ 'is there a null string value? ';