mysql - "COUNT (*)"附近有语法错误？

Question

我想做一个交互式存储过程我的意思是，执行后用户应该输入一个单词

我写了这个，但它不起作用..

DELIMITER $$

DROP PROCEDURE IF EXISTS ric_forn$$
CREATE PROCEDURE  ric_forn (IN nome_forn VARCHAR(100) , OUT msg VARCHAR(100))
BEGIN
DECLARE num_rec INT;

IF (nome_forn = '') THEN 
    SET msg = "Attenzione il nome inserito non è valido !";
END IF;

SELECT COUNT (*) INTO num_rec FROM Fornitori WHERE Des_Fornitore = nome_forn;

IF num_rec = 0 THEN
    SET msg = "Nessun record trovato !";

ELSE

SELECT Id_Fornitore,Des_Fornitore,Ind_Fornitore WHERE Des_Fornitore = nome_forn;
    SET msg = "Records trovati:";

END IF;

END$$

DELIMITER ;

我收到此错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) INTO num_rec FROM Fornitori WHERE Des_Fornitore = nome_forn;

Answer 1

COUNT 和 (*) 之间的空格很重要。你应该把它们放在一起，不要留空格。

SELECT COUNT(*) INTO ...

异常(exception)情况是SET SQL_MODE='IGNORE_SPACE'。阅读 Function Name Parsing and Resolution了解更多详情。

<小时/>

您的另一个错误是您忘记了其中一个查询中的 FROM 子句:

SELECT Id_Fornitore,Des_Fornitore,Ind_Fornitore WHERE Des_Fornitore = nome_forn;

应该是:

SELECT Id_Fornitore,Des_Fornitore,Ind_Fornitore FROM Fornitori
WHERE Des_Fornitore = nome_forn;