Result consisted of more than one row错误的修正
来源:互联网 发布:港湾网络 李一男 编辑:程序博客网 时间:2024/05/24 15:36
今天照例发来了要送道具的名单。
我很快生成好了发放道具的脚本,发给平哥执行。但后来平哥找我说,大部分的帐号执行成功,有一小部分的帐户执行进有错误。具体的错误是:
mysql> call guocui3.IssueAvatarItems('Jiangming', @ResultString);ERROR 1172 (42000): Result consisted of more than one row
一看到这个错误,就知道是在使用SELECT INTO的时候出现了多于一行的情况,导致IssueAvatarItems这个存储过程执行错误。
于是打开这个存储过程查看其内容如下:
DROP PROCEDURE IF EXISTS IssueAvatarItems;CREATE PROCEDURE IssueAvatarItems (IN PlayerName VARCHAR(20), OUT ResultString VARCHAR(100))BEGINDECLARE Gender int;DECLARE AID int;DECLARE RCount int;SELECT 'Start processing player info...' into ResultString;SELECT Account.AccountID into AID from Account where UserName = PlayerName;/*Get the accountID from the player's username*/SELECT FOUND_ROWS() into RCount;IF (RCount=0) THEN # if there is no such player with the usernameSELECT CONCAT('The player \'',PlayerName,'\' is not found in GameDB!') INTO ResultString;ELSESELECT DISTINCT CharacterAttr.Gender INTO Gender FROM CharacterAttr, GameCharacterWHERE CharacterAttr.CharacterID = GameCharacter.CharacterIDAND GameCharacter.AccountID= AID; IF (Gender =0) THEN # it is a MaleCALL IssueMaleAvatarItem(AID);ELSEIF (Gender =1) THEN # if it is a Female CALL IssueFemaleAvatarItem(AID);END IF;SELECT ItemID from PlayerItem WHERE AccountID= AID;/*Checking inserting results*/SELECT FOUND_ROWS() into RCount;IF (RCount>5) THEN SELECT CONCAT('Player: \'',PlayerName, '\' has been successfully given ',RCount,' Avatar Items!') INTO ResultString;ELSESELECT CONCAT('Player: \'',PlayerName, '\' has errors in issuing avatar items!') INTO ResultString;END IF;SELECT ResultString;END IF;END;
这里使用SELECT INTO的地方有不少,去掉使用SELECT FOUND_ROWS()的两处地方,就只剩下两处:
SELECT Account.AccountID into AID from Account where UserName = PlayerName;
和
SELECT DISTINCT CharacterAttr.Gender INTO Gender FROM CharacterAttr, GameCharacterWHERE CharacterAttr.CharacterID = GameCharacter.CharacterIDAND GameCharacter.AccountID= AID;
我仔细想了想,因为新增加了斗地主的游戏,对应的游戏角色(Character)也增加了,所以CharacterAttr表中选取出的Gender也有可能是多个:对于已经注册了斗地主游戏的,就会有两个Character, 这样的玩家在存储过程执行的时候就会出错;如果没有注册斗地主游戏的,因为只有一个游戏角色,所以在执行的时候就不会出错。
既然知道哪里出错了,修正的方法也就有了:
DROP PROCEDURE IF EXISTS IssueAvatarItems;CREATE PROCEDURE IssueAvatarItems (IN PlayerName VARCHAR(20), OUT ResultString VARCHAR(100))BEGINDECLARE Gender int;DECLARE AID int;DECLARE RCount int;SELECT 'Start processing player info...' into ResultString;SELECT Account.AccountID into AID from Account where UserName = PlayerName LIMIT 1;/*Get the accountID from the player's username*/SELECT FOUND_ROWS() into RCount;IF (RCount=0) THEN # if there is no such player with the usernameSELECT CONCAT('The player \'',PlayerName,'\' is not found in GameDB!') INTO ResultString;ELSESELECT DISTINCT CharacterAttr.Gender INTO Gender FROM CharacterAttr, GameCharacterWHERE CharacterAttr.CharacterID = GameCharacter.CharacterIDAND GameCharacter.AccountID= AID LIMIT 1; IF (Gender =0) THEN # it is a MaleCALL IssueMaleAvatarItem(AID);ELSEIF (Gender =1) THEN # if it is a Female CALL IssueFemaleAvatarItem(AID);END IF;SELECT ItemID from PlayerItem WHERE AccountID= AID;/*Checking inserting results*/SELECT FOUND_ROWS() into RCount;IF (RCount>5) THEN SELECT CONCAT('Player: \'',PlayerName, '\' has been successfully given ',RCount,' Avatar Items!') INTO ResultString;ELSESELECT CONCAT('Player: \'',PlayerName, '\' has errors in issuing avatar items!') INTO ResultString;END IF;SELECT ResultString;END IF;END;
注意高度的部分,其实也就是在有可能出现多个结果的地方都加上了LIMIT 1;这样就能够保证有唯一确定的一行的数据中的某列可以赋值给对应的变量。
修改以后,在测试环境上执行正常。已经布到PRODUCTION上去了,再执行发放道具的脚本,一切正常。
这个问题提醒我们,在使用存储过程或函数的时候,如果使用SELECT INTO语句,一定要保证返回的数据是唯一的一列。当然如果是使用COUNT(), FOUND_ROWS()这样的系统函数,就不用担心这个问题了。
- Result consisted of more than one row错误的修正
- Result consisted of more than one row 错误的解决
- Result consisted of more than one row
- Result consisted of more than one row
- MYSQL存储过程出现Result consisted of more than one row 错误的解决方法
- Mysql报错:Result consisted of more than one row
- ERROR 1172 (42000): Result consisted of more than one row
- [Err] 1172 - Result consisted of more than one row
- 存储过程错误 1172 - Result consisted of more than one row
- mysql数学函数存储过程 Result consisted of more than one row
- Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Result consisted of more than
- ORA-01427:single-row subquery returns more than one row
- mybatis中:returned more than one row, where no more than one was expected
- Mybatis中关于:Statement returned more than one row, where no more than one was expected.
- more than one row with the given identifier was found
- ORA-01427single-row subquery returns more than one
- more than one device and emulator错误
- javax.persistence.NonUniqueResultException: result returns more than one elements
- Touch Mode备忘录
- 5. OpenGL综合知识 --- Memory类型和数据传递
- 个人博客:http://blog.liangston.com上线
- 传智播客——毕向东25--第10天总结-9
- 源码安装MySql的方法
- Result consisted of more than one row错误的修正
- android 学习笔记(三) 第一个Android应用HelloWorld
- PHP遍历目录文件的几种方法效率评析
- tesseract3.01的训练和使用
- 简单实现window与linux之间的文件共享
- 启用WCF测试客户端(WCF Test Client)的相关技巧
- 活动图和流程图的区别
- andriod的apk文件相关的编译反编译工具
- setInterval与ajax——ajax的缓存问题