MySQL 无法正确获取 PROCEDURE 中的参数值

Question

在这个简单的 PROCEDURE ，我要执行INSERT方法如果 @user_id是 NULL或通过 NULL 。但低于 INSERT 的 sql 命令无法正常工作:

Mysql 错误:

The following query has failed: "CALL `tsmsp_post_users`(@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); "

MySQL said: #1048 - Column 'username' cannot be null

执行程序

SET @p0 = '';

SET @p1 = 'qweqweqw';

SET @p2 = '';

SET @p4 = '';

SET @p5 = '';

SET @p6 = '';

SET @p7 = '';

SET @p8 = '';

CALL `tsmsp_post_users` (
      @p0 , @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8
);

我的程序:

CREATE DEFINER=`root`@`localhost` 
    PROCEDURE `tsmsp_post_users`
      (IN `user_id` INT(5), IN `username` VARCHAR(30) CHARSET utf8, IN `password`  VARCHAR(50) CHARSET utf8, IN `user_type` ENUM('موظفی','ساعتی') CHARSET utf8, IN `active` BOOLEAN, IN `created_at` DATETIME, IN `updated_at` DATETIME, IN `edited_by` INT(5), IN `start_work` DATETIME)
    DETERMINISTIC
    COMMENT 'TEST'
BEGIN

DECLARE action CHAR(10);

IF @user_id = NULL THEN
    SET action = "INSERT";
ELSE        
        SET action = "UPDATE";
END IF;
        IF action = "INSERT"  THEN
                BEGIN
                    SELECT 'INSERT';
                        INSERT INTO `users` (
                                        `user_id` ,
                                        `username` ,
                                        `password` ,
                                        `user_type` ,
                                        `active` ,
                                        `created_at` ,
                                        `updated_at` ,
                                        `edited_by` ,
                                        `start_work`
                                        )
            VALUES (
                                        @user_id, 
                                        @username,
                                        @password,
                                        @user_type,
                                        @active,
                                        @created_at,
                                        @updated_at,
                                        @edited_by,
                                        @start_work
                                     ) ;
                END;
        ELSE
                BEGIN
                        SELECT 'UPDATE';
                        SELECT * from users;
                END;
        END IF;
END

这是什么问题以及如何解决？谢谢

Answer 1

您可以通过将@参数更改为@p0、@p1、@p2...来更改插入查询，以正确映射参数，您的过程将正确执行