MySQL,空值的联合等价物?

使用ANSI CASE statement/expression :

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

SQL或MySQL中没有布尔值。MySQL实际上将值存储为INT,值为0或1:

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

在MySQL中,可以将空字符串设为空:

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

一种可能的解决方案是通过使用 CONCAT_WS CONCAT_WS() function ). 如果分隔符包含在集合的某个值中,则还必须定义字符串分隔符。

下面是一个脚本示例:

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
;

运行上一个脚本时,将获得以下输出:

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)

这对我有效:

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