MySQL CONCAT 多个字段到单个字段，单行距
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13712423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CONCAT multiple fields to a single field, single spaced
提问by kylex
I'm trying to concatenate a firstmiddlemaidenand lastname fields and use that to update a single field which is named fullname
我正在尝试连接 afirstmiddlemaiden和lastname 字段并使用它来更新名为的单个字段fullname
For each user any combination of these 4 fields can be filled. From 0 to all 4. However I also need a single space between each name (not multiple spaces).
对于每个用户，可以填写这 4 个字段的任意组合。从 0 到所有 4。但是我还需要每个名称之间有一个空格（不是多个空格）。
UPDATE nameTable SET fullname = CONCAT(first, middle, maiden, last);
回答by akatakritos
MySQL has CONCAT_WS- concatenate with separator
MySQL 有CONCAT_WS- 与分隔符连接
CONCAT_WS(' ', first, middle, maiden, last);
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
As pointed out by andrbelow, make sure any concatenated fields contain NULLand not an empty string ('') otherwise you will get a double space in the output.
正如andr下面所指出的，确保任何连接的字段都包含NULL而不是空字符串 ( '') 否则您将在输出中得到双倍空格。
Fiddle: http://sqlfiddle.com/#!2/1fe83/1
小提琴：http://sqlfiddle.com/#!2/1fe83/1
Further Application
进一步应用
Be careful therefore if in the future you use this function to make a small CSV list, because you won't get the comma for a NULLfield. You'd have to do a COALESCE(column, '')wrapper around each nullable column.
因此，如果将来您使用此函数创建一个小的 CSV 列表，请小心，因为您不会得到NULL字段的逗号。您必须对COALESCE(column, '')每个可为空的列进行包装。
回答by jasz24
empty string solution
TRIM(BOTH ' ' FROM CONCAT_WS(' ', first, middle, maiden, last))
空字符串解决方案
TRIM(BOTH ' ' FROM CONCAT_WS(' ', first, middle, maiden, last))
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
