MySQL -存储过程错误操作数应包含1列

Question

我在MySQL数据库中有这个存储过程，但是当我像下面这样测试它时，我得到了Operand should contain 1 column(s)错误：

CALL `get_strongest_policy_from_all_involved_accounts_for_user`(57);

这是我的存储过程。从第6行到第20行的代码工作正常，它返回一个0或更多记录的表。

有人能帮我弥补错误吗?我不知道是什么原因造成的。非常感谢！

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_strongest_policy_from_all_involved_accounts_for_user` (
  IN app_user_id INT
)
BEGIN
  CREATE TEMPORARY TABLE `ParentAccountPolicies` ENGINE=MEMORY SELECT (
    SELECT UAS.`password_rotation_active`, UAS.`password_rotation_days`, UAS.`Password_minimum_lenght`,
     UAS.`password_must_contain_numeric_char`, UAS.`password_must_contain_lowercase_char`,
           UAS.`password_must_contain_upercase_char`, UAS.`password_must_contain_special_char`
    FROM `user_account` UA, `user_account_settings` UAS
    WHERE UA.`account_id` = UAS.`account_id`
  AND UA.`primary_user_id` = app_user_id
    UNION
    SELECT UAS.`password_rotation_active`, UAS.`password_rotation_days`, UAS.`Password_minimum_lenght`,
     UAS.`password_must_contain_numeric_char`, UAS.`password_must_contain_lowercase_char`,
           UAS.`password_must_contain_upercase_char`, UAS.`password_must_contain_special_char`
    FROM `user_account` UA, `business` B, `app_user_n_business` AUB, `user_account_settings` UAS
    WHERE UA.`business_id` = B.`business_id`
  AND B.`business_id` = AUB.`business_id`
        AND UAS.`account_id` = UA.`account_id`
        AND AUB.`app_user_id` = app_user_id
);

  SET @RotationActive = B'0', @HaveNum = B'0', @HaveLowCase = B'0', @HaveUpCase = B'0', @HaveSpecial = B'0';
  SET @RotationDays = 0, @MinLength = 0;

  IF (SELECT DISTINCT `password_rotation_active` FROM `ParentAccountPolicies` WHERE `password_rotation_active` = TRUE) IS NOT NULL THEN
    SET @RotationActive = B'1';
  END IF;

  IF (SELECT DISTINCT `password_must_contain_numeric_char` FROM `ParentAccountPolicies` WHERE `password_must_contain_numeric_char` = TRUE) IS NOT NULL THEN
    SET @HaveNum = B'1';
  END IF;

  IF (SELECT DISTINCT `password_must_contain_lowercase_char` FROM `ParentAccountPolicies` WHERE `password_must_contain_lowercase_char` = TRUE) IS NOT NULL THEN
    SET @HaveLowCase = B'1';
  END IF;

  IF (SELECT DISTINCT `password_must_contain_upercase_char` FROM `ParentAccountPolicies` WHERE `password_must_contain_upercase_char` = TRUE) IS NOT NULL THEN
    SET @HaveUpCase = B'1';
  END IF;

  IF (SELECT DISTINCT `password_must_contain_special_char` FROM `ParentAccountPolicies` WHERE `password_must_contain_special_char` = TRUE) IS NOT NULL THEN
    SET @HaveSpecial = B'1';
  END IF;    

    SELECT MAX(`password_rotation_days`) INTO @RotationDays FROM `ParentAccountPolicies`;
    SELECT MAX(`Password_minimum_lenght`) INTO @RotationDays FROM `ParentAccountPolicies`;

    CREATE TEMPORARY TABLE `FinalPolicy` (
        `RotationActive` BIT,
        `RotationDays` INT,
        `MinimumLength` INT,
        `ShouldHaveDigit` BIT,
        `ShouldHaveLowerCaseChar` BIT,
        `ShouldHaveUpperCaseChar` BIT,
        `ShouldHaveSpecialChar` BIT
    );

    INSERT INTO `FinalPolicy` VALUES (@RotationActive, @RotationDays, @MinLength, @HaveNum, @HaveLowCase, @HaveUpCase, @HaveSpecial);

    SELECT `FinalPolicy`;
END;

Answer 1

你做这样的事：

CREATE TEMPORARY TABLE `ParentAccountPolicies` ENGINE=MEMORY SELECT (
  SELECT 'a' as x, 'b' as y
  FROM dual
  UNION
  SELECT 'c' as x, 'd' as y
  FROM dual
);

这将导致以下错误：

ER_OPERAND_COLUMNS: Operand should contain 1 column(s)

演示

原因是您有一个子选择( SELECT子句中的子查询)，必须返回一个列，最多返回一行。但是您的子选择返回多个列。

但您根本不需要使用子选择。只需移除外部选择，如下所示：

CREATE TEMPORARY TABLE `ParentAccountPolicies` ENGINE=MEMORY  
  SELECT 'a' as x, 'b' as y
  FROM dual
  UNION
  SELECT 'c' as x, 'd' as y
  FROM dual
;

演示