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本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2013年08月27日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读