What is the difference between the null value of MySQL and NULL
This article is about the difference between MySQL null and NULL. Xiao Bian thinks it is quite practical, so share it for everyone to make a reference. Let's follow the editor and have a look.
The essential difference:
1, null value does not occupy space
2, null value occupies space
In plain English:
A null value is like a vacuum cup with nothing, while a null value is a cup full of air. Although they all look the same, there are essential differences.
(Recommended course: MySQL tutorial)
Examples:
Create a test table, colA is not allowed to store null values, colB is able to 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 happens?
INSERT INTO `test`(`colA`, `colB`) VALUES (NULL, NULL);
//error occurred because colA cannot insert null value.
So what happens if both fields insert null values at the same time?
INSERT INTO `test`(`colA`, `colB`) VALUES ('', '');
Insertion succeeds, indicating that even if the field is set to null, it can insert null values.
---------------
There are now three pieces of data in the table
Next we use is not null and retrieve data from the data table
1. Query using IS NOT NULL
1 SELECT * FROM `test` WHERE colA IS NOT NULL
1 SELECT * FROM `test` WHERE colB IS NOT NULL
Conclusion: Using IS NOT NULL queries does not filter nulls, but it does filter nulls.
2. Use of queries
1 SELECT * FROM `test` WHERE colA '';
1 SELECT * FROM `test` WHERE colA '';
Conclusion: Use filters out NULL and null values.
3. Use count query
1 SELECT COUNT(colA) FROM `test`;
1 SELECT COUNT(colB) FROM `test`;
Conclusion: Using count filters out NULL values, but not nulls.
summary
1, null value does not occupy space, NULL value occupies space (occupy a byte).
When the field is not NULL, a null value can also be inserted.
3. When IS NOT NULL or IS NULL is used, it can only be found that there is no field that is not NULL or NULL, and null values cannot be found.
When using queries, null values and NULL values are filtered out.
5. NULL values are filtered out when count is used, but null values are not filtered out.
Thank you for reading! What is the difference between MySQL null and NULL is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge. If you think the article is good, you can share it so that more people can see it!
