MySQL含多条件CASE语句的存储过程未按预期执行问题求助

Let's break down why your stored procedure isn't returning results and fix the issues step by step:

1. Implicit Conversion Mismatch (Root Cause of Branch Not Triggering) #

Your parameters Tag1, Tag2, Tag3 are defined as VARCHAR(10), but you're comparing them to the number 0 (e.g., Tag1 <> 0). MySQL will try to convert string values to numbers for this comparison:

• When you pass "T1" as Tag1, it gets converted to 0 (since non-numeric text converts to 0 in MySQL)
• This makes Tag1 <> 0 evaluate to FALSE, so your second CASE branch never runs—instead, the procedure hits the ELSE clause and just sets exampleVar2v to 1 without returning any data.

Fix this by using string comparisons instead:

-- Replace conditions like Tag1 <> 0 with checks for string values
Tag1 != '0' AND Tag1 != ''

2. Incorrect GROUP BY Logic in Subqueries #

Even if the branch was triggering, your subqueries have a logical error in grouping. For example, in the second branch:

SELECT R_id FROM Key_Tag WHERE Tag_id IN (Tag1,Tag2) GROUP BY Tag_id HAVING COUNT(*) = 2

Grouping by Tag_id means you're counting how many times each tag appears, not how many tags are linked to each R_id. You need to group by R_id and count distinct tags to find records that match all specified tags:

SELECT R_id 
FROM Key_Tag 
WHERE Tag_id IN (Tag1, Tag2) 
GROUP BY R_id 
HAVING COUNT(DISTINCT Tag_id) = 2

This ensures you're selecting R_ids that have both of the specified tags associated with them. The same fix applies to the third branch's subquery.

Corrected Stored Procedure Code #

Here's the revised version with both fixes applied:

DELIMITER $$
CREATE PROCEDURE testSearch(
 IN Tag1 VARCHAR(10),
 IN Tag2 VARCHAR(10),
 IN Tag3 VARCHAR(10),
 IN TagTerm VARCHAR(10),
 IN SearchArea VARCHAR(50)
)
BEGIN
 DECLARE exampleVar2v INT DEFAULT 0;
 CASE
 WHEN Tag1 != '0' AND Tag1 != '' AND Tag2 = '0' AND Tag3 = '0' AND TagTerm = "Tag" AND SearchArea = "Responsibility" THEN
 SELECT DISTINCT R_Name FROM Responsibility WHERE R_id IN (SELECT R_id FROM Key_Tag WHERE Tag_id = Tag1) ;
 WHEN Tag1 != '0' AND Tag1 != '' AND Tag2 != '0' AND Tag2 != '' AND Tag3 = '0' AND TagTerm = "Tag" AND SearchArea = "Responsibility" THEN
 SELECT DISTINCT R_Name FROM Responsibility WHERE R_id IN (
     SELECT R_id FROM Key_Tag WHERE Tag_id IN (Tag1,Tag2) 
     GROUP BY R_id HAVING COUNT(DISTINCT Tag_id) = 2
 ) ;
 WHEN Tag1 != '0' AND Tag1 != '' AND Tag2 != '0' AND Tag2 != '' AND Tag3 != '0' AND Tag3 != '' AND TagTerm = "Tag" AND SearchArea = "Responsibility" THEN
 SELECT DISTINCT R_Name FROM Responsibility WHERE R_id IN (
     SELECT R_id FROM Key_Tag WHERE Tag_id IN (Tag1,Tag2,Tag3) 
     GROUP BY R_id HAVING COUNT(DISTINCT Tag_id) = 3
 ) ;
 ELSE
 set exampleVar2v = 1;
 END CASE;
END $$

Testing the Fixed Procedure #

Now when you run your call:

CALL testSearch("T1","T2","0","Tag","Responsibility");

It should correctly trigger the second CASE branch and return the expected results.

内容的提问来源于stack exchange，提问作者Brendon