MySQL UPDATE 将数据附加到列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14020867/
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
MySQL UPDATE append data into column
提问by Robert Hoffmann
I need to UPDATE tablename (col1name)
我需要更新表名（col1name）
If there is already data, I need to append it with values 'a,b,c' If it is NULL, I need to add the values 'a,b,c'
如果已经有数据，我需要附加值 'a,b,c' 如果它是 NULL，我需要添加值 'a,b,c'
I know there is a CONCAT argument, but not sure what the SQL syntax would be.
我知道有一个 CONCAT 参数，但不确定 SQL 语法是什么。
update tablename set col1name = concat(ifnull(col1name, 'a,b,c'), 'a,b,c')
update tablename set col1name = concat(ifnull(col1name, 'a,b,c'), 'a,b,c')
Is the above correct?
以上正确吗？
回答by Dhinakar
Try this Query:
试试这个查询：
update tablename set col1name = concat(ifnull(col1name,""), 'a,b,c');
回答by Bohemian
This should do it:
这应该这样做：
update tablename set
col1name = if(col1name is null, 'a,b,c', concat(col1name, 'a,b,c'));
Or you could make your life easier by doing it in two steps:
或者您可以分两步完成，让您的生活更轻松：
update tablename set col1name = '' where col1name is null;
then
然后
update tablename set col1name = concat(col1name, 'a,b,c');
回答by Taryn
You can use the following:
您可以使用以下内容：
update yourtable
set yourcol = case when yourcol is null then 'a,b,c'
else concat(yourcol, ' a,b,c') end
Sample data:
样本数据：
CREATE TABLE yourtable(`yourcol` varchar(50));
INSERT INTO yourtable(`yourcol`)
VALUES ('sadsdh'),
(NULL);
Will return:
将返回：
| YOURCOL |
----------------
| sadsdh a,b,c |
| a,b,c |
回答by MrMesees
IFNULL(column,''), saves any if statements, makes the SQL much simpler!
IFNULL(column,'')，保存任何if语句，使SQL更简单！
MySQL 5.6 Schema Setup:
MySQL 5.6 架构设置：
CREATE TABLE tablename
(`yourcol` varchar(50))
;
INSERT INTO tablename
(`yourcol`)
VALUES
('sadsdh'),
(NULL)
;
UPDATE tablename SET
yourcol = CONCAT( IFNULL(yourcol,' '), 'somevalue' )
;
查询：
select *
from tablename
结果：
| yourcol |
|-----------------|
| sadsdhsomevalue |
| somevalue |
回答by Rishabh_hurr
why are you write ifnull function: it is obvious that if col1name1 is empty it concatenate to null means null+'a,b,c' simply 'a,b,c' set col1name = concat(ifnull(col1name,""), 'a,b,c') instead of this you can directly write set col1name = concat(col1name, 'a,b,c')
你为什么要写 ifnull 函数：很明显，如果 col1name1 为空，它连接到 null 意味着 null+'a,b,c' 只是 'a,b,c' set col1name = concat(ifnull(col1name,""), ' a,b,c') 而不是这个你可以直接写 set col1name = concat(col1name, 'a,b,c')
