mysql - 使用动态创建的 JSON MySql 更新列值

Question

我有一张Relationships看起来像这样的桌子

ID | FromID | ToID | JsonPair
1      10      8       null
2      11      13      null
3      15      21      null
4      26      22      null

还有2张桌子From和To

FromID | FromName                   ToID | ToName
  1        'A'                       1      'Z'                 
  2        'B'                       2      'Y' 
 ...                                ...
  10       'E'                       8      'M'                        
  11       'I'                      ...
 ...                                 13     'N'                        
  15       'O'                      ...
 ...                                 21     'F'                        
  26       'U'                       22     'H' 
                                     

我正在尝试使用表单中的 Json 对象更新 JsonPair 列{FromName: ToName}。所以结果表看起来像

ID | FromID | ToID | JsonPair
1      10      8     {'E':'M'}
2      11      13    {'I':'N'}
3      15      21    {'O':'F'}
4      26      22    {'U':'H'}

我是 SQL 的新手。我在想我应该SELECT先命名，然后使用结果放入UPDATE语句中。

到目前为止，我得到了这个返回 FromName 和 ToName

SELECT F.FromName FROM Relationships AS R
JOIN From as F
ON R.FromID = F.FromID

和

SELECT T.ToName FROM Relationships AS R
JOIN To as T
ON R.FromID = T.FromID;

然后我想我应该用这个的结果来做

UPDATE Relationships
SET JsonPair = (combine result above and format to json)
WHERE JsonPair IS NULL;

我被困在组合和格式化步骤。请问我可以得到帮助吗？

我在用着MySql

标签： mysqlsqljsonsql-updateinner-join

Answer 1

您可以使用 update/join 语法以及json_object().

考虑：

update relationships r
inner join t_from f on f.fromid = r.fromid
inner join t_to   t on t.to_id  = r.to_id
set r.jsonpair = json_object(f.fromname, t.toname)

注意：fromandto是MySQL 中的保留字，因此表名的选择不好。我将它们重命名为t_from 和t_to在查询中。