mysql update field_MYSQL: Update field with concat of multiple fields

问题

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

Whith this

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

This query has 0 rows affected and no errors.

With this other query

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

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

Someone can help me?

回答1:

When this query

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

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.

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,...);

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

来源：https://stackoverflow.com/questions/19836365/mysql-update-field-with-concat-of-multiple-fields