MySQL存储过程没有值检索

Question

我已经将一个存储过程从 Sql Server 迁移到 Mysql，并且我创建了 phpmyadmin 例程，语法没问题，但是当我执行存储过程时，没有返回任何值。

DROP PROCEDURE `sp_buscar`; CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_buscar`(IN `@buscar` VARCHAR(50) CHARSET utf8) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER

SELECT idColono AS Folio, nombreColono as Nombre, CONCAT(apellidoP ,' ', apellidoM) as Apellido,  CONCAT(domicilio,'  ', numero) as Dirección,  numeroTel AS Telefono 
FROM cColonos 
WHERE domicilio LIKE CONCAT (@buscar, '%')

在 Sql Server 中它可以正常工作。

CREATE PROC sp_buscar
@buscar VARCHAR(50)
AS
SELECT idColono AS Folio, nombreColono as Nombre, apellidoP +' '+ apellidoM as Apellido,  domicilio+'  '+ numero as Dirección,  numeroTel AS Telefono
FROM cColonos
WHERE domicilio LIKE  @buscar + '%'

怎么了？

Answer 1

尝试删除 concat 之后的空格:

SELECT idColono AS Folio, nombreColono as Nombre,
       CONCAT(apellidoP ,' ', apellidoM) as Apellido,
       CONCAT(domicilio,'  ', numero) as Dirección,
       numeroTel AS Telefono 
FROM cColonos 
WHERE domicilio LIKE CONCAT(@buscar, '%');

MySQL 对函数名后的空格有解析问题。

这个相当不寻常的限制实际上是 documented :

Note

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

并且有一个允许空格的系统参数。