MySQL 5.7多边形匹配存储过程返回NULL值问题求助

你的存储过程出现textcheck_polygon和container字段为NULL的核心原因是变量名与表字段名冲突，同时游标循环逻辑也存在小问题，导致重复插入数据。下面详细拆解并给出修复方案：

1. 变量名冲突导致空间数据无法正确赋值 #

你在存储过程中声明了局部变量poly polygon;，而csatest1表的空间字段名恰好也是poly。在MySQL中，存储过程的局部变量优先级高于表字段，所以当你执行：

set poly = (select poly from csatest1 where area_id=csaele_no order by area_id asc limit 1);

这里的select poly会被MySQL解析为读取你声明的局部变量poly（此时它的值是NULL），而不是表中的poly字段，最终导致poly变量始终为NULL。后续的ST_astext(poly)和MBRContains(poly, gsa_pointxy)自然也返回NULL。

2. 游标循环逻辑导致重复插入最后一行 #

你的repeat...until循环会先执行一次循环体，再判断done状态。当游标读取到最后一行后，下一次FETCH会触发SQLSTATE '02000'（无数据），将done设为1，但此时循环体仍然会执行一次，导致最后一条数据被重复插入（你返回结果里的两个area_id=3就是这个原因）。

修复后的存储过程代码 #

针对上述问题，我修改了变量名并调整了游标循环逻辑，同时修复了xval查询中未过滤element_id的隐患：

CREATE DEFINER=`root`@`localhost` PROCEDURE `polygon_matcher`(IN gsa_ele_id integer, IN panel_no integer)
BEGIN
    Declare done boolean default 0;
    Declare xval double;
    Declare yval double;
    declare i integer default 1;
    declare text1 varchar(300);
    declare gsa_pointxy point;
    Declare textgsa_pointxy varchar(300);
    declare csaele_no integer;
    -- 修改变量名，避免与表字段冲突
    declare csa_poly polygon;
    declare textcsa_polygon varchar(500);
    declare ysno integer default 0;

    -- 声明游标，遍历多边形表
    Declare rows cursor for Select area_id from csatest1;
    -- 声明继续处理程序
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- 先检查结果表是否存在，避免重复创建报错
    DROP TABLE IF EXISTS datadump;
    CREATE table datadump (CSA_element_ID integer, textcheck_point varchar (300) , textcheck_polygon varchar (300) , container double);

    -- 根据gsa元素ID获取y值（保留order和limit确保唯一）
    Set yval = (select y from gsatest1 where element_id=gsa_ele_id order by element_id limit 1);
    -- 根据用户指定的面板号提取x值，增加element_id过滤，确保取对应元素的x值
    set xval=( SELECT CASE panel_no
        WHEN 1 THEN xpanel1
        WHEN 2 THEN xpanel2
        WHEN 3 THEN xpanel3
        WHEN 4 THEN xpanel4
        ELSE NULL END 
    FROM gsatest1 
    where element_id=gsa_ele_id -- 新增过滤条件
    order by element_id asc limit 1);

    -- 将gsa数据转换为点
    set text1 = concat('POINT (',xval,' ',yval,')');
    set gsa_pointxy = ST_geomfromtext(text1);
    -- 转换为文本用于测试
    set textgsa_pointxy = ST_astext(gsa_pointxy);

    -- 打开游标
    OPEN rows;
    -- 先执行一次FETCH，再进入循环
    FETCH rows into csaele_no;
    -- 使用loop循环替代repeat，避免重复执行最后一次
    read_loop: LOOP
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 使用修改后的变量名，读取表中的poly字段
        set csa_poly = (select poly from csatest1 where area_id=csaele_no order by area_id asc limit 1);
        set textcsa_polygon = ST_astext(csa_poly);
        -- 根据判断结果返回1或0
        set ysno = (Select MBRContains(csa_poly , gsa_pointxy));
        -- 将数据插入结果表
        Insert into datadump (CSA_element_ID, textcheck_point, textcheck_polygon, container) 
        Values (csaele_no, textgsa_pointxy, textcsa_polygon, ysno);

        -- 读取下一行
        FETCH rows into csaele_no;
    END LOOP read_loop;
    -- 关闭游标
    CLOSE rows;
END

额外注意事项 #

• 我新增了DROP TABLE IF EXISTS datadump;，避免每次调用存储过程时因表已存在而报错。
• 修复了xval查询中的逻辑：原代码没有过滤element_id=gsa_ele_id，如果gsatest1表中有多条数据，可能会取到错误的x值。
• 如果你需要找到第一个包含该点的多边形后立即停止循环（符合你的需求），可以在插入数据后判断ysno=1，然后直接关闭游标并退出循环，优化性能。比如在Insert语句后添加：

  IF ysno = 1 THEN
      CLOSE rows;
      LEAVE read_loop;
  END IF;
  

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