mysql - 存储过程和查询执行结果不同

Question

我正在尝试使用 Navicat 使用 mysql 执行存储过程，当我执行这个存储过程时，我得到 0 行，但是当我执行我想要的存储过程部分时，我得到 1 行，这就是我想要的。存储过程是这样的:

BEGIN
#Routine body goes here...

IF @autor=0 THEN

 SELECT DISTINCT bdocus.ano_pub
  FROM            bdocus INNER JOIN
                     descriptores_bdocus ON bdocus.nserie = descriptores_bdocus.nserie INNER JOIN
                     descriptores ON descriptores_bdocus.iddescriptor = descriptores.id 
  WHERE        (bdocus.descriptores LIKE '%Victimización%')AND(bdocus.ano_pub LIKE CONCAT(@anyo,'%')) AND (bdocus.codigo_area LIKE CONCAT(@tema,'%')) AND(bdocus.codigo_colectivo LIKE CONCAT(@colectivo,'%')) AND (descriptores.nombre = @descriptores)
 GROUP BY bdocus.ano_pub
 ORDER BY bdocus.ano_pub DESC;
ELSE
 SELECT DISTINCT bdocus.ano_pub
 FROM            bdocus INNER JOIN
                     descriptores_bdocus ON bdocus.nserie = descriptores_bdocus.nserie INNER JOIN
                     descriptores ON descriptores_bdocus.iddescriptor = descriptores.id INNER JOIN
                     autores_bdocus ON bdocus.nserie = autores_bdocus.nserie
  WHERE        (bdocus.descriptores LIKE '%Victimización%') AND (autores_bdocus.idautor = @autor) AND(bdocus.ano_pub LIKE CONCAT(@anyo,'%')) AND (bdocus.codigo_area LIKE CONCAT(@tema,'%')) AND(bdocus.codigo_colectivo LIKE CONCAT(@colectivo,'%')) AND (descriptores.nombre = @descriptores)
  GROUP BY bdocus.ano_pub
  ORDER BY bdocus.ano_pub DESC;
  END IF;

END

参数是这样的:

IN `@anyo` varchar(20),IN `@tema` varchar(20),IN `@autor` int,  
IN `@colectivo` varchar(30),IN `@descriptores` varchar(40)

我想要的值是@anyo='2013'、@tema='%'、@autor=44439 code>，对于 @colectivo='%' 和 @descriptores='Violencia Sex'。

当我运行存储过程的第二部分时，我的意思是:

SELECT DISTINCT bdocus.ano_pub
FROM            bdocus INNER JOIN
                     descriptores_bdocus ON bdocus.nserie = descriptores_bdocus.nserie INNER JOIN
                     descriptores ON descriptores_bdocus.iddescriptor = descriptores.id INNER JOIN
                     autores_bdocus ON bdocus.nserie = autores_bdocus.nserie
 WHERE        (bdocus.descriptores LIKE '%Victimización%') AND (autores_bdocus.idautor = 44439) AND(bdocus.ano_pub LIKE CONCAT('%','%')) AND (bdocus.codigo_area LIKE CONCAT('%','%')) AND(bdocus.codigo_colectivo LIKE CONCAT('%','%')) AND (descriptores.nombre = 'Violencia sexual')
GROUP BY bdocus.ano_pub
ORDER BY bdocus.ano_pub DESC;

结果我得到一行包含我想要的值。那么区别在哪里呢？提前致谢。

Answer 1

指出 9.4. User-Defined Variables 之间的区别很重要和常规参数13.1.15. CREATE PROCEDURE and CREATE FUNCTION Syntax ，是不同的变量。

在您的示例中，@anyo 可能是 NULL 并且 `@anyo` 被分配给 '2013' .

SQL Fiddle example