错误代码：mysql 中的 1064
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7926082/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error Code: 1064 in mysql
提问by Rehme
I created a procedure that is supposed to do some operation but every time I call it, mysql comes out with an error, which I have not clue what it means. I have tried to understand it in vain, here 's the table structure which the stored procedure where made to do an operation on it:
我创建了一个应该执行某些操作的过程，但是每次调用它时，mysql 都会出现错误，我不知道它的含义。我试图理解它是徒劳的，这是存储过程对其进行操作的表结构：
CREATE TABLE `recruitment`.`job_seeker` (
`user_id` INT Null ,
`fname` VARCHAR(45) Null ,
`lname` VARCHAR(45) Null ,
`mname` VARCHAR(45) Null ,
`gender` VARCHAR(10) Null ,
`dob` DATE Null ,
`marital_status` VARCHAR(45) Null ,
`address` VARCHAR(45) Null ,
`city` VARCHAR(45) Null ,
`nationality` VARCHAR(45) Null ,
`phone` VARCHAR(45) Null ,
`mobile` VARCHAR(45) Null ,
`degree_id` INT Null ,
`education` VARCHAR(100) Null ,
`experience` VARCHAR(250) Null ,
`other` VARCHAR(250) Null ,
`job_target` VARCHAR(250) Null ,
PRIMARY KEY (`user_id`) ,
INDEX `user_id` (`user_id` ASC) ,
INDEX `degree_id` (`degree_id` ASC) ,
CONSTRAINT `user_id`
FOREIGN KEY (`user_id` )
REFERENCES `recruitment`.`user_authentication` (`user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `degree_id`
FOREIGN KEY (`degree_id` )
REFERENCES `recruitment`.`degree` (`degree_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
Here's the stored procedure:
这是存储过程：
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createSeekerProfile`(in userName varchar(45),
in fn varchar(45),in mn varchar(45), in ln varchar(45),
in gender varchar(6),in nationality varchar(45),
in ad varchar(45),in city varchar(45),in phone varchar(15),in mob varchar(15),
in maritalStatus varchar(45), in degId int, in educ varchar(100),
in exper varchar(250), in other varchar(250),
in dob date,in jtarg varchar(250))
begin
declare returned_ID int;
set @dyn_que = CONCAT('select user_id into @returned_ID
from user_authentication where user_name = ? ');
prepare s1 from @dyn_que ;
set @usn = userName;
execute s1 using @usn ;
set @dyn_update =CONCAT('update job_seeker set fname =',fn,', lname = ',ln,' ,mname = ',mn,' ,
nationality =',nationality,',address =',ad,',city =',city,',phone=',phone,',
mobile =',mob,', gender = ',gender,',other =',other,',
degree_id =',degId,', job_target=',jtarg,', dob =',dob,', education =',educ,',
experience=',exper,', marital_status=',maritalStatus,' where user_id =@returned_ID');
prepare s2 from @dyn_update;
execute s2;
end
Whenever I call the procedure through:
每当我通过以下方式调用程序时：
call createSeekerProfile('realsilhouette','robert','marie','david','male'
,'earthal','an address here','capital of earth','012178152',
'1111111111','single',2,'engineering','looking forward','determined',
'2008-7-04','Oracle CEO')
I get an awful error which is:
我得到一个可怕的错误是：
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 'address here,
city =capital of earth,phone=012178152,
mobile =1111111111, gender' at line 2
However when I try execute the update statement manually which is inside stored procedure, it works out so well.
但是，当我尝试手动执行存储过程中的更新语句时，效果很好。
new Post : thanks god,finally I fixed the problem up, The problem came down to the order, all I did was just make the parameters in order, though update statement does not care about the order, as far as I know, I'm not sure, But what i am sure of is the stored procedure got created beautifully, here's the new code :
new Post : 感谢上帝，终于把问题解决了，问题归结为顺序，我所做的只是将参数按顺序排列，虽然更新语句不关心顺序，据我所知，我'我不确定，但我确信存储过程创建得很漂亮，这是新代码：
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createSeekerProfile`(
in un varchar(45),
in fn varchar(45),
in ln varchar(45),
in mn varchar(45),
in g varchar(10),
in dateOfBirth date,
in ms varchar(45),
in ad varchar(45),
in city varchar(45),
in nat varchar(45),
in ph varchar(45),
in mob varchar(45),
in degid int,
in educ varchar(100),
in exp varchar(250),
in other varchar(250),
in jtarg varchar(250))
begin
declare returned_ID int(11);
set @dyn_que = CONCAT('select user_id into @returned_ID from user_authentication
where user_name = ? ');
prepare s1 from @dyn_que ;
set @usn = un;
execute s1 using @usn ;
set @dyn_update =CONCAT('update job_seeker set fname
="',fn,'",lname="',ln,'",mname="',mn,'",lname ="',ln,'",
gender ="',g,'",dob="',dateOfBirth,'",marital_status="',ms,'",
address="',ad,'",city="',city,'",
nationality="',nat,'",phone="',ph,'",mobile="',mob,'",degree_id="',
degid,'",education="',educ,'",
experience="',exp,'",other="',other,'",job_target="',jtarg,'"
where user_id = @returned_ID');
prepare stm from @dyn_update;
execute stm;
end $$
many thanks
非常感谢
采纳答案by ravnur
回答by shreyas
For me adding character ` (GRAVE ACCENT) helped. It is not a single quote (')
对我来说，添加字符`（重音符号）有帮助。它不是单引号 (')
回答by Muhammad Siddique Awan
CREATE TABLE Order
(
Order_Id integer NOT NULL ,
Order_Time datetime NULL ,
Order_Status char(50) NULL ,
Customer_Id integer NOT NULL
)
