Mysql Implementation null Value First and Last Method Example

Preface

We already know that MySQL uses the SQL SELECT command and the WHERE clause to read the data in the data table, but this command may not work properly when the supplied query condition field is NULL.

To handle this situation, MySQL provides three large operators:

IS NULL: When the value of the column is NULL, this operator returns true. IS NOT NULL: When the value of the column is not NULL, the operator returns true. < = > The comparison operator (unlike the = operator), which returns true when the two values being compared are NULL.

The conditional comparison operation of NULL is quite special. You cannot use = NULL or! = NULL Looks up the NULL value in the column.

In MySQL, a comparison of the value of NULL with any other value (even NULL) always returns false, i.e. NULL = NULL returns false.

NULL is processed in MySQL using the IS NULL and IS NOT NULL operators.

Recently doing project migration, Oracle version moved to Mysql version, encountered some oracle functions, mysql does not, so we have to customize the function or find a way to replace the function for transformation.

When doing data sorting with oracle, sometimes you can use nulls first or nulls last to rank null first or last.

oracle method:

The null value ranks first

select * from A order by a desc null first

The null value comes last

select * from A order by a desc null last

However, if you move to Mysql, mysql does not provide similar functions, so how to implement them?

Here's a solution:

The null value comes last, using the IF and ISNULL functions of Mysql. Returns 1 if null, 0 if no

select * from A order by IF(ISNULL(a),1,0),a desc

The value of null comes first, and the functions of IF and ISNULL of Mysql are used. Returns 1 if null, 0 if no

select * from A order by IF(ISNULL(a),0,1),a desc

If oracle and Mysql versions are needed in mybatis, you can either send a data table version to identify dbType from the background, or use the _ databaseId method of mybatis directly.

 <if test="dbType=='oracle'">
   order by c.create_date desc nulls last
   </if>
   <if test="dbType=='mysql'">
   order by IF(ISNULL(c.create_date),1,0), c.create_date desc
   </if>

Summarize