MySQL存储过程执行报错#2014：命令不同步问题求助

我写了两个存储过程，用来统计每个订单的总购票数（订单数据可能分散在多行）。已经确认BEGIN、END和DELIMITER的使用没问题，但执行SQL时出现两个错误：

1. 静态分析提示：Missing expression. (near "ON" at position 25)
2. MySQL返回错误：#2014 - Commands out of sync; you can't run this command now

排查后发现，只有执行最后一行CALL getTotalTicketsBoughtPerOrder(@idCount);时会触发报错，进一步定位到问题出在存储过程getTotalTicketsBoughtPerOrder的WHILE循环内的SELECT @totalTicketsBought ;语句，但不清楚具体原因。

存储过程及执行SQL #

DROP PROCEDURE IF EXISTS getTotalTicketsPerOrder;
DROP PROCEDURE IF EXISTS getTotalTicketsBoughtPerOrder;
DELIMITER //
CREATE PROCEDURE getTotalTicketsPerOrder(
    IN pOrderID INT(11),
    OUT pTotalTickets INT(11)
)
BEGIN
    SELECT SUM(ticketsinorder.NoOfTicketsBought)
    INTO pTotalTickets
FROM
    ticketsinorder
WHERE
    ticketsinorder.OrderID = pOrderID ;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE getTotalTicketsBoughtPerOrder(IN idCount INT(11))
BEGIN
    DECLARE counter INT DEFAULT 1;
    WHILE counter <= idCount DO
CALL getTotalTicketsPerOrder(counter, @totalTicketsBought) ;
SELECT @totalTicketsBought ;
SET counter = counter + 1 ;
END WHILE ;
END //
DELIMITER ;
SELECT @idCount := COUNT(orders.OrderID)
FROM orders;
CALL getTotalTicketsBoughtPerOrder(@idCount);

错误日志 #

2022-08-22 20:10:26 0 [Note] mysqld.exe: Aria engine: recovery done
2022-08-22 20:10:26 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2022-08-22 20:10:26 0 [Note] InnoDB: Uses event mutexes
2022-08-22 20:10:26 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-08-22 20:10:26 0 [Note] InnoDB: Number of pools: 1
2022-08-22 20:10:26 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-08-22 20:10:26 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2022-08-22 20:10:26 0 [Note] InnoDB: Completed initialization of buffer pool
2022-08-22 20:10:26 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2084794
2022-08-22 20:10:26 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-08-22 20:10:26 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2022-08-22 20:10:26 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-08-22 20:10:26 0 [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-08-22 20:10:26 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2022-08-22 20:10:26 0 [Note] InnoDB: Waiting for purge to start
2022-08-22 20:10:26 0 [Note] InnoDB: 10.4.22 started; log sequence number 2084803; transaction id 4233
2022-08-22 20:10:26 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2022-08-22 20:10:26 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-08-22 20:10:26 0 [Note] InnoDB: Buffer pool(s) load completed at 220822 20:10:26
2022-08-22 20:10:26 0 [Note] Server socket created on IP: '::'.

错误原因 #

#2014 - Commands out of sync错误的核心原因是：存储过程getTotalTicketsBoughtPerOrder的WHILE循环中，每次执行SELECT @totalTicketsBought ;都会向客户端返回一个独立结果集。循环多次执行时会连续返回多个结果集，而多数SQL客户端（比如phpMyAdmin）无法正确处理这种连续多结果集的情况，从而触发同步错误。

修复方案 #

方案1：用单条SQL直接统计（推荐） #

完全不需要存储过程，通过分组查询就能高效实现需求，还能避免循环带来的性能问题：

SELECT 
    orders.OrderID,
    COALESCE(SUM(ticketsinorder.NoOfTicketsBought), 0) AS TotalTicketsBought
FROM orders
LEFT JOIN ticketsinorder ON orders.OrderID = ticketsinorder.OrderID
GROUP BY orders.OrderID;

用COALESCE可以确保没有购票记录的订单也返回0，而不是NULL。

方案2：修改存储过程，避免多结果集 #

如果一定要用存储过程，可以把循环结果收集到临时表中，最后一次性返回：

DROP PROCEDURE IF EXISTS getTotalTicketsPerOrder;
DROP PROCEDURE IF EXISTS getTotalTicketsBoughtPerOrder;
DELIMITER //
CREATE PROCEDURE getTotalTicketsPerOrder(
    IN pOrderID INT(11),
    OUT pTotalTickets INT(11)
)
BEGIN
    SELECT COALESCE(SUM(ticketsinorder.NoOfTicketsBought), 0)
    INTO pTotalTickets
    FROM ticketsinorder
    WHERE ticketsinorder.OrderID = pOrderID ;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE getTotalTicketsBoughtPerOrder(IN idCount INT(11))
BEGIN
    DECLARE counter INT DEFAULT 1;
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS OrderTicketTotals (
        OrderID INT(11),
        TotalTickets INT(11)
    );
    TRUNCATE TABLE OrderTicketTotals;
    
    WHILE counter <= idCount DO
        CALL getTotalTicketsPerOrder(counter, @totalTicketsBought) ;
        -- 将结果插入临时表
        INSERT INTO OrderTicketTotals (OrderID, TotalTickets) VALUES (counter, @totalTicketsBought);
        SET counter = counter + 1 ;
    END WHILE ;
    
    -- 一次性返回所有结果
    SELECT * FROM OrderTicketTotals;
    DROP TEMPORARY TABLE IF EXISTS OrderTicketTotals;
END //
DELIMITER ;
SELECT @idCount := COUNT(orders.OrderID) FROM orders;
CALL getTotalTicketsBoughtPerOrder(@idCount);

关于静态分析错误 #

静态分析提示的Missing expression. (near "ON" at position 25)大概率是SQL编辑器的误报，你的代码本身没有语法错误，修复上述多结果集问题后，这个提示应该会自动消失。

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