null and empty string space problem in MySQL

Source: Internet
Author: User
Tags current time

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 Copy Code

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 Copy Code

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.

  code is as follows copy code

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 Copy Code

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:

  code is as follows copy code

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 Copy Code

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? ';

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.