接上一篇:Centos7使用mysql5.7.20之存储过程创建动态表名-mysql创建动态表名
本次在实现表名动态生成的同时,为创建的表生成动态列名,并插入数据。
一、环境:
mysql5.7.20
二、需求
有如下表信息:
现需创建新表,将某一行数据中的stuname字段值作为表名,该行数据中的valueb,valuec,valued值作为新表名的列名,并作为列值插入表中。
新表的字段有:valueid,valueb对应的值,valueb对应的值,valueb对应的值;其中,valueid 主键,非空,自增。
举个栗子:第一行数据:t_stu_a1作为表名,该表包含以下四个字段:(valueid,b1,c1,d1),其中valueid自增,主键;b1字段值为b1,c1字段值为c1,d1字段值为d1。
三、实现
DROP PROCEDURE IF EXISTS pro_splitupqq;
CREATE PROCEDURE pro_splitupqq()
BEGIN
DECLARE s INT DEFAULT 0;
DECLARE col_a VARCHAR(255);
DECLARE col_b VARCHAR(255);
DECLARE col_c VARCHAR(255);
DECLARE col_d VARCHAR(255);
DECLARE helloset CURSOR FOR SELECT stuname,valueb,valuec,valued FROM testdes;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
OPEN helloset;
FETCH helloset INTO col_a,col_b,col_c,col_d;
WHILE s<>1 DO
SET @tmp_b=col_b,@tmp_c=col_c,@tmp_d=col_d;
set @preparable_stmt1=CONCAT("CREATE TABLE IF NOT EXISTS ",col_a,"(valueid TINYINT NOT NULL PRIMARY KEY auto_increment,",col_b," VARCHAR(255) NULL,",col_c," VARCHAR(255) NULL,",col_d," VARCHAR(255) NULL);");
prepare stmt1 from @preparable_stmt1;
EXECUTE stmt1;
SET @preparable_stmt2=CONCAT("INSERT INTO ",col_a," (",col_b,",",col_c,",",col_d,") VALUES (@tmp_b,@tmp_c,@tmp_d)");
prepare stmt2 from @preparable_stmt2;
EXECUTE stmt2;
FETCH helloset INTO col_a,col_b,col_c,col_d;
END WHILE;
CLOSE helloset;
END;
CALL pro_splitupqq();
四、结果:
mysql创建动态表名和列名的需求最终得以实现。