Shulou(Shulou.com)06/01 Report--

Preface

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

To handle this situation, MySQL provides three operators:

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

The conditional comparison operation on NULL is quite special. You cannot use = NULL or! = NULL to find the NULL value in a column.

In MySQL, the comparison of the NULL value with any other value, even if it is NULL, always returns false, that is, NULL = NULL returns false.

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

Recently in the project migration, the Oracle version moved to the Mysql version, encountered some oracle functions, mysql does not have, so I have to think of custom functions or find a way to replace the function to transform.

When oracle sorts data, it is sometimes possible to use nulls first or nulls last to rank null values first or last.

Oracle method:

Null is at the top of the list.

Select * from An order by a desc null first

Null came last.

Select * from An order by a desc null last

However, if you move to Mysql, mysql does not provide a similar function, so how do you implement it?

Here are the solutions:

The null value comes last, using the IF and ISNULL functions of Mysql. If 1 is returned as empty, 0 is returned.

Select * from An order by IF (ISNULL (a), 1pm 0), a desc

The null value is at the top of the list, using Mysql's IF and ISNULL functions. If 1 is returned as empty, 0 is returned.

Select * from An order by IF (ISNULL (a), 0jue 1), a desc

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

Order by c.create_date desc nulls last order by IF (ISNULL (c.create_date), 1d0), c.create_date desc

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.