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

Using <> will filter out NULL and empty values.

3. Use count query

1 SELECT COUNT(colA) FROM `test`;

1 SELECT COUNT(colB) FROM `test`;

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

Summary

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!