mysql - 如何修复过程 SQL 中的错误？

Question

有一个hranimka，创建的时候出现了错误。也许是被...击中的她

存储过程:

   CREATE PROCEDURE insert_log(

  IN LogType INT, 
  IN LogIdNote INT,
  IN LogName VARCHAR, 
  IN LogTime TIMESTAMP, 
  IN logTypeCategory INT, 
  IN LogIdUser INT) 

begin

INSERT INTO log (LogType, 
                 LogIdNote, 
                 LogName, 
                 LogTime, 
                 logTypeCategory, 
                 LogIdUser, 
                 LogTypeUser, 
                 LogUrl)
SELECT LogType, LogIdNote, LogName, LogTime, logTypeCategory, LogIdUser, url.URLCategorysubscribetotype, u.UsersTypeAccount FROM users u LEFT JOIN categorysubscribetotype url ON url.CategoryTypeCategorysubscribetotype = LogType WHERE u.idUsers = LogIdUser;

end //

错误:

1064 - 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 'INT LogType, INT LogIdNote, VARCHAR LogName, TIMESTAMP LogTime, I' at line 3

我尝试只更改参数的数据类型。

我想，下一个代码会给我一个很好的结果，但我需要将 SELECT 查询的结果保存在变量中，并将其插入到查询插入中:

DELIMITER |

CREATE PROCEDURE insert_log(

  IN pLogType INT, 
  IN pLogIdNote INT,
  IN pLogName VARCHAR(150), 
  IN pLogTime TIMESTAMP, 
  IN plogTypeCategory INT, 
  IN pLogIdUser INT) 

BEGIN

DECLARE user_type INT DEFAULT 0;
DECLARE url VARCHAR(250) DEFAULT;

SET user_type = (SELECT UsersTypeAccount FROM users WHERE idUsers = pLogIdUser);
SET url = (SELECT URLCategorysubscribetotype FROM categorysubscribetotype WHERE CategoryTypeCategorysubscribetotype = pLogType);

INSERT INTO log (pLogType, 
                 pLogIdNote, 
                 pLogName, 
                 pLogTime, 
                 plogTypeCategory, 
                 pLogIdUser, 
                 pLogTypeUser, 
                 pLogUrl)
VALUES (
                 LogType, 
                 LogIdNote, 
                 LogName, 
                 LogTime, 
                 logTypeCategory, 
                 LogIdUser, 
                 user_type, 
                 url
);

END |

delimiter ;

Answer 1

您的问题在这里:

INSERT INTO log (pLogType, //wrong!
                 pLogIdNote, 
                 pLogName, 
                 pLogTime, 
                 plogTypeCategory, 
                 pLogIdUser, 
                 pLogTypeUser, 
                 pLogUrl)

您已将参数用作列，而它们应该是 VALUES 试试这个查询

DELIMITER //
 CREATE PROCEDURE insert_log(
    IN pLogType INT, 
    IN pLogIdNote INT,
    IN pLogName VARCHAR(150), 
    IN pLogTime TIMESTAMP, 
    IN plogTypeCategory INT, 
    IN pLogIdUser INT) 

   BEGIN

    DECLARE user_type INT DEFAULT 0;
    DECLARE url VARCHAR(250) DEFAULT;

    SET user_type = (
                        SELECT UsersTypeAccount 
                        FROM users 
                        WHERE idUsers = pLogIdUser
                    );
    SET url = (
                        SELECT URLCategorysubscribetotype 
                        FROM categorysubscribetotype 
                        WHERE CategoryTypeCategorysubscribetotype = pLogType
               );

    INSERT INTO log (
                     `LogType`, 
                     `LogIdNote`, 
                     `LogName`, 
                     `LogTime`, 
                     `logTypeCategory`, 
                     `LogIdUser`, 
                     `LogIdUserType`, /*I added this*/
                     `LogIdUrl`,     /*this one too */
    )VALUES (
                     pLogType, 
                     pLogIdNote, 
                     pLogName, 
                     pLogTime, 
                     plogTypeCategory,  
                     pLogIdUser,
                     user_type, 
                     url
    );

   END //
 DELIMITER ;

请注意您需要调整此存储过程，几乎没有错误。例如 pLogTypeUser 和 pLogUrl 是未定义的，我在需要更改列名的地方添加了注释。