MySQL update为子查询中的每一行运行SET

s3fp2yjn  于 2023-04-05  发布在  Mysql
关注(0)|答案(1)|浏览(108)

我有两张table:FilesFileLogs,我试图摆脱FileLogs表,并将其中的一些数据存储在Files表中。
文件
| 身份证|lastStatusId| meta|
| --------------|--------------|--------------|
| 一百八十七|一百零一|空|
| 一八八|一百零一|{“foo”:“bar”}|
文件日志
| 身份证|文件ID|状态Id|创建时间| meta|
| --------------|--------------|--------------|--------------|--------------|
| 1|一百八十七|一百零一|2019-06-18 19:43:19|空|
| 第二章|一百八十七|一百零二|2019-06-18 19:43:22|空|
| 三|一百八十七|二百零二|2019-06-18 19:45:32|{“foo2”:“bar2”}|
我已经将lastStatusId列添加到Files表中(默认值为101)。
为了从日志中获取每个文件的最新statusIdlatestStatusId,我运行了这个查询,它工作得很好:

UPDATE
  `Files` f1,
  (
    SELECT
      f.id as fileId,
      fl.statusId,
      fl.meta
    FROM
      (
        SELECT
          MAX(id) as id,
          fileId
        FROM
          `FileLogs`
        GROUP BY
          fileId
      ) AS latestIds
      LEFT JOIN `FileLogs` AS fl ON latestIds.id = fl.id
      LEFT JOIN `Files` AS f ON f.id = fl.fileId
    ORDER BY
      fl.id ASC
  ) temp1
SET
  f1.lastStatusId = temp1.statusId
WHERE
  f1.id = temp1.fileId;

我想我可以使用一个类似的查询来检查所有FileLogs并将状态写入Files的meta字段,如下所示:

UPDATE
  `Files` f1,
  (
    SELECT
      f.id as fileId,
      fl.statusId,
      fl.meta,
      fl.createdAt
    FROM
      `FileLogs` AS fl
      LEFT JOIN `Files` AS f ON f.id = fl.fileId
  ) temp1
SET
  f1.meta = JSON_MERGE_PATCH(
    COALESCE(f1.meta, '{}'),
    JSON_OBJECT(
      'statusInfo',
      JSON_OBJECT(
        CONCAT('', temp1.`statusId`),
        JSON_OBJECT(
          'createdAt',
          temp1.createdAt
        )
      )
    )
  )
WHERE
  f1.id = temp1.fileId;

子查询的结果如下所示:

fileId  statusId    meta            createdAt
187     101         NULL            2019-06-18 19:43:19
187     102         NULL            2019-06-18 19:43:22
187     202         {"foo": "bar"}  2019-06-18 19:43:26

但是SET只对第一个f1.id = temp1.fileId运行,而不是第二个或第三个(这是有道理的)-结果看起来像这样:

File id:187

meta

{
    "statusInfo": {
        "101": {
            "createdAt": "2019-06-18 19:43:19.000000"
        }
    }
}

1.我怎样才能实现另外两个状态(102,202)也被添加到 meta列?
1.我如何也能在那里获得FileLogs的 meta数据(如果不是null)?

ig9co6j1

ig9co6j11#

您可以在子查询中预先聚合JSON。下面是一个演示:

select f.id,
  json_objectagg(l.statusId,
    json_object(
      'createdAt', l.createdAt, 
      'meta', l.meta
  )) as statusInfo
from Files as f
join FileLogs as l on f.id = l.fileId
group by f.id;

给出示例数据的结果:

id: 187
statusInfo: {
  "101": {
    "meta": null,
    "createdAt": "2019-06-18 19:43:19.000000"
  },
  "102": {
    "meta": null,
    "createdAt": "2019-06-18 19:43:22.000000"
  },
  "202": {
    "meta": {
      "foo2": "bar2"
    },
    "createdAt": "2019-06-18 19:45:32.000000"
  }
}

现在,这类似于你想要的JSON结构,我们可以将其 Package 到UPDATE中:

update Files
join (
  select fileId,
    json_objectagg(statusId,
      json_object(
        'createdAt', createdAt,
        'meta', meta
      )
    ) as statusInfo
  from FileLogs
  group by fileId
) as f on Files.id = f.fileId
set meta = json_object('statusInfo', f.statusInfo);

但是,您应该考虑这样做的影响。

  • 在JSON中存储数据比在FileLogs的行中存储相同的数据占用更多的空间。
  • File.meta JSON文档添加后续信息可以通过JSON合并来完成,但这肯定比简单地向FileLogs插入一个新行需要更仔细的考虑。
  • 如果你需要搜索具有特定状态的条目,或者哪一个是最近的,或者哪一个在给定日期有状态条目,那么搜索JSON文档可能会很棘手,而如果将状态信息保存在FileLogs的行中,则会简单得多。在使用JSON之前,你应该确保你知道如何做所有你需要做的查询。

相关问题