MYSQL - 根据列是否为空来追加或插入值

As title says, im trying to append a string to a VARCHAR column in my table. The string is something like " //string ", forward slashes will be used later to explode the string to an array in PHP. I was wondering if there's a way in MYSQL to perform a CONCAT(columnname, "//string") if the column is empty, otherwise perform a normal UPDATE ... SET ... WHERE . In this way, i will avoid the first value of my future exploded string to be a "//string" with forward slahes.

正如标题所说,我试图将一个字符串附加到我表中的VARCHAR列。该字符串类似于“// string”,稍后将使用正斜杠将字符串分解为PHP中的数组。我想知道如果列为空,MYSQL中是否有一种方法可以执行CONCAT(columnname,“// string”),否则执行正常的UPDATE ... SET ... WHERE。通过这种方式,我将避免我未来爆炸字符串的第一个值为带有正斜面的“//字符串”。

also, above I 've used bold characters for "in MYSQL" because I know i could first query the DB (to check if the column is empty) with something like:

另外,上面我使用了“在MYSQL中”的粗体字符,因为我知道我可以先查询数据库(检查列是否为空),例如:

$q = $conn->dbh->prepare('SELECT columnname FROM tablename WHERE username=:user');
$q->bindParam(':user', $username);
$q->execute();
$check = $q->fetchColumn();

and then leave PHP decide which operation perform:

然后让PHP决定执行哪个操作:

if ($check != '') { // PERFORM A CONCAT }
else { // PERFORM AN UPDATE }

but this would mean a waste of time/resources due to 2x database calls and more PHP code.

但这意味着由于2x数据库调用和更多PHP代码而浪费时间/资源。

thanks.

2 个解决方案

#1

https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

That means in your case:

这意味着在你的情况下:

INSERT INTO tablename (id,columnname) VALUES (1,'//string')
  ON DUPLICATE KEY UPDATE columnname=CONCAT(columnname,'//string');

http://sqlfiddle.com/#!9/bd0f4/1

UPDATE Just to show you your options:

更新只是为了向您展示您的选择:

http://sqlfiddle.com/#!9/8e61c/1

INSERT INTO tablename (id, columnname) VALUES (1, '//string')
  ON DUPLICATE KEY UPDATE columnname=CONCAT(columnname,'//string');

INSERT INTO tablename (id, columnname) VALUES (1, '//string')
  ON DUPLICATE KEY UPDATE columnname=CONCAT(columnname,'//string');

INSERT INTO tablename (id, columnname) VALUES ((SELECT id FROM tablename t WHERE columnname='blahblah'), '//string')
  ON DUPLICATE KEY UPDATE columnname=CONCAT(columnname,'//string');

INSERT INTO tablename (id, columnname) VALUES ((SELECT id FROM tablename t WHERE id=2), '//string')
  ON DUPLICATE KEY UPDATE columnname=CONCAT(columnname,'//string');

INSERT INTO tablename (id, columnname) VALUES ((SELECT id FROM tablename t WHERE columnname='newone'), '//newone')
  ON DUPLICATE KEY UPDATE columnname=CONCAT(columnname,'//newone');

#2

If what you want is this:

如果你想要的是这个:

first string: column will contain 'firststring'

第一个字符串:列将包含'firststring'

second string: column will contain 'firststring//secondstring'

第二个字符串:列将包含'firststring // secondstring'

then do the update like this:

然后像这样做更新:

UPDATE tablename SET columnname = CONCAT( IF(IFNULL(columnname,'')='','',CONCAT(columnname,'//')), :string) WHERE username=:user

#1