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 条评论
登录 后参与评论

相关文章

来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1312
来自专栏WindCoder

网易MySQL微专业学习笔记(二)-Mysql数据对象

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

721
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1.5K2
来自专栏数据和云

循序渐进:Oracle 12c新特性Sharding技术解读

引言 数据库构架设计中主要有 Shared Everthting、Shared Nothing 和 Shared Disk: Shared Everthting...

4607
来自专栏idba

insert 语句加锁机制

之前的文章里面总结了很多死锁案例,其实里面有几篇文章对于insert加锁流程表述的不准确,而且微信公众号又无法修改,所以通过本文重新梳理insert...

1283
来自专栏友弟技术工作室

MySQL优化思路及框架

MySQL优化框架 1. SQL语句优化 2. 索引优化 3. 数据库结构优化 4. InnoDB表优化 5. MyISAM表优化 6. Memory表优化 7...

37610
来自专栏数据之美

MySQL 死锁与日志二三事

最近线上 MySQL 接连发生了几起数据异常,都是在凌晨爆发,由于业务场景属于典型的数据仓库型应用,白天压力较小无法复现。甚至有些异常还比较诡异,最后 root...

3996
来自专栏杨建荣的学习笔记

MySQL中使用pt-osc的一些小结

Percona的pt-osc工具算是DBA的一个福利工具。想想一个数据量有些大的表,在上面做DDL操作真是一种煎熬,我们也基本理解了这是一种空间换时间的...

1361
来自专栏L宝宝聊IT

索引、视图、存储过程和触发器的应用

1678
来自专栏杨建荣的学习笔记

MySQL中的derived table(r12笔记第47天)

初始MySQL中的derived table还是在一个偶然的问题场景中。 下面的语句在执行的时候抛出了错误。 UPDATE payment_data rr ...

3895

扫码关注云+社区

领取腾讯云代金券