时间戳

首页 > 解决方案 > 解析 JSON 并插入 MySQL

mysql - 解析 JSON 并插入 MySQL

问题描述

我正在从每个客户端向我的服务器接收 JSON 数据。我有三个主表；数据类型、templaricustomers 和 mqttpacket。

这里的数据类型来自 JSON 变量名，我将它们保存在数据库中。

由于我是 MySQL 的初学者，我正在尝试创建一个循环并将解析的 JSON 插入到相关表中。

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_INSERT_DATA`(
    IN `incoming_data` TEXT,
    IN `value_array` TEXT,
    IN `customer_id` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE i INT;
DECLARE value_iteration VARCHAR(50);
DECLARE lcl_data_type_id INT;

    SET i = 1;

    WHILE (LOCATE(',', value_array) > 0)
    DO
        SET @arr_data_type_name = SUBSTRING_INDEX(value_array,',',i);
        SET value_array = SUBSTRING(value_array, LOCATE(',',value_array) + 1);

        SELECT JSON_EXTRACT(@incoming_data, @arr_data_type_name) INTO value_iteration;
        
        SET @arr_data_type_name := SUBSTRING_INDEX(@arr_data_type_name, ".", -1);
        
        SELECT id INTO lcl_data_type_id FROM test_database.datatypes WHERE datatypes.data_name = @arr_data_type_name LIMIT 1;
        
        INSERT INTO test_database.mqttpacket (data_type_id,inserted_time,customer_id,data_value) VALUES(lcl_data_type_id,NOW(),customer_id,value_iteration);
        SET i = i+1;
    END WHILE;

END

JSON中的示例incoming_data就像；

{"d": {"subcooling": 6,"B1": 382,"B2": 386,"B3": 526,"B4": 361,"B5": 713,"B6": 689,"B7": 386,"B8": 99,"Discharge": 663,"Suction": 111,"High_Pressure": 225,"Low_Pressure": 78,"Evaporation": 31,"Condensation": 388,"MAX_CMP_SPEED": 950,"Thermal_Limit": 950,"SH": 78,"EEV_pct": 571,"COP": 52,"DSH": 272,"Water Flux": 713,"Fan Power": 239,"Delta T to Start": 0,"Delta P to Start": 60,"CMP_ROTOR_RPS": 430,"SET_CH_FLASH": 120,"SET_HP_FLASH": 500,"SET_DHW_FLASH": 500,"Defrosting": 0,"B8_AVERAGE": 42,"SET_PLANT": 0,"SET_CH_BMS": 430,"SET_HP_BMS": 382,"SET_DHW_BMS": 510,"SET_ACTIVE": 402,"SET_DSH": 323,"EEV_INJ_pct": 0,"LPT": 0,"HPT": 0,"PLANT_MODE_MANUAL": 0,"DHW_MODE_MANUAL": 0,"WATER_FLOW": 713,"DISCHARGE_TMP": 663,"INVERTER_TMP": 25,"ENVELOP_ZONE": 1,"EEV_A_STEPS": 274,"EBM_POWER": 239,"EBM_MAX_POWER": 322,"COMP_pct_FINAL": 359,"TOTAL_POWER_ABSORBED": 2599,"NAME": [17236,11585,13388,50,0,0,0,0,0,0,0,0,0,0,0,0],"POWER_OUT_KW": 134,"COOLING CAPACITY": [35],"EBM1_PCT": [861],"EBM2_PCT": [767]},"ts": "2021-02-02T14:42:02.479731" }

value_array 的一个例子是这样的；

$.d.subcooling,$.d.B1,$.d.B2

这是我的存储过程。我只需要逐个节点提取JSON节点并从“incoming_data”中找到“datatypename”，即“节点名”，并按其值插入mqtt_packet表。

它无法获取“value_iteration”的数据并插入不相关的数据类型 ID。请告诉我我的查询有什么问题。我希望我很清楚......干杯！

标签： mysqljsonstored-proceduresmysql-error-1064mysql-json

解决方案

推荐阅读