SQL存储过程执行报错‘Result consisted of more than one row’求助

问题根源 #

你的存储过程输入参数名domain_level_two_id与表t_auto_generate_dml2_based_code_sequence的列名DOMAIN_LEVEL_TWO_ID完全一致（MySQL不区分大小写），导致WHERE条件DOMAIN_LEVEL_TWO_ID = domain_level_two_id被解析为列与列自身比较，即表中所有行都满足该条件，最终SELECT语句返回多行数据，触发错误。

解决方案 #

修改输入参数名，避免与列名重复，同时更新存储过程中所有引用该参数的位置。以下是修正后的存储过程代码：

create
    definer = dt@`%` procedure create_farmer_unique_code(IN in_domain_level_two_id int, OUT generated_unique_code varchar(50))
BEGIN

    DECLARE int_seq int;
    DECLARE dml2_code char(3);
    DECLARE error_message varchar(255);

    IF (SELECT COUNT(*) FROM t_auto_generate_dml2_based_code_sequence WHERE DOMAIN_LEVEL_TWO_ID = in_domain_level_two_id) =
       0 THEN
        SET error_message = CONCAT('Division ID ', in_domain_level_two_id, ' does not exist.');
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = error_message;
    END IF;

    UPDATE t_auto_generate_dml2_based_code_sequence
    SET CURRENT_VALUE = (CURRENT_VALUE + 1)
    WHERE DOMAIN_LEVEL_TWO_ID = in_domain_level_two_id;

    SELECT CURRENT_VALUE, DOMAIN_LEVEL_TWO_CODE
    INTO int_seq, dml2_code
    FROM t_auto_generate_dml2_based_code_sequence
    WHERE DOMAIN_LEVEL_TWO_ID = in_domain_level_two_id;

    SET generated_unique_code = CONCAT(dml2_code, '-', LPAD(int_seq, 5, '0'));

END;

额外验证点 #

• 确认表t_auto_generate_dml2_based_code_sequence的DOMAIN_LEVEL_TWO_ID已正确设置为主键，且无重复数据。可执行以下语句验证：

  SELECT * FROM t_auto_generate_dml2_based_code_sequence WHERE DOMAIN_LEVEL_TWO_ID = 你的测试ID;
  

内容的提问来源于stack exchange，提问作者Sandy_Blaq