MySQL 具有不同分隔符的多重串联 - CONCAT_WS

发布于 2025-01-02 23:37:04 字数 651 浏览 1 评论 0原文

我正在动态构建一个具有多个字段和不同分隔符的 MySQL 连接。如果当前连接的字段为空或为空，我想删除分隔符。

$concat_string = "CONCAT_WS('".$infix_array[0]."'," . $concat_fields_array[0] . "," . $concat_fields_array[1] . ")";
$i = 0;
foreach($concat_fields_array as $concat_field){
    if($i >= 2){
         $concat_string = "CONCAT_WS('".$infix_array[$i-1]."'," . $concat_string . "," . $concat_field . ")";
    }
    $i++;
}

这会构建一个嵌套的 CONCAT_WS，如下所示：

 CONCAT_WS(', ',CONCAT_WS(' - ',CONCAT_WS(':',CONCAT_WS(' ',field1,field2),field3),field4),field5)

将字段包装在 NULLIF 中（例如 NULLIF(field1, '') ）会消除额外的中缀吗？

有没有更快的函数来完成这个任务？

原文

I am dynamically building a MySQL concatenation with multiple fields and different separators. I'd like to drop the separator if the currently concatenated field is blank or null.

$concat_string = "CONCAT_WS('".$infix_array[0]."'," . $concat_fields_array[0] . "," . $concat_fields_array[1] . ")";
$i = 0;
foreach($concat_fields_array as $concat_field){
    if($i >= 2){
         $concat_string = "CONCAT_WS('".$infix_array[$i-1]."'," . $concat_string . "," . $concat_field . ")";
    }
    $i++;
}

This builds a nested CONCAT_WS like:

 CONCAT_WS(', ',CONCAT_WS(' - ',CONCAT_WS(':',CONCAT_WS(' ',field1,field2),field3),field4),field5)

Would wrapping the fields in NULLIF like NULLIF(field1, '') get rid of extra infixes?

Is there a faster function to accomplish this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吃兔兔 2025-01-09 23:37:04

NULL 字段不会有问题，因为 CONCAT_WS 将跳过 NULL 值（连同分隔符）。对于空字符串（不会被跳过），您可以在执行此操作之前将它们转换为 NULL。如果您无法更新数据库本身，请进行如下查询：

select <your expression> from (select case when col1 = '' then null else col1 end as col1 from my_table);

因此，只需将其即时转换为 null 并从该内部查询中进行选择即可。

为了使这项工作有效，您必须使用 nullif，因此不要使用：

CONCAT_WS(a, CONCAT_WS(c, d))

use

NULLIF(CONCAT_WS(a, NULLIF(CONCAT_WS(c, d), '')), '')

我认为您不会找到更快的方法。另一种选择可能是只选择这些字段，然后在 PHP 端进行串联，但只有当您生成的串联不是查询中某些 WHERE 子句的一部分时，才有可能这样做。

You don't have a problem with NULL fields, because CONCAT_WS will skip NULL values (together with separator). Regarding empty strings (which will not be skipped), you can just turn them into NULLs before doing this. If you cannot update the database itself, then make a query like:

select <your expression> from (select case when col1 = '' then null else col1 end as col1 from my_table);

so, just convert it to null on the fly and make your select from that inner query.

In order to make this work, you will have to use nullif, so instead of:

CONCAT_WS(a, CONCAT_WS(c, d))

use

NULLIF(CONCAT_WS(a, NULLIF(CONCAT_WS(c, d), '')), '')

I don't think that you will find a faster way of doing that. Another option might be to just select those fields and then make concatenation on the PHP side, but that is possible only if your resulting concatenation is not part of some WHERE clause further in your query.

回复收藏 0 原文

~没有更多了~