Shulou(Shulou.com)05/31 Report--

This article mainly explains "the difference between NULL and empty string in MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn the difference between NULL and empty string in MySQL.

Today received a MySQL work order, is to execute several SQL statements. As soon as I read it, I found this sentence more interesting.

The statement goes something like this:

Update app_code_value set channel_id=null where task_id=378 and channel_id=''

Update app_code_value set channel_id=null where task_id=379 and channel_id=''

Because I am familiar with Oracle, I always like to look at a lot of problems from the perspective of Oracle. In most cases, they are the same, but there are still some differences. These require extra attention.

If you look at the above SQL statement from Oracle's point of view, you can basically conclude that the statement does not need to be executed. Because in Oracle, null and empty string still have different meanings, but the effect is the same.

Of course, with regard to NULL, there are syntax like is null and is not null in MySQL,Oracle, which is also the basic specification. If you use a case like = null, the effect is the same as oracle.

Select count (*) from app_code_value where task_id=378 and channel_id=null

+-+

| | count (*) |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

What about null and empty strings in MySQL? let's take a look.

Use is null

> select count (*) from app_code_value where task_id=378 and channel_id is null

+-+

| | count (*) |

+-+

| | 90000 |

+-+

1 row in set (14.46 sec)

Use empty string

> select count (*) from app_code_value where task_id=378 and channel_id =''

+-+

| | count (*) |

+-+

| | 90000 |

+-+

1 row in set (14.46 sec)

If you look at the above results, it is easy to think that the two effects are the same. I was almost misled by this situation. Let's see another one.

> select count (*) from app_code_value where task_id=378 and (channel_id is null or channel_id ='')

+-+

| | count (*) |

+-+

| | 180000 |

+-+

1 row in set (5.41 sec)

And directly ignore whether the field is empty, look at all the matching data, you can see that this is the data.

> select count (*) from app_code_value where task_id=378

+-+

| | count (*) |

+-+

| | 180000 |

+-+

1 row in set (5.41 sec)

As can be seen from the above tests, there are still some differences between null and empty strings. If you want to make a visual distinction, I see a very good example, which is to compare the relationship between vacuum and air to empty string and null.

Null and timestamp

(root:localhost:Wed Jul 6 22:46:46 2016) [test] > create table test_null (id int,date timestamp)

Insert into test_null values (1) nQuery OK, 0 rows affected (0.16 sec)

(root:localhost:Wed Jul 6 22:46:51 2016) [test] > insert into test_null values (1 focus null)

Query OK, 1 row affected (0.00 sec)

(root:localhost:Wed Jul 6 22:46:51 2016) [test] > select * from test_null

+-+ +

| | id | date |

+-+ +

| | 1 | NULL |

+-+ +

1 row in set (0.00 sec)

To be more specific, you can use length. Of course we can do some tests by the way.

Create table test_null (id int,name varchar (30))

Let's take a look at the performance of numerical types.

Insert into test_null (id) values (null)

Insert into test_null (id) values ('')

> select * from test_null

+-+ +

| | id | name |

+-+ +

| | NULL | NULL |

| | 0 | NULL |

+-+ +

2 rows in set (0.00 sec)

You can see the handling of the numeric type int, and the empty string will be processed to 0

Let's clear the data and see how the character type behaves.

Truncate table test_null

Character type inserts null and empty string

Insert into test_null (name) values (null)

Insert into test_null (name) values ('')

The results are as follows:

> select * from test_null

+-+ +

| | id | name |

+-+ +

| | NULL | NULL |

| | NULL |

+-+ +

2 rows in set (0.00 sec)

The handling of empty strings is still special. An empty string is an empty string.

Let's take a look at using length to compare the results of these two fields.

> select length (id), id,length (name), name from test_null

+-+

| | length (id) | id | length (name) | name |

+-+

| | NULL | NULL | NULL | NULL | |

| | NULL | NULL | 0 |

+-+

2 rows in set (0.01sec)

The length of the empty string is 0, while the length of null is still null. The difference between this and Oracle is obvious.

The tests in Oracle are as follows:

Create table test_null (id number,name varchar2 (30))

SQL > insert into test_null values (1Jing null)

1 row created.

SQL > insert into test_null values (2)

1 row created.

SQL > select * from test_null

ID NAME

one

two

SQL > select length (id), id,length (name), name from test_null

LENGTH (ID) ID LENGTH (NAME) NAME

1 1

1 2

At this point, I believe you have a deeper understanding of "the difference between NULL and empty strings in MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.