专栏首页帘卷西风的专栏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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • VS2010/VS2012 设置全局头文件和库路径

            在VS2010之前,设置项目的全局头文件和库路径是非常方便的,直接选择菜单Tools->Options->Projects and Soluti...

    帘卷西风
  • linux安装valgrind

    转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog)

    帘卷西风
  • mysql 大小写敏感的一个解决方案

         今天,有同事告诉我,我们游戏登陆的时候,账号和密码没有区分大小写,后来又发现创建账号和角色也没有区分大小写。思考登陆流程之后,应该是Mysql没有区分...

    帘卷西风
  • 1000 Genome Project

    1000 Genome Project 的目标是发现在人群中频率大于1%的变异位点,对来自不同人群的大量样本进行测序,识别到了许多的变异位点,为人类遗传变异的研...

    生信修炼手册
  • c语言_头文件_stdlib

    landv
  • Identify the logic how BOL node name is categorized into different object type

    Identify the logic how BOL node name is categorized into different object type

    Jerry Wang
  • Kotlin实战【四】迭代事物:while和for

    区间本质:两个值之间的间隔。这两个值通常是数字:一个是起始值,一个是结束值。使用 ..来表示

    先知先觉
  • 如何通过跨学科应用提高对函数式程序设计的兴趣(cs)

    函数式编程代表了应用和实现软件的现代化工具。函数式编程的最新发展报告了这种范式中越来越多的方法。然而,缺乏广泛的跨学科应用。我们的目标是提高学生的兴趣,追求进一...

    用户7454091
  • SQL 性能优化

    Oracle 优化器RBO, CBO RBO 基于规则的优化器 oracle 10g开始,已经丢弃RBO CBO 基于成本的优化器 oracle 8中开始引...

    王小明_HIT
  • [Python]随机生成大量的虚拟信息测试数据(姓名,手机号,ID,家庭住址等)

    泰坦HW

扫码关注云+社区

领取腾讯云代金券