mysql - 通过 MySQL 替换字段上的字符串

Question

我的一个表有以下字段(它是一个字符串):

'[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
  {"id":"7","value":"Reuters"},
  {"id":"20","value":"2017/03/17"}]'`

如您所见，最后一部分，即带有 id=20 的部分，上面有一个日期，2017/03/17。

有人可以告诉我如何更新该字段以便获得输出2017-03-17吗？

更新后我想要的输出是:

'[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
 {"id":"7","value":"Reuters"},
 {"id":"20","value":"2017-03-17"}]'`

如果我使用:

UPDATE mytable
SET myfield = replace(myfield , '/', '-');

它删除了链接的 /，我想保留它们。

PS:字段的 ID 始终相同(例如，日期的 ID 始终为 20)，唯一改变的是值。

Answer 1

您可以使用如下查询来完成此操作:

UPDATE  mytable
    SET myfield = CONCAT (
        SUBSTRING_INDEX(myfield, '"id":"20","value":', 1)
        , '"id":"20","value":',
        , REPLACE (SUBSTRING_INDEX(myfield, '"id":"20","value":', -1),'/','-')
    )
WHERE ....:

示例

mysql> SELECT
    ->     CONCAT (
    ->     SUBSTRING_INDEX('[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
    '>  {"id":"7","value":"Reuters"},
    '>  {"id":"20","value":"2017/03/17"}]', '"id":"20","value":', 1),
    ->  '"id":"20","value":',
    ->  REPLACE (SUBSTRING_INDEX('[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
    '>  {"id":"7","value":"Reuters"},
    '>  {"id":"20","value":"2017/03/17"}]', '"id":"20","value":', -1),'/','-')
    ->  ) AS result;
+------------------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
 {"id":"7","value":"Reuters"},
 {"id":"20","value":"2017-03-17"}] |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>