如何在MySQL的SELECT语句中使用存储过程？附示例代码

Hey there! Let's break down how to work with your listing_count procedure alongside SELECT queries. First, a key note: MySQL doesn’t let you directly call a stored procedure inside a SELECT clause. Stored procedures are designed to run operational tasks (like creating temporary tables, looping through data) rather than returning a value or result set that SELECT can directly reference. But based on what your procedure appears to do—recursively fetching all category IDs linked to a parent category—we’ve got a few solid workarounds:

Option 1: Convert the Procedure to a Custom Function #

If your goal is to get a reusable set of category IDs to filter your SELECT results, turning your logic into a user-defined function makes sense. This lets you call it directly in your query. Here’s how to rewrite it:

DELIMITER //
DROP FUNCTION IF EXISTS get_category_ids//
CREATE FUNCTION get_category_ids(parent INT(11)) RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE ids TEXT DEFAULT CAST(parent AS CHAR);
    DECLARE temp_ids TEXT;
    
    REPEAT
        SELECT GROUP_CONCAT(DISTINCT id SEPARATOR ',')
        INTO temp_ids
        FROM category
        WHERE FIND_IN_SET(related, ids) AND NOT FIND_IN_SET(id, ids);
        
        IF temp_ids IS NOT NULL THEN
            SET ids = CONCAT(ids, ',', temp_ids);
        END IF;
    UNTIL temp_ids IS NULL END REPEAT;
    
    RETURN ids;
END //
DELIMITER ;

Now you can use it in a SELECT like this:

SELECT * FROM listings 
WHERE FIND_IN_SET(category_id, get_category_ids(123)); -- Replace 123 with your parent ID

Option 2: Use the Stored Procedure to Generate a Temp Table, Then Query It #

If you want to stick with your stored procedure, you can run it first to populate a temporary table, then join that table in your SELECT. First, let’s finish and clean up your procedure:

DELIMITER //
DROP PROCEDURE IF EXISTS listing_count//
CREATE PROCEDURE listing_count(IN parent INT(11))
BEGIN
    DECLARE count1 INT(11) DEFAULT 0;
    DECLARE count2 INT(11) DEFAULT 1;
    
    -- Clear existing temp table if it exists
    DROP TEMPORARY TABLE IF EXISTS ids;
    CREATE TEMPORARY TABLE ids AS (SELECT id FROM category WHERE id = parent);
    
    WHILE count1 <> count2 DO
        SET count1 = (SELECT COUNT(id) FROM ids);
        INSERT INTO ids(id)
        SELECT id FROM category 
        WHERE id NOT IN (SELECT id FROM ids) 
          AND related IN (SELECT id FROM ids);
        SET count2 = (SELECT COUNT(id) FROM ids);
    END WHILE;
END //
DELIMITER ;

Then execute the procedure first, then run your SELECT:

-- Call the procedure to build the temp table of category IDs
CALL listing_count(123);
-- Join the temp table with your listings data
SELECT l.* FROM listings l
JOIN ids ON l.category_id = ids.id;

Option 3: Use a Recursive CTE (MySQL 8.0+) #

If you’re running MySQL 8.0 or newer, recursive Common Table Expressions (CTEs) are the cleanest solution—no procedures or functions needed. You can write the recursive logic directly in your SELECT query:

WITH RECURSIVE category_tree AS (
    SELECT id FROM category WHERE id = 123 -- Replace with your parent ID
    UNION ALL
    SELECT c.id FROM category c
    JOIN category_tree ct ON c.related = ct.id
)
SELECT l.* FROM listings l
JOIN category_tree ct ON l.category_id = ct.id;

This approach is more readable, maintainable, and avoids the overhead of stored procedures or functions for simple recursive fetching.

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