Do you know the difference between empty value and null value in mysql

Preface

Recently I found that my friend's method of judging null values ​​when writing SQL was incorrect, which caused errors in the data in the program. I will sort it out here to help everyone correctly judge null values ​​in the future. The following is an example to explain to you.

Create a table

create table test (colA varchar(10) not null,colB varchar(10) null);

Insert data into the test table

Insert data where colA is null

insert into test values ​​(null,1);

An error will be reported at this time because the colA column cannot be null.

Insert data where colB is null

insert into test values ​​(1,null);

The insertion was found to be successful.

Insert all empty data

insert into test values ​​('',''); 

Insert data where colA is empty

insert into test values ​​('',null); 

It is found that null values ​​can be inserted successfully into the colA column, while both null values ​​and NULL values ​​can be inserted into the colB column.
To summarize, a field defined as NOT NULL can only insert empty values, not null values, while a NULL field can insert empty values ​​or null values.

Insert data that is not empty

insert into test values ​​(1,2);

Start query

It can be found that is not null will only filter columns with null values, while <> will filter both empty values ​​and null values, so you should choose the filtering method according to the actual situation.
In addition, to judge the null value, you can only use is null or is not null, not = or <>.

Special attention

1. When performing count() to count the number of records in a column, if a NULL value is used, it will be automatically ignored by the system, but the null value will be counted.

2. Use IS NULL or is not null to judge NULL. You can use ifnull() function in SQL statement function to process it. Use = or <> to judge empty characters.

3. Special considerations for MySQL: For the timestamp data type, if a NULL value is inserted into a column of this data type, the value that appears is the current system time. If you insert a null value, '0000-00-00 00:00:00' will appear.

4. Whether to use is null or = to judge the null value should be distinguished according to the actual business.

5. When using ORDER BY, NULL values ​​are presented first. If you sort in descending order using DESC, NULL values ​​appear last. When GROUP BY is used, all NULL values ​​are considered equal, so only one row is displayed.

Summarize

This is the end of this article about the difference between empty values ​​and null values ​​in MySQL. For more information about the difference between empty values ​​and null values ​​in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!