我有两个表，即 ds_message 和 ds_params，第一个表包含模板，第二个表包含键值对

表结构: ds_message

_____________________________________________
id  template                                  
_____________________________________________
1  'Dear {a}, the price of {b} is {c}'       
2  'Dear {i}, you selected the product {j}'  

架构:

CREATE TABLE `ds_message` (
  `id` int NOT NULL,
  `template` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

ALTER TABLE `ds_message`
  ADD PRIMARY KEY (`id`);

INSERT INTO `ds_message` (`id`, `template`) VALUES
(1, 'Dear {a}, the price of {b} is {c}');

INSERT INTO `ds_message` (`id`, `template`) VALUES
(2, 'Dear {i}, you selected the product {j}');

表结构: ds_params

_________________________________________________
id   message_id    json_key    json_value
_________________________________________________
1    1             a         John
2    1             b         bat
3    1             c         $10
4    2             i         Emma
5    2             j         Jam

架构:

CREATE TABLE `ds_params` (
  `id` int NOT NULL,
  `message_id` int NOT NULL,
  `json_key` varchar(500) NOT NULL,
  `json_value` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

ALTER TABLE `ds_params`
  ADD PRIMARY KEY (`id`);

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(1, 1, 'a', 'John');

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(2, 1, 'b', 'bat');

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(3, 1, 'c', '$10');

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(4, 2, 'i', 'Emma');

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(5, 2, 'j', 'Jam');

我需要替换 ds_message 表中的键(例如 {a} => John)。

我试过下面的代码，

UPDATE ds_message AS t
INNER JOIN ds_params m ON m.message_id = t.id
    SET t.template = REPLACE(t.template, CONCAT('{', m.json_key , '}'), m.json_value);

一旦我执行了代码，我得到了这样的输出，只有第一个键被替换，其余的键没有得到更新。

_____________________________________________
id  template                                  
_____________________________________________
1   Dear John, the price of {b} is {c}
2   Dear Emma, you selected the product {j}

请帮助我如何做到这一点。

最佳答案

我们有 2 个选择。

首先是使用 while 循环。

SET @n = 0;
SELECT COUNT(*) FROM ds_params INTO @n;
SET @i=0;
WHILE @i<@n DO 
    UPDATE ds_message AS t
    INNER JOIN ds_params m ON m.message_id = t.id AND m.id = @i 
    SET t.template = REPLACE(t.template, CONCAT('{', m.json_key , '}'), m.json_value)
    SET @i = @i + 1;
END WHILE;

第二个选项是这个。如果你有修复 json_key

UPDATE ds_message AS t
LEFT JOIN ds_params a ON a.message_id = t.id  and a.json_key='a'
LEFT JOIN ds_params b ON b.message_id = t.id  and b.json_key='b'
LEFT JOIN ds_params c ON c.message_id = t.id  and c.json_key='c'
LEFT JOIN ds_params i ON i.message_id = t.id  and i.json_key='i'
LEFT JOIN ds_params j ON j.message_id = t.id  and j.json_key='j'
SET t.template = REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(t.template, CONCAT('{', IFNULL(a.json_key, '') , '}')
                         , IFNULL(a.json_value, '')),
              CONCAT('{', IFNULL(b.json_key, '') , '}'), IFNULL(b.json_value, '')),
              CONCAT('{', IFNULL(c.json_key, '') , '}'), IFNULL(c.json_value, '')),
              CONCAT('{', IFNULL(i.json_key, '') , '}') ,IFNULL(i.json_value, '')),
              CONCAT('{', IFNULL(j.json_key, '') , '}') ,IFNULL(j.json_value, ''));

关于mysql - 在 MySQL 中通过内部连接使用更新语句查找和替换，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/57602507/