Author: 3# A post - 80 generation focused on web technology

I don't have to spell smart people, I just need to spell those lazy people I will surpass most people!

CSDN @geek Xiaojun, CSDN official debut, ‘3# original

Corporate blog: 👉 GeekerJun 👈

Blog garden URL: 👉 cnblogs.com/GeekerJun 👈

The difference between NULL and NULL values

NULL is a NULL value stored in a field

An empty string value is a null character (” “) stored in the field.

Let’s take a test to see how they differ from each other:

1, occupied space difference

mysql> select length(NULL), length(' '), length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
|         NULL |          0 |           1 |
+--------------+------------+-------------+
1 row in set (0.03 sec)

Copy the code

== NULL = NULL = NULL = NULL = NULL = NULL = NULL

NULL columns require additional space in the row to record whether their values are NULL.

NULL columns require extra space in the row to record whether or not their value is NULL

In common sense: a NULL string is like a vacuum cup, with nothing, while a NULL string is like a cup full of air. They both look the same, but there is a fundamental difference

2. Differences in insertion modes

Create table tb_test

create table tb_test(
  id int unsigned primary key auto_increment,
  one varchar(10) NOT NULL,
  two varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8; Insert for validation:If NULL is not allowed to be inserted, it will fail
insert into tb_test(one,two) value (NULL.NULL);
1048 - Column 'one' cannot be null

Insert NULL string (" "); insert NULL string (" "); insert NULL (" "); ^. ^
insert into tb_test(one,two) value (' '.' ');
Query OK, 1 row affected
This is why inserting (" ") an empty string succeeds when testing insert statements with the not NULL constraint!

Copy the code

3, the difference in the query method comparison

Create table tb_test2

create table tb_test2(
  id int unsigned primary key auto_increment,
  one varchar(10) NOT NULL,
  two varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Simulation data:
insert into tb_test2(one,two) values (1.NULL);
insert into tb_test2(one,two) values (' '.2);
insert into tb_test2(one,two) values (3.3);

# Query the one field
# query one field with is NULL
select * FROM tb_test2 where one is null; # NULL = NULL; # NULL = NULL;

# Query the one field with is not NULL
select * FROM tb_test2 where one is not null;  # Select NULL from 'one'; # select NULL from 'one'

# use = and! = to query the one field
select * FROM tb_test2 where one =' ';
select * FROM tb_test2 whereone ! =' ';

# Query the two field
# query the two field with is NULL
select * FROM tb_test2 where two is null;  # NULL = NULL;

# query the two field with is not NULL
select * FROM tb_test2 where two is not null; # There are two results that do not match NULL


Use = to query the two field
select * FROM tb_test2 where two =' ';

# to use! = to query the two field
# NULL = NULL; = = = = = = = = = = = = = = =
select * FROM tb_test2 wheretwo ! =' '; 


Copy the code

== Summary: == If you want to query only NULL columns, use is NULL; if you want to query only NULL columns, use = “”.

The recommended query mode is NULL/IS not NULL, and the NULL value (” “) can be = or! =, <, > and other arithmetic operators!

4, the difference in the count() function

Create table tb_test3

create table tb_test3(
  id int unsigned primary key auto_increment,
  one varchar(10) NOT NULL,
  two varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Simulation data:
insert into tb_test3(one,two) values (1.NULL);
insert into tb_test3(one,two) values (' '.2);
insert into tb_test3(one,two) values (3.3);

# COUNT (' one ', 'one')
select count(one) from tb_test3;   # Result: 3, indicating that the empty string (") is counted by the count() function!
# COUNT the two fields:
select count(two) from tb_test3;   # NULL = count(); # NULL = count()

# Note: using asterisks for statistics counts NULL!
SELECT count(*) FROM tb_test;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

Copy the code

Should the actual development use NULL values or NULL values (” “)?

According to the actual business to distinguish, personal suggestion in the actual development if there is no special business scenario, you can directly use the empty string value (“)!

If my blog is helpful to you, if you like my blog content, please “like” “comment” “collection” one key three! I heard that 👉 praise 👈 people will not be too bad luck, every day will be full of vitality oh hey hey!! ❤️ ❤️ ❤️ Your support is what keeps me going. Don’t forget to follow me at 👉 and 👈!

If there are any mistakes or inaccuracies in the above content, please leave a message below 👇 to point out, or you have a better idea, welcome to exchange and learn ~~~