What is the difference between MySQL's null value and NULL? (with examples)

#This article brings you what is the difference between MySQL's null value and NULL? (With examples), it has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

The essential difference:

1. Null values ​​do not occupy space

2. Null values ​​occupy space

In layman's terms:

The empty value is like a vacuum transition cup with nothing, while the null value is a cup filled with air. Although they all look the same, they have Essential difference.

(Recommended course: MySQL Tutorial)

Example:

Create a test table, colA cannot store null values, colB can store null values.

CREATE TABLE `test` (
   `colA` varchar(255) NOT NULL,
   `colB` varchar(255) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Try inserting a null value, what will happen?

INSERT INTO `test`(`colA`, `colB`) VALUES (NULL, NULL);

//An error occurred because colA cannot insert null values.

#So what will happen if null values ​​are inserted into two fields at the same time.

INSERT INTO `test`(`colA`, `colB`) VALUES ('', '');

The insertion is successful, indicating that even when the field is set to a null value, a null value can be inserted

--------- -------------------------------------------------- ----Inquire--------------------------------------------- ------------

Now there are three pieces of data in the table

Next we use is not null and <> ; Retrieve data in the data table

1. Use IS NOT NULL query

1 SELECT * FROM `test` WHERE colA IS NOT NULL

1 SELECT * FROM `test` WHERE colB IS NOT NULL

Conclusion: Using the IS NOT NULL query will not filter out null values, but it will filter out NULL.

##2. Query using <>

1 SELECT * FROM `test` WHERE colA <> &#39;&#39;;

1 SELECT * FROM `test` WHERE colA <> &#39;&#39;;

Conclusion:

Using <> will filter out NULL and empty values.

3. Use count query

1 SELECT COUNT(colA) FROM `test`;

1 SELECT COUNT(colB) FROM `test`;

Conclusion :

Using count will filter out NULL values, but will not filter out null values.

Summary

1. Null values ​​do not occupy space, and NULL values ​​occupy space (occupies one byte).

2. When the field is not NULL, you can also insert a null value.

3. When using IS NOT NULL or IS NULL, you can only find out that there is no non-NULL or NULL value in the field, but you cannot find out the null value.

4. When using <> to query, empty values ​​and NULL values ​​will be filtered out.

5. When using count statistics, NULL values ​​will be filtered out, but NULL values ​​will not be filtered out.

The above is the detailed content of What is the difference between MySQL's null value and NULL? (with examples). For more information, please follow other related articles on the PHP Chinese website!