mysql存储过程where条件_MySQL存储过程where条件执行失败的问题

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UpdatePlayer`(out returnvalue long,

out returndesc VARCHAR(128),in roleID int,

in level int, in mapID int, in posX int,

in posY int, in attrPoint int,

in faction int, in lastLogin int,

in state int, in expendedStorage int,

in experiencePoint int, in playMoney int,

in subMoney int, in lockedMoney int,

in activist int, in combatNum int,

in achievePoint int, in showSuit bool,

in subMoneyPay bool, in flyingChessData VARCHAR(500),

in apcAsistants VARCHAR(100), in title VARCHAR(30),

in spouseName VARCHAR(20), in teacherName VARCHAR(20),

in integral int, in invalidTime int, in memberPeriod int)

BEGIN

UPDATE player set MapID = mapID, ScenePosX = posX,

ScenePosY = posY, AttributePoint = attrPoint,

Level = level, FactionID = faction, LastLogin = lastLogin,

ExpendedStorage = expendedStorage, ExperiencePoint = experiencePoint,

PlayMoney = playMoney, SubMoney = subMoney, LockedMoney = lockedMoney,

Activist = activist, CombatNum = combatNum, AchievePoint = achievePoint,

ShowSuit = showSuit, SubMoneyPay = subMoneyPay,

FlyingChessData = flyingChessData, APCAsistants = apcAsistants,

Title = title, SpouseName = spouseName, TeacherName = teacherName,

Status = state, Integral = integral,

InvalidTime = invalidTime, MemberPeriod = memberPeriod

WHERE RoleID = roleID;

set returnvalue = 0;

END

前几天也出现了一个类似的问题，当时是保存宠物数据的时候，大概思路是:

select count(*) into count from pet where condition;

if count > 0 then

update pet set key1 = value1, ... where condition;

else

insert into pet (key1, ...) values (value1, ...);

end if;

无论传什么参数，count始终大于0，新建的宠物始终存不到数据库，当时也是调了很久，还以为是mysql的BUG，后来我改用replace into 语句解决了这个问题，以为是偶发，也没深究这个情况。

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UpdatePet`(out returnvalue long,out returndesc VARCHAR(128),

in roleID int, in petID VARCHAR(20), in name VARCHAR(20),

in isBattle bool, in life int, in loyalty int, in maxLife int,

in modelID int, in monsterID int,in phase int, in savvy int,

in enhanceRate int, in sortTime int, in petAdvancedType int,

in isStore bool, in fightAbility int)

BEGIN

replace into pet (RoleID, PetID, Name, IsBattle, Life, Loyalty,

MaxLife, ModelID, MonsterID, Phase, Savvy, EnhanceRate,

SortTime, PetAdvancedType, IsStore, FightAbility)

values(roleID, petID, name, isBattle, life, loyalty, maxLife,

modelID, monsterID, phase, savvy, enhanceRate, sortTime,

petAdvancedType, isStore, fightAbility);

set returnvalue=0;

END

今天又出现这个问题，说明不是偶发问题，下决心要把这个问题搞清楚，对存储过程做了很多次修改和测试，始终找不到问题的关键，后来突然想到一个问题，是不是参数命名问题，改了一些参数命名，某一次突然正确了，经过比较发现，原来where后面作为条件的变量名不能和字段名相同，而且这里是不区分大小写的。但是作为update和insert into的参数确是可以的，mysql真的很坑爹呀。

最后回到最开始的问题，where后面是 RoleID = roleID; 所以执行失败了,只需要把参数roleID改下名，不和字段名RoleID同名即可。修改后的存储过程如下：

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UpdatePlayer`(out returnvalue long,

out returndesc VARCHAR(128), in rID int,

in level int, in mapID int, in posX int,

in posY int, in attrPoint int,

in faction int, in lastLogin int,

in state int, in expendedStorage int,

in experiencePoint int, in playMoney int,

in subMoney int, in lockedMoney int,

in activist int, in combatNum int,

in achievePoint int, in showSuit bool,

in subMoneyPay bool, in flyingChessData VARCHAR(500),

in apcAsistants VARCHAR(100), in title VARCHAR(30),

in spouseName VARCHAR(20), in teacherName VARCHAR(20),

in integral int, in invalidTime int, in memberPeriod int)

BEGIN

UPDATE player set MapID = mapID, ScenePosX = posX,

ScenePosY = posY, AttributePoint = attrPoint,

Level = level, FactionID = faction, LastLogin = lastLogin,

ExpendedStorage = expendedStorage, ExperiencePoint = experiencePoint,

PlayMoney = playMoney, SubMoney = subMoney, LockedMoney = lockedMoney,

Activist = activist, CombatNum = combatNum, AchievePoint = achievePoint,

ShowSuit = showSuit, SubMoneyPay = subMoneyPay,

FlyingChessData = flyingChessData, APCAsistants = apcAsistants,

Title = title, SpouseName = spouseName, TeacherName = teacherName,

Status = state, Integral = integral,

InvalidTime = invalidTime, MemberPeriod = memberPeriod

WHERE RoleID = rID;

set returnvalue = 0;

END