问题描述

MySQL:为什么这样的小改动会造成存储过程错误呢？

 DELIMITER?$$??????????????????
USE?mysql$$
DROP?PROCEDURE?IF?EXISTS?demo$$
CREATE?PROCEDURE?demo(_id?INT,?_content?VARCHAR(64))????
BEGIN??????????????
    SET?@id?=?_id;
    SET?@content?=?_content;
??????????????????????????????????????????????????????????????????????????????????????
    PREPARE?stmt?FROM?"INSERT?INTO?blog?(id,?content)?VALUES(?,??)";
    EXECUTE?stmt?USING?@id,?@content;
    DEALLOCATE?PREPARE?stmt;
END$$
DELIMITER?;
DELIMITER?$$

如果改写成如下就会报错：

 DELIMITER $$

USE mysql$$
DROP PROCEDURE IF EXISTS demo$$
CREATE PROCEDURE demo(_id INT, _content VARCHAR(64))
BEGIN
    -- set @id = _id;
    -- set @content = _content;

    PREPARE stmt FROM "INSERT INTO blog (id, content) VALUES(?, ?)";
    EXECUTE stmt USING _id, _content;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

 错误代码： 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 '_id, _content;
    DEALLOCATE PREPARE stmt;
END' at line 7

解决方案

双-- 就是将其注释掉了 ，所以数据库缺少'_id, _content;

解决方案二：

MySQL错误，存储过程死循环错误
Mysql 存储过程 不存在错误

解决方案三：

查找官方问道找到答案了，USING只能接user variable

http://dev.mysql.com/doc/refman/5.6/en/execute.html