MySQL，空值的合并等价物？

我知道聚合将返回传递给它的第一个非空值。是否有类似的东西会返回第一个非空/非假值？

例如:

select FunctionIWant(0,'','banana') as fruit;  //returns banana.
26910 次浏览
小开
最佳答案

Use the ANSI CASE statement/expression:

SELECT CASE
WHEN LENGTH(col) = 0 OR col IS NULL THEN 'banana'
ELSE col
END AS fruit

There's no boolean in SQL, or MySQL. MySQL actually stores the value as an INT, values zero or one:

SELECT CASE
WHEN col = 0 THEN 'banana'
ELSE col
END AS fruit
小开

There is no such function in MySQL, but you can develop your own stored function. The main difficulty here is that the number of parameters to pass to the function can vary.

One possible solution could be to pass a string with a set of values separated by a separator by using the CONCAT_WS function (see CONCAT_WS() function). You would also have to define a string delimiter in case your separator would be included in one of the values of the set.

Here is an example script:

DELIMITER |;
CREATE FUNCTION MY_COALESCE (
p_set_string TEXT
,p_delimiter CHAR(1)
) RETURNS TEXT
DETERMINISTIC
BEGIN


RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(
REPLACE(REPLACE(
CONCAT(p_delimiter,p_set_string,p_delimiter)
,CONCAT(p_delimiter,'0',p_delimiter),'')
,CONCAT(p_delimiter,p_delimiter),'')
,p_delimiter,2),p_delimiter,-1)
;
END;
|;
DELIMITER ;


SET @separator=',', @delimiter='$';
SELECT
MY_COALESCE(
CONCAT_WS(CONCAT(@delimiter,@separator,@delimiter),0,'','banana')
,@delimiter
) as fruit
;

When running the previous script you get the following output:

MySQL> --------------
MySQL> SET @separator=',', @delimiter='$'
MySQL> --------------
MySQL>
MySQL> Query OK, 0 rows affected (0.00 sec)
MySQL>
MySQL> --------------
MySQL> SELECT
MySQL>   MY_COALESCE(
MySQL>       CONCAT_WS(CONCAT(@delimiter,@separator,@delimiter),0,'','banana')
MySQL>      ,@delimiter
MySQL>     ) as fruit
MySQL> --------------
MySQL>
MySQL> +--------+
MySQL> | fruit  |
MySQL> +--------+
MySQL> | banana |
MySQL> +--------+
MySQL> 1 row in set (0.02 sec)

You can of course adapt the value of the string delimiter so there won't be any conflicts with your set values.

小开

You could make NULL from empty string in MySQL:

SELECT coalesce(NULLIF(email, ''), 'user@domain.example') FROM users WHERE id=1000000;
小开

This worked for me:

SELECT IF(myValue > 0, myValue, 'empty string') AS Value FROM myTable;

提交答案