我想调用mysql存储过程建表,表名通过in参数来传进。
CREATE PROCEDURE friendList (in tname varchar(20))
BEGIN
SET @sqlstr =CONCAT('CREATE TABLE if not exists ',
tname,
'(
userid int unsigned,
phone VARCHAR(20) NOT NULL,
name VARCHAR(30) NOT NULL,
birth int,
nick VARCHAR(15),
impression varchar(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8') ;
prepare stmt from @sqlstr;
execute stmt;
END;
在windows 的cmd下执行一直报错。 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
原来,在cmd下执行的mysql,其;相当于commit,所以会一直报这个错。
应更改语句结束符号,本实例将语句结束符更改为“//”。
于是:
delimiter //
CREATE PROCEDURE friendList (in tname varchar(20))
BEGIN
SET @sqlstr =CONCAT('CREATE TABLE if not exists ',
tname,
'(
userid int unsigned,
phone VARCHAR(20) NOT NULL,
name VARCHAR(30) NOT NULL,
birth int,
nick VARCHAR(15),
impression varchar(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8') ;
prepare stmt from @sqlstr;
execute stmt;
END;
//
CREATE PROCEDURE friendList (in tname varchar(20))
BEGIN
SET @sqlstr =CONCAT('CREATE TABLE if not exists ',
tname,
'(
userid int unsigned,
phone VARCHAR(20) NOT NULL,
name VARCHAR(30) NOT NULL,
birth int,
nick VARCHAR(15),
impression varchar(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8') ;
prepare stmt from @sqlstr;
execute stmt;
END;
在windows 的cmd下执行一直报错。 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
原来,在cmd下执行的mysql,其;相当于commit,所以会一直报这个错。
应更改语句结束符号,本实例将语句结束符更改为“//”。
于是:
delimiter //
CREATE PROCEDURE friendList (in tname varchar(20))
BEGIN
SET @sqlstr =CONCAT('CREATE TABLE if not exists ',
tname,
'(
userid int unsigned,
phone VARCHAR(20) NOT NULL,
name VARCHAR(30) NOT NULL,
birth int,
nick VARCHAR(15),
impression varchar(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8') ;
prepare stmt from @sqlstr;
execute stmt;
END;
//