Summary of techniques for using coalesce() in mysql (must read)

In mysql, there are actually many methods and functions that are very useful. This time I introduce one called coalesce. It is very troublesome to spell, but its function is to pass the returned The first non-null value in the entered parameter. The following article mainly introduces relevant information on the usage techniques of coalesce() in mysql. Friends in need can refer to it.

Preface

Recently I accidentally discovered coalesce of mysql, and I just had time, so I added mysqlcoalesce()## The usage skills of # are summarized and shared with everyone. Let’s take a look at the detailed introduction:

coalesce() explanation

In the return parameters The first non-empty

expression (and so on from left to right);

Usage examples

Three

variablesa,b,c.

select coalesce(null,2,3); // Return 2

select coalesce(null,null,3); // Return 3

select coalesce(1,2,3); // Return 1

As can be seen from the above example, its function is to return the first non-null value in the passed parameter, another example is

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1); 
-- Return 1

If all the parameters passed in are null, null will be returned, such as

SELECT COALESCE(NULL, NULL, NULL, NULL); 
-- Return NULL

The occasion when this parameter is used is: If a field defaults to null, If you want it to return not null, but 0 or other values, you can use this function

SELECT COALESCE(字段名,0) as value from t;//（如果数据库提成字段默认值不是为0值的话肯定是开发那个的错，个人观点请勿喷哈）。

Summary

The above is the detailed content of Summary of techniques for using coalesce() in mysql (must read). For more information, please follow other related articles on the PHP Chinese website!