mysql - 在 Mysql 中创建带有 BLOB 变量的存储过程

Question

这是在早期版本的 mysql 上工作的存储过程。现在，我有 Mysql 5.5.19.0，我不能创建像这样的存储过程

DELIMITER $$

USE `XXX`$$

DROP PROCEDURE IF EXISTS `current_to_prev`$$

CREATE DEFINER=`user`@`localhost` PROCEDURE `current_to_prev`(
IN _USERNAME VARCHAR(45),
IN _FILENAME VARCHAR(45),
IN _TYPE INT
)
BEGIN
    DECLARE _uid INT;
    DECLARE _fdata BLOB;
    DECLARE _fname VARCHAR(45);
    DECLARE _type VARCHAR(45);
    SET _uid = (SELECT userid FROM USER WHERE username = _USERNAME);
    SET _fdata = (SELECT FileData FROM userfiles_current WHERE userid = _uid AND filename = _FILENAME);
    SET _fname = (SELECT FileName FROM userfiles_current WHERE userid = _uid AND filename = _FILENAME);
    SET _type = (SELECT TYPE FROM userfiles_current WHERE userid = _uid AND TYPE = _TYPE);
    UPDATE userfiles_prev SET FileData = _fdata, FileName = _fname, TYPE = _type WHERE userid = _uid AND filename = _FILENAME;
END$$

DELIMITER ;

我收到以下错误:

Query : CREATE DEFINER=<code>user</code>@<code>localhost</code> PROCEDURE <code>current_to_prev</code>( IN _USERNAME VARCHAR(45), IN _FILENAME VARCHAR(45), IN _TYPE INT ...





Error Code : 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 '_FILENAME VARCHAR(45),
IN _TYPE INT
)
BEGIN
DECLARE _uid INT;
DECLARE _fdata B' at line 3


如您所见，MySQL 不能接受 BLOB 变量。我该如何解决这个问题？

提前致谢!

Answer 1

MySQL 接受 blob 类型的参数、变量等。

参数 IN _FILENAME 中的错误 - 它是 reserved word .将其更改为其他内容，您会没事的。