mysql 存储过程问题:插入首先运行，并且始终运行我放置的任何 If 条件

Question

需要快速帮助，在下面首先触发SQL插入，然后触发其他选择查询。 插入语句执行任何 If 条件我放置。 我正在phpMyAdmin中测试这个。 我也试图将插入语句转换为单独的存储过程，但结果也是一样的。 提前感谢..

DELIMITER $$
DROP PROCEDURE IF EXISTS get_top$$
CREATE PROCEDURE get_top(
IN tolerance INT,
IN cred INT,
IN cgreen INT,
IN cblue INT,
IN userID INT,
IN mapID BIGINT
)
BEGIN
DECLARE topList VARCHAR(255) DEFAULT NULL;
DECLARE top1 VARCHAR(255) DEFAULT NULL;
DECLARE top2 VARCHAR(255) DEFAULT NULL;
DECLARE top3 VARCHAR(255) DEFAULT NULL;
DECLARE get_result VARCHAR(255) DEFAULT NULL;
DECLARE dev_notes VARCHAR(255) DEFAULT NULL;
SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
SELECT CONCAT(top_1, ';', top_2, ';', top_3) INTO topList
FROM Tops
WHERE red = cred AND green = cgreen AND  blue = cblue
ORDER BY top_ID asc
LIMIT 0 , 1 ;
SELECT topList,cred,cgreen,cblue;
IF topList IS NULL THEN BEGIN
SELECT  top_1, top_2, top_3 
INTO top1, top2, top3
FROM Tops 
WHERE top_1>0
LIMIT 0 , 1 ;
SELECT "in IF",top1,CHAR_LENGTH(top1);
IF (top1 IS NULL) THEN 
SET  get_result:= 'None';
ELSE
SET  get_result:= 'Nearest';
END IF;
SET topList=top1+ ','+ top2+ ','+ top3;
SET dev_notes='result:'+get_result+' mapID:'+mapID+' User ID:'+userID+' date/time:'+NOW();
SELECT topList,get_result;
INSERT INTO Tops (red,green,blue,top_1,top_2,top_3,notes) VALUES (cred,cgreen,cblue,top1,top2,top3,dev_notes);
END;
ELSE BEGIN
SET  get_result:= 'Exact';
SELECT "in else",topList,get_result,dev_notes;
END; END IF;
IF CHAR_LENGTH(mapID)>0 THEN
UPDATE Map SET toponym=toponymList, toponym_conf=get_result WHERE map_ID=mapID;
END IF;
END$$
DELIMITER ;

Answer 1

我已经修改了您的代码以将调试消息写入表，以便我可以说明正在发生的事情。

DELIMITER $$
DROP PROCEDURE IF EXISTS get_top$$
CREATE PROCEDURE get_top(
IN tolerance INT,
IN cred INT,
IN cgreen INT,
IN cblue INT,
IN userID INT,
IN mapID BIGINT
)
BEGIN
DECLARE topList VARCHAR(255) DEFAULT NULL;
declare topsid int;
DECLARE top1 VARCHAR(255) DEFAULT null;
DECLARE top2 VARCHAR(255) DEFAULT null;
DECLARE top3 VARCHAR(255) DEFAULT null;
DECLARE get_result VARCHAR(255) DEFAULT NULL;
DECLARE dev_notes VARCHAR(255) DEFAULT NULL;
SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
SELECT top_id,CONCAT(top_1, ';', top_2, ';', top_3) INTO topsid,topList
FROM Tops
WHERE red = cred AND green = cgreen AND  blue = cblue
ORDER BY top_ID asc
LIMIT 0 , 1 ;
insert into debug_table (msg) values (concat('Start topsid:', coalesce(topsid,'not found'),' toplist:',coalesce(topList,'null'),' cred:',cred,' cgreen:',cgreen,' cblue:',cblue));
IF topList IS NULL THEN 
BEGIN
SELECT  top_1, top_2, top_3 
INTO top1, top2, top3
FROM Tops 
WHERE top_1>0
LIMIT 0 , 1 ;
insert into debug_table (msg) values (concat("in IF",' top1:',coalesce(top1,'null'),' len top1:',if(top1 is not null,CHAR_LENGTH(top1),0)));
IF (top1 IS NULL) THEN 
SET  get_result:= 'None';
ELSE
SET  get_result:= 'Nearest';
END IF;
SET topList=top1+ ','+ top2+ ','+ top3;
#SET dev_notes='result:'+get_result+' mapID:'+mapID+' User ID:'+userID+' date/time:'+NOW();
SET dev_notes=CONCAT('result:',get_result,' mapID:',mapID,' User ID:',userID,' date/time:',NOW());
insert into debug_table (msg) values (concat('about to insert toplist:',coalesce(topList,'null'),' get_result:',get_result,' dev_notes:',DEV_NOTES));
INSERT INTO Tops (red,green,blue,top_1,top_2,top_3,notes) VALUES (cred,cgreen,cblue,top1,top2,top3,dev_notes);
END;
ELSE BEGIN
SET  get_result:= 'Exact';
insert into debug_table (msg) values (concat(' in else toplist:',coalesce(topList,'null'),' get_result:',get_result,' dev_notes:',DEV_NOTES));
END; 
END IF;
#SELECT CHAR_LENGTH(MAPID);
#IF CHAR_LENGTH(mapID)>0 THEN
#    UPDATE Map SET toponym=toponymList, toponym_conf=get_result WHERE map_ID=mapID;
#END IF;
END$$
DELIMITER ;

所以如果上衣看起来像这样

DROP TABLE IF EXISTS TOPS;
CREATE TABLE TOPS(TOP_ID INT AUTO_INCREMENT PRIMARY KEY,
red INT,green INT,blue INT,top_1 varchar(4),top_2 varchar(4),top_3 varchar(4),notes VARCHAR(100));

我这样做

ariaDB [sandbox]> TRUNCATE TABLE TOPS;
Query OK, 0 rows affected (0.16 sec)
MariaDB [sandbox]> truncate table debug_table;
Query OK, 0 rows affected (0.24 sec)
MariaDB [sandbox]> CALL GET_TOP(1,1,1,1,'1','1');
Query OK, 1 row affected (0.17 sec)
MariaDB [sandbox]> select * from tops;
+--------+------+-------+------+-------+-------+-------+-------------------------------------------------------------+
| TOP_ID | red  | green | blue | top_1 | top_2 | top_3 | notes                                                       |
+--------+------+-------+------+-------+-------+-------+-------------------------------------------------------------+
|      1 |    1 |     1 |    1 | NULL  | NULL  | NULL  | result:None mapID:1 User ID:1 date/time:2017-07-20 09:45:00 |
+--------+------+-------+------+-------+-------+-------+-------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [sandbox]> CALL GET_TOP(1,1,1,1,'1','1');
Query OK, 1 row affected (0.10 sec)
MariaDB [sandbox]>
MariaDB [sandbox]> SELECT * FROM debug_table;
+----+--------------------------------------------------------------------------------------------------------------------+
| id | msg                                                                                                                |
+----+--------------------------------------------------------------------------------------------------------------------+
|  1 | Start topsid:not found toplist:null cred:1 cgreen:1 cblue:1                                                        |
|  2 | in IF top1:null len top1:0                                                                                         |
|  3 | about to insert toplist:null get_result:None dev_notes:result:None mapID:1 User ID:1 date/time:2017-07-20 09:45:00 |
|  4 | Start topsid:1 toplist:null cred:1 cgreen:1 cblue:1                                                                |
|  5 | in IF top1:null len top1:0                                                                                         |
|  6 | about to insert toplist:null get_result:None dev_notes:result:None mapID:1 User ID:1 date/time:2017-07-20 09:45:00 |
+----+--------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

我希望在第一次调用时不会在 tops 中找到任何记录，并且 topslist 将为空，这在debug_table中的前三条记录中得到了证明。在第二次调用时，在 tops 中找到一条记录，但由于 top_1，top_2 和 top_3 为 null，因此 topslist 再次为 null。如果"失败"中的选择，因为没有值> 0 的tops_1记录，因此将另一条记录插入空值到顶部，这在debug_table记录 4 -6 中是可追溯的。 顺便说一句，检查 varchar 字段中的值> 0 不是一个好主意。