Mysql函数转换非空值

时间:2022-10-18 10:19:40

I want to know if mysql have a function which receives two arguments, if the first is null, it returns null otherwise it returns the second.

我想知道mysql是否有一个接收两个参数的函数,如果第一个为null,则返回null,否则返回第二个。

It would be a shortcut for if(something is null, null, anotherthing).

这将是if(某事为null,null,anotherthing)的捷径。

1 个解决方案

#1


1  

Unfortunately there is no function for yor behavior, but it is simple to create your own function if you have the permissions for that, other wise you could also add an IF THEN ELSE to your query like you already showed but that would be no answer for your question. If you want to create a function then you will have the problem that you need explicit parameter types and with mysql it is unfortunately also not functional to overload a function (same function name but different parameter types). So you need for different types different functions with different function names. It would look like that:

不幸的是,没有yor行为的功能,但是如果你拥有该权限就很容易创建自己的功能,另外你也可以像你已经展示的那样在你的查询中添加一个IF THEN ELSE但是这样就没有答案了。你的问题。如果你想创建一个函数,那么你将遇到需要显式参数类型的问题,而且对于重载函数(相同的函数名称但不同的参数类型),遗憾的是它也不起作用。因此,您需要具有不同功能名称的不同类型的不同功能。它看起来像那样:

    CREATE FUNCTION `fmap_varchar`(e1 VARCHAR(255), e2 VARCHAR(255) )
    RETURNS VARCHAR(255) DETERMINISTIC
    RETURN IF(e1 IS NULL, NULL, e2);

You would have maybe also to take care for the correct charset which you can also specify at the returns clause.

您可能还需要注意正确的字符集,您也可以在returns子句中指定它。

And the solution with a query you gave already:

您已经提供的查询解决方案:

    SELECT IF(something IS NULL, NULL, anotherthing) FROM your_table;

#1


1  

Unfortunately there is no function for yor behavior, but it is simple to create your own function if you have the permissions for that, other wise you could also add an IF THEN ELSE to your query like you already showed but that would be no answer for your question. If you want to create a function then you will have the problem that you need explicit parameter types and with mysql it is unfortunately also not functional to overload a function (same function name but different parameter types). So you need for different types different functions with different function names. It would look like that:

不幸的是,没有yor行为的功能,但是如果你拥有该权限就很容易创建自己的功能,另外你也可以像你已经展示的那样在你的查询中添加一个IF THEN ELSE但是这样就没有答案了。你的问题。如果你想创建一个函数,那么你将遇到需要显式参数类型的问题,而且对于重载函数(相同的函数名称但不同的参数类型),遗憾的是它也不起作用。因此,您需要具有不同功能名称的不同类型的不同功能。它看起来像那样:

    CREATE FUNCTION `fmap_varchar`(e1 VARCHAR(255), e2 VARCHAR(255) )
    RETURNS VARCHAR(255) DETERMINISTIC
    RETURN IF(e1 IS NULL, NULL, e2);

You would have maybe also to take care for the correct charset which you can also specify at the returns clause.

您可能还需要注意正确的字符集,您也可以在returns子句中指定它。

And the solution with a query you gave already:

您已经提供的查询解决方案:

    SELECT IF(something IS NULL, NULL, anotherthing) FROM your_table;