我有一个这样的表:

// mytable
+----+-------------------------------------------+
| id |                 col                       |
+----+-------------------------------------------+
| 1  | Peter|423421 , Alex Jon|61333             |
| 2  | Barmar|624321                             |
| 3  | Jack|624321 , Ali|312331 , Leonard|624321 |
+----+-------------------------------------------+


我需要检查第二行是否不包含824326,然后在该行末添加此值, Sara|824326。像这样:

| 2  | Barmar|624321 , Sara|824326               |




现在我想知道,如何检查字段不包含薄膜号824326

这是我的尝试,我只需要条件(检查是否存在)

UPDATE mytable
SET    col = CASE WHEN col = {:val doesn't exist} // <= How to write this condition?
                   THEN CONCAT(col,':newval')
                   ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "It is duplicate";
             END,
WHERE id = 2;


注意1::newval是上面查询中的, Sara|824326

注意2::val是上面查询中的824326

最佳答案

您必须使用INSTR。尝试这个:

UPDATE mytable
SET    col = CASE WHEN col INSTR(col, ':val') = 0
                   THEN CONCAT(col,':newval')
                   ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "It is duplicate";
             END,
WHERE id = 2;

10-08 01:34