mysql - 运行sql语句时出现问题

Question

我正在运行此存储过程并从 Mysql 收到错误

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_bk_mailsettings_insert`(OUT _mID bigint,
    _username varchar(255),
    _msendusername varchar(255),
    _alais varchar(255),
    _password varchar(255),
    _MailServer varchar(255),
    _Port varchar(100),
    _err varchar(255))
BEGIN
INSERT INTO `bk_mailsettings`(`username`,`msendusername`,`alais`,`password`,`MailServer`,`Port`,`err`,`active`)
VALUES (_username,_msendusername,_alais,_password,_MailServer,_Port,_err,0);

select last_insert_id() into _mID;
END

错误是:

Error Executing Database Query. OUT or INOUT argument 1 for routine bakeryshop.usp_bk_mailsettings_insert is not a variable or NEW pseudo-variable in BEFORE trigger

我很困惑出了什么问题，一切似乎都很好，我检查了很多论坛和帖子，但没有关于如何解决此问题的有效答案。

Answer 1

使用此存储过程:

DELIMITER //

DROP PROCEDURE IF EXISTS usp_bk_mailsettings_insert//

CREATE PROCEDURE usp_bk_mailsettings_insert (
    OUT _mID bigint,
    _username varchar(255),
    _msendusername varchar(255),
    _alais varchar(255),
    _password varchar(255),
    _MailServer varchar(255),
    _Port varchar(100),
    _err varchar(255)
    )
BEGIN
    INSERT INTO `bk_mailsettings`(`username`,`msendusername`,`alais`,`password`,`MailServer`,`Port`,`err`,`active`)
    VALUES (_username,_msendusername,_alais,_password,_MailServer,_Port,_err,0);

    SELECT last_insert_id() INTO _mID;
END//

DELIMITER ;

您可以调用:

CALL usp_bk_mailsettings_insert(@out, arg1, arg2, arg3, arg4, arg5, arg6, arg7)

然后结果@out将可用:

SELECT @out;

->

mysql> SELECT @out;
+------+
| @out |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

如果希望存储过程直接返回结果，则需要使用:

DELIMITER //

DROP PROCEDURE IF EXISTS usp_bk_mailsettings_insert//

CREATE PROCEDURE usp_bk_mailsettings_insert (
    _username varchar(255),
    _msendusername varchar(255),
    _alais varchar(255),
    _password varchar(255),
    _MailServer varchar(255),
    _Port varchar(100),
    _err varchar(255)
    )
BEGIN
    INSERT INTO `bk_mailsettings`(`username`,`msendusername`,`alais`,`password`,`MailServer`,`Port`,`err`,`active`)
    VALUES (_username,_msendusername,_alais,_password,_MailServer,_Port,_err,0);

    SELECT last_insert_id();
END//

DELIMITER ;

然后调用它:

CALL usp_bk_mailsettings_insert(arg1, arg2, arg3, arg4, arg5, arg6, arg7)

->

+------------------+
| last_insert_id() |
+------------------+
|              123 |
+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)