Sorting problems with null values in order by in MySQL

An order by ordering in MySQL encountered a problem with a null value MySQL database, when order by ordering, if there is a null value, then NULL is the smallest, ASC is ordered, the null value is at the front. What if we want NULL to be followed by a non-null line in front of the queue?

When designing a MySQL database, if the field allows null values, you need to be aware of those rows that have null values when sorting the field.

We know that the meaning of NULL means nothing, or that it can be understood as "unknown", and that the result of comparison with any value is false,

By default, MySQL considers null values to be smaller than other types of data, that is, NULL is the smallest when order by is ordered, and the null value is at the top of the ASC ordinal order. What if we want NULL to be followed by a non-null line in front of the queue?

Let's take a small example to illustrate the situation.

First, create a test data table Test_user,

Mysql> CREATE TABLE Test_user (

ID int unsigned NOT NULL auto_increment,

Username varchar (TEN) is not NULL,

Age int,

Primary key (ID))

Engine=myisam default Charset=utf8;

Query OK, 0 rows affected (0.14 sec)

Fills 6 data, where 3 sets the age value and the other 3 age values are null

mysql> INSERT INTO Test_user values (1, ' user1 ', +), (2, ' user2 ', 30);

Query OK, 2 rows Affected (0.00 sec) Records:2 duplicates:0 warnings:0

mysql> INSERT into Test_user (username) VALUES (' User3 '), (' User4 '), (' User5 ');

Query OK, 3 Rows Affected (0.00 sec) Records:3 duplicates:0 warnings:0

mysql> INSERT INTO Test_user values (6, ' User6 ', 23);

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Test_user;

+----+-----------+------+ | ID | Username | Age | +----+-----------+------+

| 1 | User1 | 28 |

| 2 | User2 | 30 |

| 3 | User3 | NULL |

| 4 | User4 | NULL |

| 5 | User5 | NULL |

| 6 | User6 | 23 |

+----+-----------+------+ 6 rows in Set (0.00 sec)

We sort from small to large according to the age field, and we see that the null value is the smallest, top of the line.

Mysql> SELECT * from Test_user order by age;

+----+-----------+------+ | ID | Username | Age | +----+-----------+------+

| 3 | User3 | NULL | | 4 | User4 | NULL |

| 5 | User5 | NULL |

| 6 | User6 | 23 |

| 1 | User1 | 28 |

| 2 | User2 | 30 |

+----+-----------+------+ 6 rows in Set (0.00 sec)

According to the age field from the big to the small sort, we see that the null value is indeed the smallest, the last side of the line

Mysql> SELECT * from Test_user order BY age Desc;

+----+-----------+------+ | ID | Username | Age | +----+-----------+------+

| 2 | User2 | 30 |

| 1 | User1 | 28 |

| 6 | User6 | 23 |

| 3 | User3 | NULL |

| 4 | User4 | NULL |

| 5 | User5 | NULL |

+----+-----------+------+ 6 rows in Set (0.00 sec)

One thing to note here is that the null value itself cannot be sorted, meaning that a null cannot be compared to another null. You may have found that the three null values of the rows, either in the positive or reverse order, are consistent in sequence and may not be consistent.

What if we want the record of the null value to be displayed on the last side of the sequence of age?

1. Regenerate a column, such as Agenull, using the IS null operator to change the row of the null value to 1, the non-null value of the row to 0, sort the field first, and then the age sort

Mysql> Select *,age is null as Agenull from Test_user order by Agenull,age; +----+-----------+------+---------+ | ID | Username | Age | Agenull | +----+-----------+------+---------+ | 6 | User6 | 23 | 0 | | 1 | User1 | 28 | 0 | | 2 | User2 | 30 | 0 | | 3 | User3 | NULL | 1 | | 4 | User4 | NULL | 1 | | 5 | User5 | NULL | 1 | +----+-----------+------+---------+ 6 rows in Set (0.01 sec)

2, directly using the ISNULL function to evaluate the age column, the first method is the same reason

Mysql> SELECT * from Test_user ORDER by IsNull (age); +----+-----------+------+ | ID | Username | Age | +----+-----------+------+ | 6 | User6 | 23 | | 1 | User1 | 28 | | 2 | User2 | 30 | | 3 | User3 | NULL | | 4 | User4 | NULL | | 5 | User5 | NULL | +----+-----------+------+ 6 rows in Set (0.00 sec)

3, you can also use a small trick in MySQL, in front of the field with a minus sign, that is, the minus sign, ASC to DESC, DESC changed to ASC

Mysql> SELECT * from Test_user order by-age desc; +----+-----------+------+ | ID | Username | Age | +----+-----------+------+ | 6 | User6 | 23 | | 1 | User1 | 28 | | 2 | User2 | 30 | | 3 | User3 | NULL | | 4 | User4 | NULL | | 5 | User5 | NULL | +----+-----------+------+ 6 rows in Set (0.00 sec)

Therefore, when designing a database, it is best not to null if a field is to be sorted.

Source: http://www.th7.cn/db/mysql/201606/195206.shtml

From for notes (Wiz)

Sorting problems with null values in order by in MySQL