如何在 MySQL 中使用 Coalesce

How to use Coalesce in MySQL

提问by Belmark Caday

A little help here. I really don't understand how to use this coalesce in MySQL

这里有一点帮助。我真的不明白如何在 MySQL 中使用这种合并

I have read all the pages in page 1 result of how to use coalsecein google result.

我已经阅读了how to use coalsece谷歌结果的第 1 页结果中的所有页面。

I know its meaning that it returns the first non-null value it encounters and null otherwise.

我知道它的意思是它返回它遇到的第一个非空值，否则返回空值。

But it's still vague for me.

但对我来说仍然很模糊。

1. How come I saw queries that returns multiple values? Isn't it only the first not null value that is returned?
2. And how do it decide which column to base? coalesce(column1,column2)? what if first column is null and other column is not null?
3. Or if I'm wrong or my syntax is wrong, how do i properly write it?
4. Can someone provide a very good and simple example on how to use it?
5. And when it is desirable to use.

1. 我怎么看到返回多个值的查询？不是只返回第一个非空值吗？
2. 它如何决定以哪一列为基础？coalesce(column1,column2)? 如果第一列为空而其他列不为空怎么办？
3. 或者，如果我错了或我的语法错误，我该如何正确地编写它？
4. 有人可以提供一个关于如何使用它的非常好的和简单的例子吗？
5. 并在需要时使用。

回答by eggyal

1. How come i saw queries that returns multiple values? isnt it only the first not null value that is returned?

   Yes, it is only the first non-NULL value that is returned. You must be mistaken about the queries you have seen where you thought that was not the case: if you could show us an example, we might be able to help clarify the misunderstanding.

2. And how do it decide which column to base? coalesce(column1,column2)? what if first column is null and other column is not null?

   In order of its arguments: in this example, column1before column2.

3. Or if im wrong or my syntax is wrong, how do i properly write it?

   You're not wrong.

4. Can someone provide a very good and simple example on how to use it?

   Taken from the documentation:

   
   mysql> SELECT COALESCE(NULL,1);
           -> 1
   mysql> SELECT COALESCE(NULL,NULL,NULL);
           -> NULL
   

5. And when it is desirable to use.

   It is desirable to use whenever one wishes to select the first non-NULL value from a list.

1. 我怎么看到返回多个值的查询？是不是只有第一个返回的非空值？

   是的，它只是返回的第一个非 NULL 值。您一定是错误地看到了您认为并非如此的查询：如果您可以向我们展示一个示例，我们也许能够帮助澄清误解。

2. 它如何决定以哪一列为基础？合并（第 1 列，第 2 列）？如果第一列为空而其他列不为空怎么办？

   按照其参数的顺序：在本例中，column1before column2。

3. 或者，如果我错了或我的语法错误，我该如何正确编写？

   你没有错。

4. 有人可以提供一个关于如何使用它的非常好的和简单的例子吗？

   取自文档：

   
   mysql> SELECT COALESCE(NULL,1);
           -> 1
   mysql> SELECT COALESCE(NULL,NULL,NULL);
           -> NULL
   

5. 并在需要时使用。

   无论何时希望从列表中选择第一个非 NULL 值，都需要使用。

回答by crazeyez

I personally use coalesce when I want to find the first column that isn't blank in a row from a priority list.

当我想从优先级列表中找到一行中不是空白的第一列时，我个人使用了coalesce。

Say for example I want to get a phone number from a customer table and they have 3 columns for phone numbers named mobile, home and work, but I only want to retrieve the first number that isn't blank.

举例来说，我想从客户表中获取一个电话号码，他们有 3 列用于名为 mobile、home 和 work 的电话号码，但我只想检索第一个不为空的号码。

In this instance, I have the priority of mobile, then home and then work.

在这种情况下，我的优先级是移动，然后是家，然后是工作。

TABLE STRUCTURE
--------------------------------------------
| id | customername | mobile | home | work |
--------------------------------------------
| 1  | Joe          | 123    | 456  | 789  |
--------------------------------------------
| 2  | Jane         |        | 654  | 987  |
--------------------------------------------
| 3  | John         |        |      | 321  |
--------------------------------------------

SELECT id, customername, COALESCE(mobile, home, work) AS phone FROM customers

RESULT
------------------------------
| id | customername | phone  |
------------------------------
| 1  | Joe          | 123    |
------------------------------
| 2  | Jane         | 654    |
------------------------------
| 3  | John         | 321    |
------------------------------

回答by Andrew

COALESCE will return the first non-null column or value.

COALESCE 将返回第一个非空列或值。

Example Usage:

示例用法：

SELECT COALESCE(my_column, my_other_column, 'default') as username FROM my_table;

Example results:

结果示例：

my_column  my_other_column     results
null       null                'default'
null       0                   '0'
null       'jimmy'             'jimmy'
'bob'      'jimmy'             'bob'

回答by Pec1983

This is a perfect example of the Coalesceif will change the nullfields/columns to what you want them to be in the case from nullto 0and 1.

这是Coalesceif 将null字段/列更改为您希望它们在从nullto0和的情况下的完美示例1。

Copy this into a mysql editor it will look a lot better

将其复制到 mysql 编辑器中，它看起来会好很多

Select "Column1 (Dont Want Touched)", 
  coalesce(column2(That you want set to 0 if null), 0) as column2 /* (give it same name as was e.g. "column2") */, 
  coalesce(column3(Instead of null set to 1) ,1) as column3 /* (give it same name as was e.g. "column3") */ 
from "MydataTable" 
Where 'somedates' in ('2015-04-10', '2015-04-03', '2015-03-27', '2015-04-17')  and id = 10 order by 'somedates';