MySQL的存储过程,检查是否存在记录
问题描述:
我创建了以下存储过程:MySQL的存储过程,检查是否存在记录
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_summit`(IN `assoc_code` CHAR(5), IN `assoc_name` CHAR(50), IN `reg_code` CHAR(2), IN `reg_name` CHAR(100), IN `code` CHAR(20), IN `name` CHAR(100), IN `sota_id` CHAR(5), IN `altitude_m` SMALLINT(5), IN `altitude_ft` SMALLINT(5), IN `longitude` DECIMAL(10,4), IN `latitude` DECIMAL(10,4), IN `points` TINYINT(3), IN `bonus_points` TINYINT(3), IN `valid_from` DATE, IN `valid_to` DATE)
BEGIN
declare assoc_id SMALLINT(5);
declare region_id SMALLINT(5);
declare summit_id MEDIUMINT(8);
-- ASSOCIATION check if an association with the given code and name already exists
SELECT id INTO assoc_id FROM association WHERE code = assoc_code LIMIT 1;
IF (assoc_id IS NULL) THEN
INSERT INTO association(code, name) VALUES (assoc_code, assoc_name);
set assoc_id = (select last_insert_id());
END IF;
-- REGION check if a region with the given code and name already exists
SET region_id = (SELECT id FROM region WHERE code = reg_code AND name = reg_name AND association_id = assoc_id);
IF (region_id IS NULL) THEN
INSERT INTO region(association_id, code, name) VALUES (assoc_id, reg_code, reg_name);
set region_id = (select last_insert_id());
END IF;
-- SUMMIT check if a summit with given parameters already exists
SET summit_id = (SELECT id FROM summit WHERE association_id = assoc_id AND region_id = region_id);
IF (summit_id IS NULL) THEN
INSERT INTO summit(code, name, sota_id, association_id, region_id, altitude_m, altitude_ft, longitude,
latitude, points, bonus_points, valid_from, valid_to)
VALUES (code, name, sota_id, assoc_id, region_id, altitude_m, altitude_ft, longitude, latitude,
points, bonus_points, valid_from, valid_to);
END IF;
END$$
基本上,它应该和检查,如果在一些表中存在的记录,如果没有,就应该将其插入和使用插入的id(自动增量)。 问题是,即使记录存在(例如在关联表中),assoc_id也会一直返回null,并导致记录重复。 我是新来的存储过程,所以我可能会做一些愚蠢的错误。我一直试图调试这个SP几个小时,但我找不到问题。
答
一个新手的错误。 我忘了在字段比较中指定表名,并导致与参数名称(例如参数名称)的一些冲突。 一个好主意是为参数指定某种类型的前缀(如p_),并始终在SP中指定表的名称。
您可能会遇到'empty!= NULL'问题。看看[这](http://*.com/questions/17832906/how-to-check-if-field-is-null-or-empty-mysql)的答案。也许有一些尝试... – ethrbunny 2014-10-08 16:32:03