저장 프로시저 두 번 이상 MySQL 업데이트

저장 프로시저 두 번 이상 MySQL 업데이트

MySQL의 일부 행을 업데이트하기 위한 저장 프로시저가 있습니다.하지만 저장 프로시저를 호출하면 실제 행보다 더 많은 영향을 미칩니다.

저장 프로시저는 다음과 같습니다.

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_INSERT_DATA`(
    IN `incoming_data` TEXT,
    IN `value_array` TEXT,
    IN `serial_number` VARCHAR(50),
    IN `data_timestamp` VARCHAR(50),
    OUT `result_id` INT,
    OUT `result_message` VARCHAR(500)
)
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;
DECLARE arr_data_type_name VARCHAR(50);
DECLARE affected_data_row_count INT;
DECLARE cstmr_id INT;


DECLARE exit handler FOR 40001
BEGIN
    GET DIAGNOSTICS CONDITION 1
    @p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
    SET result_id = -2;
    SET result_message = CONCAT('RETURNED ERROR NO : ', @p1 , '\r\nMESSAGE TEXT : ',@p2);
ROLLBACK;
END;

DECLARE exit handler for sqlexception
BEGIN
    GET DIAGNOSTICS CONDITION 1
    @p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
    SET result_id = -999;
    SET result_message = CONCAT('RETURNED ERROR NO : ', @p1 , '\r\nMESSAGE TEXT : ',@p2);
ROLLBACK;
END;

    START TRANSACTION;
    SET i = 1;
    SET affected_data_row_count = 0;
    SET cstmr_id = 0;
    SET result_id = 0;
    SET result_message = 'Success';
    
    SELECT id INTO cstmr_id FROM templaricustomers WHERE templaricustomers.customer_serial_number = serial_number LIMIT 1;
    IF(cstmr_id <> 0) THEN
        
        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);
            
                IF (SELECT COUNT(id) FROM datatypes WHERE datatypes.data_name = arr_data_type_name) > 0 THEN 
                    SELECT id INTO lcl_data_type_id FROM datatypes WHERE datatypes.data_name = arr_data_type_name LIMIT 1;
                ELSE
                    INSERT INTO datatypes (datatypes.data_name,datatypes.description ,datatypes.inserted_date) VALUES(arr_data_type_name,arr_data_type_name,NOW());
                    SELECT id INTO lcl_data_type_id FROM datatypes WHERE datatypes.data_name = arr_data_type_name LIMIT 1;
                END IF;
                
                
                IF (SELECT COUNT(id) FROM mqttpacket WHERE mqttpacket.data_type_id = lcl_data_type_id AND mqttpacket.customer_id = cstmr_id) > 0 THEN
                    UPDATE mqttpacket SET mqttpacket.data_value = value_iteration , mqttpacket.inserted_time = data_timestamp WHERE mqttpacket.data_type_id = lcl_data_type_id AND mqttpacket.customer_id = cstmr_id;
                ELSE
                INSERT INTO mqttpacket (mqttpacket.data_type_id,mqttpacket.customer_id,mqttpacket.data_value,mqttpacket.inserted_time) VALUES(lcl_data_type_id,cstmr_id,value_iteration,data_timestamp);
                END IF;
                
                SET affected_data_row_count = affected_data_row_count +1;
                
        END WHILE;
        SET result_id = 0;
        SET result_message = CONCAT('Query performed successfully. Effected rows :  ',CAST(affected_data_row_count AS CHAR));

    ELSE
        SET result_id = -1;
        SET result_message = 'Customer Serial Number not found.';
    END IF;
        

COMMIT;
END

제가 부르는 이름은 이렇습니다.

CALL `SP_INSERT_DATA`('{"subcooling":-40,"B1":113,"B2":206,"B3":471,"B4":226,"B5":8,"B6":380,"B7":210,"B8":187,"Discharge":135,"Suction":120,"High_Pressure":90,"Low_Pressure":90,"Evaporation":73,"Condensation":73,"MAX_CMP_SPEED":1100,"Thermal_Limit":748,"SH":46,"EEV_pct":0,"COP":0,"DSH":60,"WaterFlux":8,"FanPower":1,"DeltaTtoStart":0,"DeltaPtoStart":60,"CMP_ROTOR_RPS":0,"SET_CH_FLASH":120,"SET_HP_FLASH":350,"SET_DHW_FLASH":470,"Defrosting":0,"B8_AVERAGE":63,"SET_PLANT":0,"SET_CH_BMS":160,"SET_HP_BMS":200,"SET_DHW_BMS":480,"SET_ACTIVE":200,"SET_DSH":250,"EEV_INJ_pct":0,"LPT":0,"HPT":0,"PLANT_MODE_MANUAL":0,"DHW_MODE_MANUAL":0,"WATER_FLOW":8,"DISCHARGE_TMP":135,"INVERTER_TMP":8,"ENVELOP_ZONE":1,"EEV_A_STEPS":0,"EBM_POWER":1,"EBM_MAX_POWER":106,"COMP_pct_FINAL":0,"TOTAL_POWER_ABSORBED":10,"POWER_OUT_KW":0,"COOLINGCAPACITY":0}' , '$.subcooling,$.B1,$.B2,$.B3,$.B4,$.B5,$.B6,$.B7,$.B8,$.Discharge,$.Suction,$.High_Pressure,$.Low_Pressure,$.Evaporation,$.Condensation,$.MAX_CMP_SPEED,$.Thermal_Limit,$.SH,$.EEV_pct,$.COP,$.DSH,$.WaterFlux,$.FanPower,$.DeltaTtoStart,$.DeltaPtoStart,$.CMP_ROTOR_RPS,$.SET_CH_FLASH,$.SET_HP_FLASH,$.SET_DHW_FLASH,$.Defrosting,$.B8_AVERAGE,$.SET_PLANT,$.SET_CH_BMS,$.SET_HP_BMS,$.SET_DHW_BMS,$.SET_ACTIVE,$.SET_DSH,$.EEV_INJ_pct,$.LPT,$.HPT,$.PLANT_MODE_MANUAL,$.DHW_MODE_MANUAL,$.WATER_FLOW,$.DISCHARGE_TMP,$.INVERTER_TMP,$.ENVELOP_ZONE,$.EEV_A_STEPS,$.EBM_POWER,$.EBM_MAX_POWER,$.COMP_pct_FINAL,$.TOTAL_POWER_ABSORBED,$.POWER_OUT_KW,$.COOLINGCAPACITY,', '123456', '2021-02-24 10:43:00.00' ,@result_id, @result_message);

테이블 mqtt 패킷은 시스템 버전의 테이블입니다.테이블 생성 쿼리는 다음과 같습니다.

CREATE TABLE test_database.MQTTPacket(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    data_type_id INT NOT NULL,
    customer_id INT NOT NULL,
    data_value INT NULL,
    row_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START INVISIBLE,
    row_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END INVISIBLE,
    inserted_time TIMESTAMP(6) NULL DEFAULT NULL,
    PERIOD FOR SYSTEM_TIME(row_start,row_end),
    FOREIGN KEY (data_type_id) REFERENCES test_database.DataTypes(id),
    FOREIGN KEY (customer_id) REFERENCES test_database.templaricustomers(id)
    )WITH SYSTEM VERSIONING;

CREATE TABLE `datatypes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `data_name` VARCHAR(50) NOT NULL COLLATE 'latin1_general_ci',
    `inserted_date` DATE NULL DEFAULT NULL,
    `description` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
    `device_id` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `device_id` (`device_id`) USING BTREE,
    CONSTRAINT `datatypes_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `test_database`.`devices` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)

CREATE TABLE `templaricustomers` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `customer_serial_number` VARCHAR(50) NOT NULL COLLATE 'latin1_general_ci',
    `customer_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
    `inserted_date` TIMESTAMP(6) NOT NULL DEFAULT current_timestamp(6),
    PRIMARY KEY (`id`) USING BTREE
)

따라서 저장 프로시저를 처음 호출하면 result_message 변수가 "Query performed successful"을 반환합니다.영향을 받는 행: 53" 그러나 MySql은 말합니다.

영향을 받는 행: 107 발견된 행: 0 경고: 0 쿼리 지속 시간: 0.015초입니다.

SP의 두 번째 통화에서는

영향을 받는 행: 160개 발견된 행: 0개 경고: 1개 쿼리에 대한 지속 시간: 0.031초입니다.

미리 도와주셔서 감사합니다.

언급URL : https://stackoverflow.com/questions/66349530/stored-procedure-updating-more-than-one-time-mysql