Result consisted of more than one row错误的修正

今天照例发来了要送道具的名单。

我很快生成好了发放道具的脚本，发给平哥执行。但后来平哥找我说，大部分的帐号执行成功，有一小部分的帐户执行进有错误。具体的错误是：

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))
BEGIN
	DECLARE 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 username
		SELECT CONCAT('The player \'',PlayerName,'\' is not found in GameDB!') INTO ResultString;
	ELSE
		SELECT DISTINCT CharacterAttr.Gender INTO Gender FROM CharacterAttr, GameCharacter
			WHERE CharacterAttr.CharacterID = GameCharacter.CharacterID
			AND GameCharacter.AccountID= AID; 
		IF (Gender =0) THEN # it is a Male
			CALL 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;
			ELSE
				SELECT 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, GameCharacter
			WHERE CharacterAttr.CharacterID = GameCharacter.CharacterID
			AND GameCharacter.AccountID= AID; 

我仔细想了想，因为新增加了斗地主的游戏，对应的游戏角色(Character)也增加了，所以CharacterAttr表中选取出的Gender也有可能是多个：对于已经注册了斗地主游戏的，就会有两个Character, 这样的玩家在存储过程执行的时候就会出错；如果没有注册斗地主游戏的，因为只有一个游戏角色，所以在执行的时候就不会出错。

既然知道哪里出错了，修正的方法也就有了：

DROP PROCEDURE IF EXISTS IssueAvatarItems;
CREATE PROCEDURE IssueAvatarItems (IN PlayerName VARCHAR(20), OUT ResultString VARCHAR(100))
BEGIN
	DECLARE 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 username
		SELECT CONCAT('The player \'',PlayerName,'\' is not found in GameDB!') INTO ResultString;
	ELSE
		SELECT DISTINCT CharacterAttr.Gender INTO Gender FROM CharacterAttr, GameCharacter
			WHERE CharacterAttr.CharacterID = GameCharacter.CharacterID
			AND GameCharacter.AccountID= AID LIMIT 1; 
		IF (Gender =0) THEN # it is a Male
			CALL 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;
			ELSE
				SELECT 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()这样的系统函数，就不用担心这个问题了。