MySQL Case Select行为不符合预期 [英] MySQL Case Select not behaving as expected

问题描述

在MySQL中创建更复杂的存储过程时，我的CASE语句遇到了一个奇怪的问题.我简化了显示问题的程序.我在每个循环中选择三件事来阐明问题:count变量，CASE之前的modTemp变量，然后是代表采用哪个CASE路径的数字.

While creating a more complex stored procedure in MySQL, I encountered a weird problem with my CASE statement. I have simplified my procedure to show the issue. I am selecting three things per loop to clarify the problem: the count variable, the modTemp variable before the CASE, and then a number representing which CASE path was taken.

DELIMITER //
CREATE PROCEDURE `AddPlants` (IN plantNum int)
BEGIN
    DECLARE count int;
    DECLARE modTemp int;

    SET count = 1;
    WHILE count <= plantNum DO
        SELECT CONCAT('count = ', count);
        SET modTemp = count % 3;
        SELECT CONCAT('modTemp = ', modTemp);

        CASE modTemp
            WHEN modTemp = 1 THEN
                SELECT 1;
            WHEN modTemp = 2 THEN
                SELECT 2;
            WHEN modTemp = 0 THEN
                SELECT 3;
            ELSE
                SELECT CONCAT('Error: modTemp = ', modTemp);
        END CASE;

        SET count = count + 1;
    END WHILE;
END//

现在我使用CALL AddPlants(3);这是我的结果:

Now I use CALL AddPlants(3); Here are my results:

预期输出

count = 1，modTemp = 1，1

count = 1, modTemp = 1, 1

count = 2，modTemp = 2，2

count = 2, modTemp = 2, 2

count = 3，modTemp = 0，3

count = 3, modTemp = 0, 3

实际输出

count = 1，modTemp = 1，1

count = 1, modTemp = 1, 1

count = 2，modTemp = 2，错误:modTemp = 2

count = 2, modTemp = 2, Error: modTemp = 2

count = 3，modTemp = 0，1

count = 3, modTemp = 0, 1

每个时间，计数和modTemp是正确的值.但是，在第二个循环中，modTemp为2，但不进入CASE2.在第三个循环中，modTemp为0，但是进入CASE1.这里有什么想法吗?

Each time, count and modTemp are the correct values. However, on the second loop, modTemp is 2 but does not enter CASE 2. And in the third loop, modTemp is 0 but goes into CASE 1. Any ideas what is happening here?

推荐答案

您正在混合使用CASE的两种方式.您可以这样写:

You're mixing the two ways to use CASE. You either write:

CASE
    WHEN <expression1> THEN <result1>;
    WHEN <expression2> THEN <result2>;
    ...
END CASE

这将计算每个表达式，并为第一个真表达式执行相应的结果.或者:

This evaluates each expression, and executes the corresponding result for the first true one. Or:

CASE <expression>
    WHEN <val1> THEN <result1>;
    WHEN <val2> THEN <result2>;
    ...
END CASE

这会将<expression>与每个值进行比较，并为第一个匹配的结果执行相应的结果.

This compares <expression> to each value, and executes the corresponding result for the first one that matches.

您使用了第二种语法，但是您的值也包含一个比较.所以它们都是0(对于false)或1(对于true)，这就是您要与modTemp进行比较的原因.更改为:

You used the second syntax, but your values also contain a comparison. So they're all either 0 (for false) or 1 (for true), and that's what you're comparing modTemp to. Change to:

    CASE modTemp
        WHEN 1 THEN
            SELECT 1;
        WHEN 2 THEN
            SELECT 2;
        WHEN 0 THEN
            SELECT 3;
        ELSE
            SELECT CONCAT('Error: modTemp = ', modTemp);
    END CASE;

这篇关于MySQL Case Select行为不符合预期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！