MySQL 5.7中带LIMIT 1的子查询报返回多行错误的排查

报错原因 #

MySQL 5.7在处理存储过程循环中带变量偏移量的LIMIT子查询赋值时，存在优化器解析异常：虽然单独用常量测试limit X,1能正常返回1行，但在循环上下文里，变量i的引用可能被优化器误判，导致LIMIT限制未正确生效，触发“子查询返回多行”的错误。另外，如果serialitems表的SerialItemId存在重复值，ORDER BY后的排序不稳定，也可能在循环迭代中触发这个异常（理论上LIMIT 1只取一行，但MySQL 5.7的某些场景下会错误判定结果集行数）。

修复方案 #

推荐改用游标遍历（存储过程遍历记录的标准方式），或者通过临时表预存数据再循环读取，彻底规避变量LIMIT的问题：

方案1：使用游标遍历

create procedure MyMultiRecordProc(in fooIn varchar(12))
begin
    declare i int default 0;
    declare bar int default 0;
    declare cur_serial cursor for 
        select s.SerialNumber from serialitems s order by s.SerialItemId asc;
    declare continue handler for not found set i = 10; -- 触发退出循环的条件

    if (fooIn is null or trim(fooIn) = '') then 
        signal sqlstate '45005' set message_text = 'errors!';
    end if;

    open cur_serial;
    read_loop: loop
        fetch cur_serial into bar;
        if i >= 10 then leave read_loop; end if;
        
        call MySingleRecordProc(bar, fooIn);
        set i = i + 1;
    end loop;
    close cur_serial;
end;

方案2：临时表预存数据后循环读取

create procedure MyMultiRecordProc(in fooIn varchar(12))
begin
    declare n int default 0;
    declare i int default 0;
    declare bar int default 0;

    if (fooIn is null or trim(fooIn) = '') then 
        signal sqlstate '45005' set message_text = 'errors!';
    end if;

    -- 创建内存临时表存储前10条目标记录
    create temporary table if not exists temp_serial (
        row_idx int auto_increment primary key,
        serial_num int
    ) engine=memory;

    truncate table temp_serial;
    insert into temp_serial (serial_num)
    select s.SerialNumber from serialitems s order by s.SerialItemId asc limit 10;

    select count(*) into n from temp_serial;
    set i = 0;
    
    while i < n do
        set bar = (select serial_num from temp_serial where row_idx = i + 1);
        call MySingleRecordProc(bar, fooIn);
        set i = i + 1;
    end while;

    drop temporary table if exists temp_serial;
end;

补充说明 #

原代码的逻辑本身没问题，但MySQL 5.7对存储过程中变量LIMIT的处理存在兼容性缺陷，改用游标或临时表能彻底避免这类问题，同时代码的可读性和稳定性也会更高。

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