MySQL SQL UPDATE 字段中的所有值，附加字符串 CONCAT 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4128335/
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
SQL UPDATE all values in a field with appended string CONCAT not working
提问by Fresheyeball
Here is what I want to do:
这是我想要做的：
current table:
当前表：
+----+-------------+
| id | data |
+----+-------------+
| 1 | max |
| 2 | linda |
| 3 | sam |
| 4 | henry |
+----+-------------+
Mystery Query ( something like "UPDATE table SET data = CONCAT(data, 'a')")
神秘查询（类似"UPDATE table SET data = CONCAT(data, 'a')"）
resulting table:
结果表：
+----+-------------+
| id | data |
+----+-------------+
| 1 | maxa |
| 2 | lindaa |
| 3 | sama |
| 4 | henrya |
+----+-------------+
thats it! I just need to do it in a single query, but can't seem to find a way. I am using mySQL on bluehost (I think its version 4.1)
就是这样！我只需要在一个查询中完成它，但似乎找不到方法。我在 bluehost 上使用 mySQL（我认为它的版本是 4.1）
Thanks everyone.
谢谢大家。
回答by Marc B
That's pretty much all you need:
这几乎就是你所需要的：
mysql> select * from t;
+------+-------+
| id | data |
+------+-------+
| 1 | max |
| 2 | linda |
| 3 | sam |
| 4 | henry |
+------+-------+
4 rows in set (0.02 sec)
mysql> update t set data=concat(data, 'a');
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from t;
+------+--------+
| id | data |
+------+--------+
| 1 | maxa |
| 2 | lindaa |
| 3 | sama |
| 4 | henrya |
+------+--------+
4 rows in set (0.00 sec)
Not sure why you'd be having trouble, though I am testing this on 5.1.41
不知道为什么你会遇到问题，虽然我正在 5.1.41 上测试这个
回答by andrejc
CONCAT with a null value returns null, so the easiest solution is:
带有 null 值的 CONCAT 返回 null，因此最简单的解决方案是：
UPDATE myTable SET spares = IFNULL (CONCAT( spares , "string" ), "string")
更新 myTable SET 备件 = IFNULL（CONCAT（备件，“字符串”），“字符串”）
回答by DS_web_developer
UPDATE mytable SET spares = CONCAT(spares, ',', '818') WHERE id = 1
not working for me.
不为我工作。
spares is NULLby default but its varchar
备件是NULL默认的，但它的varchar
回答by Rohan Khude
回答by Jeremy Thille
UPDATE
myTable
SET
col = CONCAT( col , "string" )
Could not work it out. The request syntax was correct, but "0 line affected" when executed.
无法解决。请求语法正确，但执行时“0 行受影响”。
The solution was :
解决方案是：
UPDATE
myTable
SET
col = CONCAT( myTable.col , "string" )
That one worked.
那个有效。
回答by Fresheyeball
Solved it. Turns out the column had a limited set of characters it would accept, changed it, and now the query works fine.
解决了。原来该列有一组有限的字符，它可以接受，更改它，现在查询工作正常。
回答by Eric
You can do this:
你可以这样做：
Update myTable
SET spares = (SELECT CASE WHEN spares IS NULL THEN '' ELSE spares END AS spares WHERE id = 1) + 'some text'
WHERE id = 1
field = field + value does not work when field is null.
field = field + value 在 field 为空时不起作用。
