Mysql count（*）让我失望 [英] Mysql count(*) is failing me

问题描述

我在mysql DB中有表作者和发布者。它们都有ISBN

列的共同点。然后我发出一个电话（通过php）到存储过程

它包含以下代码：

  BEGIN  
  DECLARE  count  int ; 
 
  SELECT  count（*） FROM  
（
  SELECT   distinct  
 table_name 
  FROM  information_schema.columns 
  WHERE  table_name  IN （'  authors'，'publishers'）
  AND  column_name = '  ISBN' 
 和 table_schema = '  myDB' 
）any_alias 
  INTO  count; 
  SELECT  count  INTO  msg;  / *  仅用于调试计数器* /  
 结束 

基本上，我希望上面的代码片段返回2作为计数表的数量

有共同的ISBN列（反映在count变量中）。不幸的是，

计数总是返回0，尽管多次检查ISBN列是

那里（使用phpmyadmin）。为什么count（*）表现得很奇怪？或者是否有我必须设置的
a特殊.ini配置？



这是我的测试驱动程序：

  if （！$ this-> MakeProcedure（   StupidProcedure，  OUT msg VARCHAR （25）））{
 echo  无法创建程序 ; 
} 
 
  if （！$ this-> db-> query（  SET @msg =''）||！$ this-> db-> query（  CALL StupidProcedure（@msg）））{
 echo  调用StupidProcedure失败：; 
} 其他 {
 $ res = $ this-> db-> query（   SELECT @msg as _p_out）; 
  $ row  = $ res-> fetch（）; 
 echo  $ row  ['  _ p_out']. 计数列; 
} 

我们将非常感谢任何帮助。

解决方案

this-> MakeProcedure（ StupidProcedure， OUT msg VARCHAR（25）））{
echo 无法创建程序;
}

if （！

this-> db-> query （ SET @msg =''）||！

< blockquote> this-> db-> query（ CALL StupidProcedure（@msg））） {
echo 调用StupidProcedure失败：;
} 其他 {

I have tables authors and publishers within mysql DB. They both have ISBN
Column in common. I then issued a call (through php) to a stored procedure
That contains the following code:

BEGIN 	
DECLARE count int;
                    
                    SELECT count (*) FROM 
                                    (
                                       SELECT distinct
                                       table_name
                                       FROM information_schema.columns
                                       WHERE table_name IN ('authors', ‘publishers’)
                                       AND column_name = 'ISBN'
                                       and table_schema = 'myDB'
                                    )any_alias
                    INTO count;
                    SELECT count INTO msg;      /*just to debug counter*/
END 

Basically, I want the above snippet to return 2 as counted number of tables that
have ISBN column in common (to reflect in count variable). Unfortunately,
count is always returning 0 in spite of multiple - checking that ISBN column is
there (with phpmyadmin). Why is count(*) behaving strangely ? or is there
a special .ini configuration that I must set ?

here is my test driver:

if(!$this->MakeProcedure("StupidProcedure", "OUT msg VARCHAR(25)" )){
            echo "could not create procedure";
  }

if(!$this->db->query("SET @msg = ''") || !$this->db->query("CALL StupidProcedure(@msg)")){
          echo "CALL to StupidProcedure  failed:";
}  else   {
    $res= $this->db->query("SELECT @msg as _p_out");
     $row = $res->fetch();
                echo $row['_p_out']." counted columns ";
}        

Any help will be greatly appreciated.

解决方案

this->MakeProcedure("StupidProcedure", "OUT msg VARCHAR(25)" )){ echo "could not create procedure"; } if(!

this->db->query("SET @msg = ''") || !

this->db->query("CALL StupidProcedure(@msg)")){ echo "CALL to StupidProcedure failed:"; } else {

这篇关于Mysql count（*）让我失望的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！