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


 

  • 2
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
MySQL编程基础: MySQL是一个关系型数据库管理系统,它使用SQL语言进行数据操作和管理。MySQL支持多种编程语言,例如Python、Java、C++和JavaScript等。在MySQL编程中,通常需要掌握以下基础知识: 1. 数据类型:MySQL支持多种数据类型,例如整数、浮点数、日期、字符串等。 2. 数据库连接:在MySQL编程中,需要使用连接字符串来连接数据库。连接字符串包括服务器名称、用户名、密码和数据库名称等信息。 3. 数据库操作:MySQL支持多种数据库操作,例如查询、插入、更新和删除等。 4. 错误处理:在MySQL编程中,需要处理可能出现的错误,例如连接失败、SQL语句错误等。 存储过程存储过程是一种在MySQL中定义的可重用的程序,它可以接收参数并返回结果。存储过程通常用于执行常见的数据库操作,例如插入、更新和删除等。存储过程有以下优点: 1. 可重用性:存储过程可以在多个应用程序中重复使用,减少了代码的重复编写。 2. 性能优化:存储过程可以在数据库服务器上执行,因此可以减少网络传输和客户端计算的开销。 3. 安全性:存储过程可以限制用户对数据库的访问权限,从而提高了数据库的安全性。 在MySQL中,需要使用CREATE PROCEDURE语句来定义存储过程。例如,以下是一个简单的存储过程,它接收一个整数参数并返回一个字符串: ``` CREATE PROCEDURE myprocedure (IN id INT, OUT result VARCHAR(255)) BEGIN SELECT name INTO result FROM mytable WHERE id = id; END ``` 在此存储过程中,IN关键字表示输入参数,OUT关键字表示输出参数,BEGIN和END之间的语句存储过程的主体。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值