MySQL存储过程无匹配数据时返回全空行，如何改为返回无结果？

我正在开发一个用于简单AJAX用户搜索的MySQL存储过程，该过程基于多个字段并使用LIKE进行关键词查询。当前的问题是：当没有匹配数据时，它会返回一行全为NULL的结果，而不是无结果。

存储过程代码如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `searchCustomerOrders`(IN in_term varchar(60)) 
BEGIN 
  SELECT 
    u.first_name as firstName, 
    u.last_name as lastName, 
    os.status, 
    o.orderID, 
    CASE WHEN COUNT(op.orderID) = 0 THEN null ELSE COUNT(op.orderID) END as qty 
  FROM `order` as o 
  JOIN order_product as op ON o.orderID = op.orderID 
  JOIN order_status as os ON o.statusID = os.statusID 
  JOIN users as u ON o.userID = u.ID 
  WHERE o.orderID LIKE CONCAT('%', in_term, '%') 
     or u.last_name LIKE CONCAT('%', in_term, '%') 
     or u.first_name LIKE CONCAT('%', in_term, '%') 
     or u.last_name + ', ' + u.first_name LIKE CONCAT('%', in_term, '%') 
     or os.status LIKE CONCAT('%', in_term, '%'); 
END

我发现问题似乎出在COUNT语句上，移除该语句后会返回正确的空结果。请问如何修改才能保留COUNT统计的同时，在无匹配数据时返回空结果？

这是因为COUNT()是聚合函数，当你在SELECT中使用聚合函数但没有GROUP BY子句时，MySQL会把整个结果集当作一个分组处理——哪怕没有匹配的行，聚合函数也会返回一个统计值（这里COUNT(op.orderID)无匹配时返回0，CASE把它转成NULL，但整行还是会被输出）。

要解决这个问题，你可以按以下两种方式修改：

方法1：添加GROUP BY子句 #

既然你需要统计每个订单的产品数量，应该按订单相关的唯一字段分组，比如o.orderID（同时包含其他非聚合字段，确保分组唯一性）。这样当没有匹配订单时，GROUP BY不会生成任何分组，自然就不会返回行：

CREATE DEFINER=`root`@`localhost` PROCEDURE `searchCustomerOrders`(IN in_term varchar(60)) 
BEGIN 
  SELECT 
    u.first_name as firstName, 
    u.last_name as lastName, 
    os.status, 
    o.orderID, 
    COUNT(op.orderID) as qty  -- 若需要空值可保留CASE，但不影响无结果时的返回逻辑
  FROM `order` as o 
  JOIN order_product as op ON o.orderID = op.orderID 
  JOIN order_status as os ON o.statusID = os.statusID 
  JOIN users as u ON o.userID = u.ID 
  WHERE o.orderID LIKE CONCAT('%', in_term, '%') 
     or u.last_name LIKE CONCAT('%', in_term, '%') 
     or u.first_name LIKE CONCAT('%', in_term, '%') 
     or CONCAT(u.last_name, ', ', u.first_name) LIKE CONCAT('%', in_term, '%')  -- MySQL字符串拼接需用CONCAT，而非+号
     or os.status LIKE CONCAT('%', in_term, '%')
  GROUP BY u.first_name, u.last_name, os.status, o.orderID;  -- 包含所有非聚合字段的GROUP BY
END

方法2：用子查询包裹统计逻辑 #

如果你不想用GROUP BY，也可以把COUNT的统计放到子查询里，这样主查询没有匹配行时就不会返回任何结果：

CREATE DEFINER=`root`@`localhost` PROCEDURE `searchCustomerOrders`(IN in_term varchar(60)) 
BEGIN 
  SELECT 
    u.first_name as firstName, 
    u.last_name as lastName, 
    os.status, 
    o.orderID, 
    (SELECT COUNT(op_inner.orderID) FROM order_product op_inner WHERE op_inner.orderID = o.orderID) as qty
  FROM `order` as o 
  JOIN order_status as os ON o.statusID = os.statusID 
  JOIN users as u ON o.userID = u.ID 
  WHERE o.orderID LIKE CONCAT('%', in_term, '%') 
     or u.last_name LIKE CONCAT('%', in_term, '%') 
     or u.first_name LIKE CONCAT('%', in_term, '%') 
     or CONCAT(u.last_name, ', ', u.first_name) LIKE CONCAT('%', in_term, '%')
     or os.status LIKE CONCAT('%', in_term, '%');
END

另外补充一个小细节：你原代码里用u.last_name + ', ' + u.first_name拼接字符串是错误的——MySQL中+是算术运算符，字符串拼接必须用CONCAT()函数，我在上述示例中已经修正了这个问题，避免出现非预期的计算结果。

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