php 存储过程MySQL的行数

Question

我在 MySQL 中有一个存储过程。我在 php 中将我的程序称为

$qry = $mysqli->prepare("CALL seen_table()");

我尝试通过以下方式获取结果行

$row = $qry->num-rows;

但即使有一个结果集，其结果0。然后我还尝试将输出参数放入我的过程中，其中我的过程内部是...

SELECT COUNT(*) INTO cnt FROM TBL

...那么这是我的代码

 $qry = $mysqli->prepare("CALL seen_table(@cnt)");
 $qry1 = $mysqli->query("SELECT @cnt");
 $row = $qry1->num_rows;

那么现在即使没有计数，结果也总是 1。当我尝试在 Mysql 中执行 CALL 和 SELECT @cnt 时。如果没有计数。结果将是

|@cnt|
|(null)|

null 真的算作 1 吗？请帮忙。非常感谢。

编辑:添加了 saw_table 过程代码

DELIMITER $$

USE `xiroosco_mundoxiro`$$

DROP PROCEDURE IF EXISTS `seen_table`$$

CREATE DEFINER=`xiroosco`@`103.16.170.%` PROCEDURE `seen_table`(bound
VARCHAR(255),IN cmntId INT,IN cmntViewID VARCHAR(255),OUT viewCNT INT) 
BEGIN 

DECLARE seen_ID INT DEFAULT 0; 
DECLARE seen_notifica_ID INT DEFAULT 0; 
DECLARE seen_viewers TEXT; 
DECLARE occurance INT DEFAULT 0; 
DECLARE i INT DEFAULT 0; 
DECLARE splitted_value INT; 
DECLARE done INT DEFAULT 0; 
DECLARE cur1 CURSOR FOR SELECT seen.seen_ID, seen.seen_notifica_ID,
seen.seen_viewers 
                FROM seen 
                WHERE seen.seen_notifica_ID = cmntId; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 

DROP TEMPORARY TABLE IF EXISTS seen2; 
CREATE TEMPORARY TABLE seen2( 
`seen_ID` INT NOT NULL, 
`seen_notifica_ID` INT NOT NULL, 
`seen_viewers` VARCHAR(255) NOT NULL 
) ENGINE=MEMORY; 

OPEN cur1; 
 read_loop: LOOP 
  FETCH cur1 INTO seen_ID,seen_notifica_ID, seen_viewers; 
  IF done THEN 
   LEAVE read_loop; 
  END IF; 

  SET occurance = (SELECT LENGTH(seen_viewers) -
LENGTH(REPLACE(seen_viewers, bound, '')) +1); 
  SET i=1; 
  WHILE i <= occurance DO 
   SET splitted_value = (SELECT
REPLACE(SUBSTRING(SUBSTRING_INDEX(seen_viewers, bound, i), 
   LENGTH(SUBSTRING_INDEX(seen_viewers, bound, i - 1)) + 1), ',', '')); 

   INSERT INTO seen2 VALUES (seen_ID,seen_notifica_ID, splitted_value); 
   SET i = i + 1; 

  END WHILE; 
 END LOOP; 
 IF cmntViewID = "*" THEN 
  SELECT * FROM seen2 GROUP BY seen2.seen_viewers; 
  SELECT COUNT(*) INTO viewCNT FROM seen2; 
 ELSE 
  SELECT * FROM seen2 WHERE seen2.seen_viewers = cmntViewID GROUP BY
seen2.seen_viewers; 
  SELECT seen_ID INTO viewCNT FROM seen2 WHERE seen2.seen_viewers =
cmntViewID GROUP BY seen2.seen_viewers; 
 END IF; 
CLOSE cur1; 
END$$ 

DELIMITER ;

这就是我调用我的程序示例的方式

CALL seen_table (',',2995,'356',@count); 

Answer 1

NULL 算作 1，但据我所知，它违反了 ANSI 标准:如果没有结果，也不应该返回 NULL。

为了获取从 MySQL 中的过程(或任何结果集)返回的行计数，有信息函数。

我不知道你想返回哪个选择的计数，所以只是给你一个例子:

{your procedure before this part}
IF cmntViewID = "*" THEN 
  SELECT SQL_CALC_FOUND_ROWS * FROM seen2 GROUP BY seen2.seen_viewers; 
  SELECT COUNT(*) INTO viewCNT FROM seen2; 
 ELSE 
  SELECT * FROM seen2 WHERE seen2.seen_viewers = cmntViewID GROUP BY
seen2.seen_viewers; 
  SELECT seen_ID INTO viewCNT FROM seen2 WHERE seen2.seen_viewers =
cmntViewID GROUP BY seen2.seen_viewers; 
 END IF;
{your procedure after this part}

然后执行:

CALL seen_table();
SELECT FOUND_ROWS();

这将返回“SELECT SQL_CALC_FOUND_ROWS * FROM saw2 GROUP BY saw2.seen_viewers;”中的行数查询。