MySQL - 结果由多个行组成

问题描述：

我是一个MySQL新手，一直在尝试创建一个存储过程。下面的代码返回错误Error Code: 1172. Result consisted of more than one row。我究竟做错了什么？（我使用MySQL的工作台）MySQL - 结果由多个行组成

CREATE DEFINER=`root`@`localhost` PROCEDURE `season_private_league_user`(
IN user_id INT, 
OUT league_name VARCHAR(25), 
OUT host_user VARCHAR(30)) 
BEGIN 

    DECLARE userteamid INT; 
    DECLARE var_league_name VARCHAR(25); 
    DECLARE var_host_user VARCHAR(30); 

    # Retrieve user team from user_id 
    SELECT CS_USER_TEAMS_ID INTO userteamid 
    FROM classicseasonmodel_classicseasonuserteam 
    WHERE user_id = user_id; 

    #LEAGUE NAME 
    SELECT classicseasonmodel_classicseasonprivateleague.private_league_name INTO var_league_name 
    FROM classicseasonmodel_classicseasonuserteamprivateleague 
    INNER JOIN classicseasonmodel_classicseasonprivateleague 
    ON classicseasonmodel_classicseasonuserteamprivateleague.private_league_id=classicseasonmodel_classicseasonprivateleague.CS_PRIVATE_LEAGUE_ID 
    WHERE user_team_id = userteamid; 

    #HOST_USER 
    SELECT classicseasonmodel_classicseasonprivateleague.host_user_id INTO var_host_user 
    FROM classicseasonmodel_classicseasonuserteamprivateleague 
    INNER JOIN classicseasonmodel_classicseasonprivateleague 
    ON classicseasonmodel_classicseasonuserteamprivateleague.private_league_id=classicseasonmodel_classicseasonprivateleague.CS_PRIVATE_LEAGUE_ID 
    WHERE user_team_id = userteamid; 

    SET league_name = var_league_name; 
    SET host_user = var_host_user; 

END

CALL season_private_league_user(2, @league_name, @host_user); SELECT @league_name AS league_name; SELECT @host_user AS host_user;

答

您的列名和参数名是相同的。重命名您的输入参数并将命令更改为：

SELECT CS_USER_TEAMS_ID INTO userteamid 
FROM classicseasonmodel_classicseasonuserteam 
WHERE user_id = @user_id;

答

一说结果存储在一个变量返回多行，它返回该错误的存储过程，你的SELECT。这样，您只能将一个值存储在一个变量中，而不是多个值。

你可以阅读关于SELECT ... INTO语句here。您可能最感兴趣的部分是：

将所选值分配给变量。变量的数量必须与列数相匹配。查询应该返回一个单行。如果查询不返回行，则会发生错误代码为 1329的警告（无数据），并且变量值保持不变。如果查询返回多行，则会发生错误1172（结果包含多行的）。如果语句可能检索多行，则可以使用LIMIT 1将结果集限制为单行的。

MySQL - 结果由多个行组成

相关推荐