今天在调试一个存储过程时,一直有问题,源码如下:
BEGIN
DECLARE
cnt INT DEFAULT 0;
DECLARE
deId INT DEFAULT 0;
SELECT COUNT(1) FROM zabbixdata WHERE `hostid`=hostId INTO cnt;
IF cnt >= 10 THEN
SELECT id FROM zabbixdata WHERE `hostid`=hostIdORDER BY getdatatime ASC LIMIT 0,1 INTO deId;
DELETE FROM zabbixdata WHERE id = deId;
END IF;
INSERT INTO zabbixdata (hostid,getdatatime)VALUE(hostIdl, saveTime);
END
传入的两个参数为:IN hostId VARCHAR (64),IN saveTime VARCHAR (64)
主要功能就是每次存入一条数据时,判断该hostId的数据条数是否大于等于10,如果大于等于10删除时间最小的一条后再存入。
SELECT COUNT(1) FROM zabbixdata WHERE `hostid`=hostId INTO cnt;
结果,cnt的值一直为整张表的行数,打印参数hostId值又是正确的,后面查了很多资料才找到问题:在存储过程里面,where后面作为条件的变量名不能和字段名相同,而且是不区分大小写的。所以在
WHERE hostid
=hostId INTO cnt;
类似于WHERE后面的判断是1=1这样永真式,后面将代码调整为:
BEGIN
DECLARE
cnt INT DEFAULT 0;
DECLARE
deId INT DEFAULT 0;
SELECT COUNT(1) FROM zabbixdata WHERE `hostid`=hostIdVal INTO cnt;
IF cnt >= 10 THEN
SELECT id FROM zabbixdata WHERE `hostid`=hostIdVal ORDER BY getdatatime ASC LIMIT 0,1 INTO deId;
DELETE FROM zabbixdata WHERE id = deId;
END IF;
INSERT INTO zabbixdata (hostid,getdatatime)VALUE(hostIdVal, saveTime);
END
传入参数为:IN hostIdVal VARCHAR (64),IN saveTime VARCHAR (64)