MYSQL:使用多个字段合并更新字段 [英] MYSQL: Update field with concat of multiple fields

问题描述

我正在尝试使用同一表的某些字段的CONCAT更新表的字段.

I'm trying to update a field of my table with the CONCAT of the some fields of the same table.

这是

UPDATE tabex SET field1=CONCAT(tabex.a1,', ',tabex.a2,', ',tabex.a3,', ',tabex.a4,', ',tabex.a5,', ',tabex.a6,', 'tabex.a7,', ',tabex.a8,', ',tabex.a9 );

此查询有0行受影响，没有错误.

This query has 0 rows affected and no errors.

通过其他查询

UPDATE tabex SET field1=CONCAT_WS(tabex.a1,', ',tabex.a2,', ',tabex.a3,', ',tabex.a4,', ',tabex.a5,', ',tabex.a6,', 'tabex.a7,', ',tabex.a8,', ',tabex.a9 );

如果某些a(n)字段的内容为NULL，则mysql会放置上一个结果的副本

If the content of some of a(n) fields is NULL mysql puts a copy of the previous result

有人可以帮助我吗?

推荐答案

查询时

UPDATE tabex SET field1=CONCAT(tabex.a1,', ',tabex.a2,', ',tabex.a3,', ',tabex.a4,', ',tabex.a5,', ',tabex.a6,', 'tabex.a7,', ',tabex.a8,', ',tabex.a9 );

不会影响行，唯一的解释是表是空的.它将更新表中的每一行.但是，如果其中一列为NULL，则您的field1列也将为NULL.
为了避免这种情况，您必须使用COALESCE()函数.此函数返回其第一个参数非NULL.

doesn't affect a row, the only explanation would be, that the table is empty. It would update every row in the table. But if one of the columns is NULL, your field1 column will also be NULL.
To avoid that, you have to use the COALESCE() function. This function returns the first of its parameters which is not NULL.

UPDATE tabex SET field1=CONCAT(COALESCE(tabex.a1, ''),', ',...);

在旁注中，我不得不问，为什么要这么做.在大多数情况下，列中用逗号分隔的值是一个坏主意.

On a sidenote I have to ask, why you want to do this. Comma separated values in columns are a bad idea most of the times.

最后，使用CONCAT_WS()的查询是错误的.函数名称中的_WS是带分隔符"的缩写，因此第一个参数是分隔符，然后将其置于函数的其他参数之间.所以你应该这样写:

And finally, your query using CONCAT_WS() is wrong. The _WS in the function name is short for "with separator", so the first parameter is the separator which then is placed between the other parameters of the function. So you should write it like this:

UPDATE tabex SET field1=CONCAT_WS(',', tabex.a1, tabex.a2, tabex.a3,...);

CONCAT_WS()函数的另一个优点是，它忽略NULL值.在手册中详细了解这两个函数. a>.

Another advantage of the CONCAT_WS() function is, that it ignores NULL values. Read more about the two functions in the manual.

这篇关于MYSQL:使用多个字段合并更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！