将多个MySQL列转换为JSON

Question

我想要将mysql数据库中一个表中的多列数据转换为另一个表中的单个JSON格式的数据，使用SQL查询。数据库版本是5.7.16。

第一个表格如下：

   CREATE TABLE `log_old` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `temperature` DECIMAL(5,2) NULL DEFAULT NULL,
    `heating_requested` BIT(1) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
   )COLLATE='utf8_general_ci'
   ENGINE=InnoDB;

第二个表格长这样

    CREATE TABLE `log_new` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    'data' JSON,
    PRIMARY KEY (`id`),
    )COLLATE='utf8_general_ci'
    ENGINE=InnoDB;

数据JSON在log_new表的所有行中具有相同的格式，应该如下所示：

{
    temperature: value,
    heatingRequested: false
}

例如，log_old看起来像这样。

 +--+-----------+-----------------+
 |id|temperature|heating_requested|
 +--+-----------+-----------------+
 |1 |    12     |        true     |
 +--+-----------+-----------------+
 |2 |    14     |        true     |
 +--+-----------+-----------------+
 |3 |    20     |        false    |
 +--+-----------+-----------------+

我希望log_new的显示效果如下：

 +--+-----------------------------------------+
 |id|              data                       |
 +--+-----------------------------------------+
 |1 |{temperature:12, heatingRequested: true} | 
 +--+-----------------------------------------+
 |2 |{temperature:14, heatingRequested: true} | 
 +--+-----------------------------------------+
 |3 |{temperature:20, heatingRequested: false}| 
 +--+-----------------------------------------+

我尝试使用JSON_INSERT()

    SELECT JSON_INSERT((SELECT data  FROM log_new  ), '$.temperature',
   (SELECT temperature FROM log_old));

但是这会抛出错误"子查询返回多行"。我只有使用while来逐行执行它的解决方案，但这可能需要很长时间。

   DELIMITER //  
   CREATE PROCEDURE doLog()
   BEGIN
    SELECT COUNT(*) into @length from log_zone;  
    SET @selectedid = 1;
    WHILE @selectedid < @length DO
        SELECT temperature,heating_requested INTO @temperature,@heating_requested FROM log_old where id=@selectedid;    
        SELECT   JSON_OBJECT('temperature',@temperature,'heatingRequested',@heating_requested) into @data_json;
        SET @selectedid = @selectedid + 1;
        INSERT INTO log_new (data) VALUES (@data_json);
     END WHILE;
    END;
    //
    CALL doLog()

- Morme

---

你在 SELECT JSON_INSERT((SELECT data FROM log_new ), '$.temperature', (SELECT temperature FROM log_old)); 中的两个子查询返回了多行，所以出现了错误。请检查插入-选择语法 - http://dev.mysql.com/doc/refman/5.7/en/insert-select.html - 并将您的函数重写为一个语句，该语句将从整个表中使用 select json_object(... 进行批量插入。 - JosMac

INSERT INTO log_new (data) SELECT JSON_OBJECT('temperature',(SELECT temperature FROM log_old)); 你这么想吗？我不确定是否理解正确。 - Morme

嵌套查询仍会为每个外部查询的行产生多行结果。您需要至少在嵌套查询中添加一个条件。但最好的方法是在一个查询中连接两个表。 - JosMac

Answer 1

作为您所有数据均可在单行中获取，因此您无需使用子查询或循环来构建JSON对象。

您可以尝试类似以下的内容：

INSERT INTO log_new (data)
   SELECT json_object('temperature',log_old.temperature,'heatingRequested',log_old.heating_requested) 
   FROM   log_old

- vince