mysql - MySQL - 存储过程错误操作数应包含 1 列
问题描述
我在 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;
解决方案
你做这样的事情:
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 子句中的子查询),它必须只返回一列和最多一行。但是您的子选择返回多个列。
但是您根本不需要使用子选择。只需像这里一样删除外部 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
;
推荐阅读
- c# - 如何在 xamarin 或 c# 或 python 中检测图像中的所有分隔线?
- keras - Keras 数据增强更改掩码的像素值(分割)
- c++ - 如何在不增加向量大小的情况下保留多维向量?
- c - #define FOO(x,c) (void)( { c = ( x ) ; }) 有什么作用?
- javascript - 询问位置权限时,Expo android 独立应用程序崩溃
- android-studio - Android Studio 中没有 GitHub 集成选项
- c# - 检查器中的 Unity C# 对象数组
- python - 仅在一个 conda 虚拟环境的系统路径中包含 FreeCAD
- node.js - 当 Websockets 协议被禁用时会发生什么?
- php - (E_ERROR) Symfony\Component\HttpKernel\Profiler\Profile::__construct() 函数的参数太少,通过了 0