ORDER BY statement in MySQL ordering of NULL fields

By default, MySQL counts null as the minimum value.

If you want to manually specify the order of NULL, you can use:

Place NULL in the top-most:

if (IsNull (field name), 0,1) ASC//ASC can be omitted

Put NULL in the last

if (IsNull (field name), 0,1) DSC

if (IsNull (field name), 1,0) ASC//ASC can be omitted

For the understanding of this use method:

Take if (IsNull (field name), 0, 1) as an example, it means that the field is divided into two parts based on whether the null value, the value of the null row is equivalent to an implied sort attribute 0, the value of a non-null row equivalent to get an implicit sorting property 1, in the sort of time, The ascending arrangement is based on this implied attribute first, because if (isnull (field name), 0, 1) ASC omits ASC, so naturally, those rows with the sort field null (the implied sort property) are at the top.

For the understanding of forcing Null to be put in the last statement, and so on.

Here's a test on MySQL 5.6:

The structure of the tested table is as follows:

Create Table  intprimarykeyint);

There are already 6 data in the table, namely:

1. Implementation:

Select *  from test order by Num;

The results are as follows:

2. Implementation:

Select *  from Order  by desc;

The results are as follows:

The 1th, 2-step results show that NULL is minimized by default.

3. Implementation:

Select *  from Order  by if (isnull(num),0,1), num;

The results are as follows:

4. Implementation:

Select *  from Order  by if (isnull(num),0,1desc;

The results are as follows:

The 3rd, 4-step results show that null values are forced to be specified at the top, regardless of whether the subsequent num field is ascending or descending, through if (IsNull (num), 0, 1).

5. Implementation:

Select *  from Order  by if (isnull(num),1,0), num;

The results are as follows:

6. Implementation:

Select *  from Order  by if (isnull(num),1,0desc;

The results are as follows:

The 3rd, 4-step results show that null values are forced to be specified last, regardless of whether the subsequent num field is ascending or descending, through if (IsNull (num), 1,0).

ORDER BY statement in MySQL ordering of NULL fields