字符串上的MySql，IFNULL（），COALSESCE（）没有替换

Description:

描述：

My statement should replace every empty title_column with 'no name', but it doesn't:

我的陈述应该用'无名'替换每个空的title_column，但它不会：

SELECT
  COALESCE(main_table.title_column, 'no name') AS title
FROM main_table;

IFNULL() behaves the same way.

IFNULL（）的行为方式相同。

What am I doing wrong ?

我究竟做错了什么？

4 个解决方案

#1

COALESCE and IFNULL substitute only NULL values, your table seem to contain empty strings:

COALESCE和IFNULL仅替换NULL值，您的表似乎包含空字符串：

SELECT
  COALESCE(NULLIF(main_table.title_column, ''), 'no name') AS title
FROM main_table;

#2

In MySQL a NULL string and an empty ('') string are not the same thing.

在MySQL中，NULL字符串和空（''）字符串不是一回事。

You have a few options, for consistency I use CASE...

你有一些选择，为了保持一致性我使用CASE ...

CASE WHEN main_table.title_column = '' THEN 'no name' ELSE main_table.title_column END

Other options could be the COALESCE(NULLIF()) shown on another answer (which uses NULLIF() to turn empty strings into NULLs and then uses coalesce as you wanted to).

其他选项可能是另一个答案上显示的COALESCE（NULLIF（））（它使用NULLIF（）将空字符串转换为NULL，然后根据需要使用合并）。

Or possibly just IIF() to shorten the CASE statement...

或者可能只是IIF（）缩短CASE声明......

IIF(main_table.title_column = '', 'no name', main_table.title_column)

#3

If you prefer a functional way of giving solutions you can always use this:

如果您更喜欢提供解决方案的功能性方法，您可以随时使用：

DELIMITER $$
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
-- Note: add your server side comments to remember what this routine does on your Api
-- --------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS `IfNullOrEmpty`$$
CREATE FUNCTION `IfNullOrEmpty`(s TEXT, value TEXT) RETURNS TEXT
NOT DETERMINISTIC
READS SQL DATA
BEGIN

IF ( (s is null) OR (trim(s) = '')  ) THEN
    return value;
END IF;
return s;    
END$$

Usage:

用法：

SELECT IfNullOrEmpty(t.FieldName,'No Name Given') as Name FROM cms_Table t

#4

used this solution for MySql,

将此解决方案用于MySql，

SELECT IF((TRIM(main_table.title_column) = ''),'no name', COALESCE(main_table.title_column, 'no name')) AS title FROM main_table

#1