MYSQL 用存储过程建表 参数作为表名

Question

CREATE PROCEDURE ct_zhiyawubiao(jx_id INT,j_id INT)
BEGIN
SET @sql='CREATE TABLE  ct_'+jx_id+j_id+'(
z_id int primary key auto_increment,
h_id int,
j_id int,
h_hph VARCHAR(50),
jx_id int,
carid int,
jx_name VARCHAR(50),
z_hkje DOUBLE,
z_lrsq date,
j_jrjg VARCHAR(50),
z_cjh VARCHAR(50),
p_id int,
p_ppmc VARCHAR(50),
z_clzt int,
z_hgzh VARCHAR(50),
z_hgzrq date,
z_fdjh VARCHAR(50),
z_cx VARCHAR(50),
z_ys VARCHAR(50),
z_pl VARCHAR(50),
z_clje double,
z_ysh VARCHAR(50),
z_yssl int,
z_rkrq date,
z_ckrq date,
z_ykrq date,
z_bz VARCHAR(50),
z_zhczz VARCHAR(50),
z_zhxgsj date,
z_sczt int,
z_by1 VARCHAR(50),
z_by2 VARCHAR(50),
z_by3 VARCHAR(50),
z_by4 VARCHAR(50),
z_by5 VARCHAR(50)
);';
PREPARE exctable FROM @sql;
EXECUTE exctable;
END;
这样写  可是调用的时候报错
call ct_zhiyawubiao(1,3);

Answer 1

修改一下sql语句，字符串拼接用contcat函数
CREATE PROCEDURE ct_zhiyawubiao(jx_id INT,j_id INT)
BEGIN
SET @sql=CONCAT('CREATE TABLE ct_',(jx_id+j_id),'(
z_id int primary key auto_increment,
h_id int,
j_id int,
h_hph VARCHAR(50),
jx_id int,
carid int,
jx_name VARCHAR(50),
z_hkje DOUBLE,
z_lrsq date,
j_jrjg VARCHAR(50),
z_cjh VARCHAR(50),
p_id int,
p_ppmc VARCHAR(50),
z_clzt int,
z_hgzh VARCHAR(50),
z_hgzrq date,
z_fdjh VARCHAR(50),
z_cx VARCHAR(50),
z_ys VARCHAR(50),
z_pl VARCHAR(50),
z_clje double,
z_ysh VARCHAR(50),
z_yssl int,
z_rkrq date,
z_ckrq date,
z_ykrq date,
z_bz VARCHAR(50),
z_zhczz VARCHAR(50),
z_zhxgsj date,
z_sczt int,
z_by1 VARCHAR(50),
z_by2 VARCHAR(50),
z_by3 VARCHAR(50),
z_by4 VARCHAR(50),
z_by5 VARCHAR(50)
);');
PREPARE exctable FROM @sql;
EXECUTE exctable;
END;

Answer 2

begin
set @sql1=concat('CREATE TABLE ct_',a_id,b_id);
SET @sql=concat(@sql1,'(
z_id int primary key auto_increment,
h_id int,
j_id int,
h_hph VARCHAR(50),
jx_id int,
carid int,
jx_name VARCHAR(50),
z_hkje DOUBLE,
z_lrsq date,
j_jrjg VARCHAR(50),
z_cjh VARCHAR(50),
p_id int,
p_ppmc VARCHAR(50),
z_clzt int,
z_hgzh VARCHAR(50),
z_hgzrq date,
z_fdjh VARCHAR(50),
z_cx VARCHAR(50),
z_ys VARCHAR(50),
z_pl VARCHAR(50),
z_clje double,
z_ysh VARCHAR(50),
z_yssl int,
z_rkrq date,
z_ckrq date,
z_ykrq date,
z_bz VARCHAR(50),
z_zhczz VARCHAR(50),
z_zhxgsj date,
z_sczt int,
z_by1 VARCHAR(50),
z_by2 VARCHAR(50),
z_by3 VARCHAR(50),
z_by4 VARCHAR(50),
z_by5 VARCHAR(50)
);');
PREPARE exctable FROM @sql;
EXECUTE exctable;
end楼上说的没错，存储过程中拼接 sql 要使用 concat函数
另外，如果报出 thread stack不够那个错的话，请修改 mysql 工作路径下的 my.ini文件，将 thread_stack调大（比如调成512k），重启mysql服务就可以。
已经调通，

Answer 3

我也碰到这样的问题，我用的是5.7
我就是把表名作为参数传入
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedurename`(tablename varchar(45) )CREATE TABLE `my`.tablename (然后表名就一直叫tablenamecall procedurename("mytablename");