mysql - 存储过程中的错误 - 列计数与值不匹配

Question

我创建了一个存储过程:

DELIMITER $$
DROP PROCEDURE IF EXISTS `zero`.`sp_for_insert_into_account_db`$$
CREATE PROCEDURE `zero`.`sp_for_insert_into_account_db` (usr_key char(6),usr_name varchar(15),usr_password varchar(15),OUT output_message INT)

BEGIN

DECLARE no_of_row INT;    
SELECT COUNT(*) INTO no_of_row from account_db;  
IF no_of_row < 4 THEN

    SET @s = CONCAT('insert into account_db (USR_KEY,USR_NAME,USR_PWD) VALUES (',usr_key,usr_name,usr_password,')');

    PREPARE stmt FROM @s;  
    EXECUTE stmt;    
    DEALLOCATE PREPARE stmt;    
    SET output_message=1;    
ELSE    
    SET output_message=0;       
END IF;
END$$

DELIMITER ;

我用查询来调用它

 call sp_for_insert_into_account_db('a','b','c',@output_ message);

错误如下:

Column count does not match value...

我正在传递 4 个参数...

为什么会出现这个错误？

我已经检查过这个语法(默认参数是 IN 类型)

sp_for_insert_into_account_db(IN usr_key char(6), 
                              IN usr_name varchar(15), 
                              IN usr_password varchar(15),
                              OUT output_message INT)

Answer 1

问题也在这里:

SET @s = CONCAT('insert into account_db (USR_KEY,USR_NAME,USR_PWD) VALUES (',usr_key,usr_name,usr_password,')');

您尝试插入 3 个值，连接返回 1

改用这个:

SET @s = CONCAT('insert into account_db (USR_KEY,USR_NAME,USR_PWD) VALUES (\'',usr_key,'\',\'',usr_name,'\',\'',usr_password,'\')');