错误代码1064将数据插入mysql

aiazj4mn  于 2021-06-23  发布在  Mysql
关注(0)|答案(1)|浏览(385)

我创建了以下存储过程以将数据插入数据库:

CREATE DEFINER=`PotatoHead`@`%` PROCEDURE `InsertIntoTable`(in    
            tablename varchar(45),in ID varchar(45),in Project 
            varchar(45),in Variant varchar(45),
            in ReleaseVersion varchar(45) ,
            in TestBenchID varchar(45) ,
            in TestCaseID int(11) ,
            in TestCaseNamespace varchar(100) ,
            in TestCaseName varchar(45),
            in TestCaseDomain varchar(45) ,
            in TestType varchar(45) ,
            in HardwareVersion varchar(4000) ,
            in SoftwareVersion varchar(4000),
            in Result varchar(45) ,
            in Comment varchar(4000),
            in Duration varchar(45) ,
            in StartTime varchar(45) ,
            in EndTime varchar(45)  )
BEGIN

  SET @sql = CONCAT('Insert into ', tablename );
  SET @sql = CONCAT('ID,Project,Variant,');
  SET @sql = CONCAT('ReleaseVersion,');
  SET @sql = CONCAT('TestBenchID ,');
  SET @sql = CONCAT('TestCaseID ,');
  SET @sql = CONCAT('TestCaseNamespace ,');
  SET @sql = CONCAT('TestCaseName ,');
  SET @sql = CONCAT('TestCaseDomain ,');
  SET @sql = CONCAT('TestType ,');
  SET @sql = CONCAT('HardwareVersion ,');
  SET @sql = CONCAT('SoftwareVersion ,');
  SET @sql = CONCAT('Result ,');
  SET @sql = CONCAT('Comment ,');
  SET @sql = CONCAT('Duration ,');
  SET @sql = CONCAT('StartTime ,');
  SET @sql = CONCAT('EndTime) VALUES (');
    SET @sql = CONCAT(ID,',',Project,',',Variant,',',
    ReleaseVersion,',',
    TestBenchID,',',
    TestCaseID,',',
    TestCaseNamespace ,',',
    TestCaseName ,',',
    TestCaseDomain ,',',
    TestType ,',',
    HardwareVersion ,',',
    SoftwareVersion ,',',
    Result ,',',
    Comment ,',',
    Duration ,',',
    StartTime ,',',
    EndTime,');');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

 END

当我尝试用以下查询调用此函数时:

Call InsertIntoTable('oablp1992','id','project','variant',
    'releaseversion',
    'testbenchid',
    1,
    'testcasenamespace',
    'testcasename',
    'Domain',
    'type',
    'hardware',
    'software',
    'result',
    'comment',
    'Duration',
    'starttime',
    'endtime');

Insert into oablp1992(ID,Project,Variant,
    ReleaseVersion,
        TestBenchID ,
    TestCaseID ,
        TestCaseNamespace ,
    TestCaseName ,
    TestCaseDomain ,
    TestType ,
        HardwareVersion ,
        SoftwareVersion ,
    Result ,
    Comment ,
    Duration ,
    StartTime ,
    EndTime) VALUES('id','project','variant',
    'releaseversion',
    'testbenchid',
        '1',
        'testcasenamespace',
        'testcasename',
        'Domain',
        'type',
        'hardware',
        'software',
    'result',
        'comment',
        'Duration',
        'starttime',
        'endtime');

我收到以下错误:
错误代码:1064。sql语法有错误;请查看与您的mysql服务器版本对应的手册,在第1行的“id,project,variant,releaseversion,testbenchid,1,testcasenamespace,testcasename,d”附近找到正确的语法
如何解决这个问题?

7uhlpewt

7uhlpewt1#

如果您使用一个命名约定,将参数和列名清楚地区分开来,那么就更清楚了。但也许这就是你想要的。注意ascii字符39(单引号)的使用。

drop procedure if exists `InsertIntoTable`;
delimiter $$
CREATE PROCEDURE `InsertIntoTable`(in    
            tablename varchar(45),
                in ID varchar(45),
                in Project varchar(45),
                in Variant varchar(45),
            in ReleaseVersion varchar(45) ,
            in TestBenchID varchar(45) ,
            in TestCaseID int(11) ,
            in TestCaseNamespace varchar(100) ,
            in TestCaseName varchar(45),
            in TestCaseDomain varchar(45) ,
            in TestType varchar(45) ,
            in HardwareVersion varchar(4000) ,
            in SoftwareVersion varchar(4000),
            in Result varchar(45) ,
            in inComment varchar(4000),
            in Duration varchar(45) ,
            in StartTime varchar(45) ,
            in EndTime varchar(45)  )
BEGIN

  SET @sql = CONCAT('Insert into ', tablename ,
  '(ID,Project,Variant,',
  'ReleaseVersion,',
  'TestBenchID,' ,
  'TestCaseID,' ,
    'TestCaseNamespace,' 
  'TestCaseName,' ,
  'TestCaseDomain,' ,
  'TestType,' ,
  'HardwareVersion,' ,
  'SoftwareVersion,' ,
  'Result,' ,
  CHAR(96),'Comment',CHAR(96),',' ,
  'Duration,' ,
  'StartTime,' ,
  'EndTime',') values ('
  );

    SET @sql = CONCAT(@SQL,
     char(39),ID,char(39),',',
     char(39),Project,char(39),',',
     char(39),Variant,char(39),',',
    char(39),ReleaseVersion,char(39),',',
    char(39),TestBenchID,char(39),',',
    char(39),TestCaseID,char(39),',',
    char(39),TestCaseNamespace ,char(39),',',
    char(39),TestCaseName ,char(39),',',
    char(39),TestCaseDomain ,char(39),',',
    char(39),TestType ,char(39),',',
    char(39),HardwareVersion ,char(39),',',
    char(39),SoftwareVersion ,char(39),',',
    char(39),Result ,char(39),',',
    char(39),inComment ,char(39),',',
    char(39),Duration ,char(39),',',
    char(39),StartTime ,char(39),',',
    char(39),EndTime,char(39),');');

   select @sql;

# PREPARE s FROM @sql;

# EXECUTE s;

# DEALLOCATE PREPARE s;

 END $$
 delimiter ;

 Call InsertIntoTable('oablp1992','id','project','variant',
    'releaseversion',
    'testbenchid',
    1,
    'testcasenamespace',
    'testcasename',
    'Domain',
    'type',
    'hardware',
    'software',
    'result',
    'comment',
    'Duration',
    'starttime',
    'endtime');

它生成以下sql语句

Insert into oablp1992(
ID,Project,Variant,ReleaseVersion,TestBenchID,TestCaseID,TestCaseNamespace,
TestCaseName,TestCaseDomain,TestType,HardwareVersion,SoftwareVersion,
Result,`Comment`,Duration,StartTime,EndTime) 
values (
'id','project','variant','releaseversion','testbenchid','1','testcasenamespace',
'testcasename','Domain','type','hardware',
'software','result','comment','Duration','starttime','endtime');

当您希望将其传递给sql时,不要忘记删除注解掉的行。

相关问题