Mysql implements null value first/last method example

Preface

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

To handle this situation, MySQL provides three major operators:

• IS NULL: This operator returns true when the column value is NULL.
• IS NOT NULL: The operator returns true when the column value is not NULL.
• <=>: A comparison operator (different from the = operator) that returns true if both values ​​being compared are NULL.

Conditional comparison operations on NULL are special. You cannot use = NULL or != NULL to look for NULL values ​​in a column.

In MySQL, a comparison of a NULL value with any other value (even NULL) always returns false, that is, NULL = NULL returns false.

MySQL handles NULL using the IS NULL and IS NOT NULL operators.

I'm currently migrating a project from Oracle to MySQL. I encountered some Oracle functions that MySQL doesn't have, so I had to customize the functions or find a way to replace the functions for the transformation.

When Oracle sorts data, it can sometimes use nulls first or nulls last to put null values ​​first or last.

Oracle method:

Null values ​​are sorted first

select * from A order by a desc null first

Null values ​​are sorted last

select * from A order by a desc null last

However, if you migrate to MySQL, MySQL does not provide a similar function, so how can you implement it?

Here is the solution:

Null values ​​are sorted last, using MySQL's IF and ISNULL functions. If it is empty, it returns 1, otherwise it returns 0

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

Null values ​​are sorted first, using MySQL's IF and ISNULL functions. If it is empty, it returns 1, otherwise it returns 0

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

If Oracle and MySQL versions are needed in mybatis, you can either pass a data table version identifier dbType from the background, or directly use the _databaseId method of mybatis.

 <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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.