MySQL审核存储过程脚本时SQL片段切分不正确

[sjjian]

版本信息（Version）

main

问题描述（Describe）

MySQL审核存储过程脚本时SQL片段切分不正确

截图或日志（Log）

输入的存储过程脚本如下：

DELIMITER $$

USE `fwdb`$$

DROP PROCEDURE IF EXISTS `_GS_GM_Check`$$

CREATE DEFINER=`root`@`%` PROCEDURE `_GS_GM_Check`(vi_uid INT,vi_pwd VARCHAR(32),vi_ip VARCHAR(100),OUT vo_level INT,OUT vo_code INT)
BEGIN
SET vo_level = 0;
	-- JinSQ 2016-04-08 CHECK IP ADD START
	IF IFNULL(vi_ip,' ')=' ' THEN
		BEGIN
	-- JinSQ 2016-04-08 CHECK IP ADD END
			-- if exists(select 1 from T_GM_Info where f_userid = vi_uid and f_pwd = vi_pwd and F_ip = vi_ip and F_Active = 1) 
			IF EXISTS(SELECT 1 FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active = 1) 
			THEN
				-- select f_level into vo_level from T_GM_Info where f_userid = vi_uid AND f_pwd = vi_pwd AND F_ip = vi_ip AND F_Active = 1;
				SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active = 1;
				SET vo_code = 1000;  -- success
			ELSE
				SET vo_code = 1001;  --  fail
			END IF;	
	-- JinSQ 2016-04-08 CHECK IP ADD START
		END;
	ELSE 
		BEGIN
			IF EXISTS(SELECT 1 FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active = 1) 
			THEN
				SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active = 1;
				SET vo_code = 1000;  -- success
			ELSE
				SET vo_code = 1001;  --  fail
			END IF;	
		END;
	END IF;
	-- JinSQ 2016-04-08 CHECK IP ADD END
    END$$

DELIMITER ;

审核后切分的片段如下：

DELIMITER $$

USE `fwdb`$$

DROP PROCEDURE IF EXISTS `_GS_GM_Check`$$

CREATE DEFINER=`root`@`%` PROCEDURE `_GS_GM_Check`(vi_uid INT,vi_pwd VARCHAR(32),vi_ip VARCHAR(100),OUT vo_level INT,OUT vo_code INT)
BEGIN
SET vo_level = 0;
	-- JinSQ 2016-04-08 CHECK IP ADD START
	IF IFNULL(vi_ip,' ')=' ' THEN
		BEGIN
	-- JinSQ 2016-04-08 CHECK IP ADD END
			-- if exists(select 1 from T_GM_Info where f_userid = vi_uid and f_pwd = vi_pwd and F_ip = vi_ip and F_Active = 1) 
			IF EXISTS(SELECT 1 FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active = 1) 
			THEN
				-- select f_level into vo_level from T_GM_Info where f_userid = vi_uid AND f_pwd = vi_pwd AND F_ip = vi_ip AND F_Active = 1;
				SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active = 1;
				SET vo_code = 1000;  -- success
			ELSE
				SET vo_code = 1001;  --  fail
			END IF;	
	-- JinSQ 2016-04-08 CHECK IP ADD START
		END;

	ELSE 
		BEGIN
			IF EXISTS(SELECT 1 FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active = 1) 
			THEN
				SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active = 1;
				SET vo_code = 1000;  -- success
			ELSE
				SET vo_code = 1001;  --  fail
			END IF;	
		END;

	END IF;

	-- JinSQ 2016-04-08 CHECK IP ADD END
    END$$

DELIMITER ;

如何复现（To Reproduce）

通过快捷审核随意创建一个MySQL的静态审核，输入上面的SQL脚步即可复现

实现方案

变更影响面

受影响的模块或功能

外部引用的潜在问题或风险

版本兼容性

测试建议