mysql中ifnull和coalesce有什么区别? [英] What is the difference bewteen ifnull and coalesce in mysql?

问题描述

select ifnull(null,'replaces the null')
-->replaces the null

select coalesce(null,null,'replaces the null')
-->replaces the null

在这两个子句中，主要区别在于参数传递.对于ifnull，这是两个参数，我们可以通过合并2或3，但是这两个之间是否还有其他区别? 以及它在MSSql中的区别.

In both clauses the main difference is argument passing. For ifnull it's two parameter and coalesce 2 or 3 we can pass,except that do we have any other difference between these two? And how it differs in MSSql.

推荐答案

两者之间的主要区别是IFNULL函数接受两个参数，如果不是NULL则返回第一个，如果第二个则返回第二个是NULL.

The main difference between the two is that IFNULL function takes two arguments and returns the first one if it's not NULL or the second if the first one is NULL.

COALESCE函数可以接受两个或多个参数，并返回第一个非NULL参数；如果所有参数均为null，则返回NULL，例如:

COALESCE function can take two or more parameters and returns the first non-NULL parameter, or NULL if all parameters are null, for example:

SELECT IFNULL('some value', 'some other value');
-> returns 'some value'

SELECT IFNULL(NULL,'some other value');
-> returns 'some other value'

SELECT COALESCE(NULL, 'some other value');
-> returns 'some other value' - equivalent of the IFNULL function

SELECT COALESCE(NULL, 'some value', 'some other value');
-> returns 'some value'

SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'

更新:MSSQL进行更严格的类型和参数检查.此外，它没有IFNULL函数，而是具有ISNULL函数，该函数需要知道参数的类型.因此:

UPDATE: MSSQL does stricter type and parameter checking. Further, it doesn't have IFNULL function but instead ISNULL function, which needs to know the types of the arguments. Therefore:

SELECT ISNULL(NULL, NULL);
-> results in an error

SELECT ISNULL(NULL, CAST(NULL as VARCHAR));
-> returns NULL

此外，MSSQL中的COALESCE函数还要求至少一个参数为非null，因此:

Also COALESCE function in MSSQL requires at least one parameter to be non-null, therefore:

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL);
-> results in an error

SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'

这篇关于mysql中ifnull和coalesce有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！