Dynamic table creation in mysql database

Generally, this type of requirement is rare when you create tables dynamically in a mysql database. I have never met this before, but this project needs to be done. That is, the field names and number of fields in the table are not fixed and should be created as needed. Www.2cto.com is the process of creating and forming a dynamic table. Step 1. create a related table/* --- create a temporary table for all metric information --- */drop table if exists INTERBANKBONDQUOTE_SClass; create table INTERBANKBONDQUOTE_SClass (Name varchar (50) not null, id int Primary key, parent int, Value varchar (50 ));
Insert into INTERBANKBONDQUOTE_SClass (Name, id, Value) values ('latest quote', 0, ''); insert into INTERBANKBONDQUOTE_SClass (Name, id, Value) values ('buy information ', 1, ''); insert into INTERBANKBONDQUOTE_SClass (Name, id, Value) values ('sales spread ', 2,''); insert into INTERBANKBONDQUOTE_SClass (Name, id, Value) values ('sale information', 3, ''); insert into INTERBANKBONDQUOTE_SClass (Name, id, Parent, Value) values ('latest valuation of medium debt ', 4,-1, ''); insert into INTERB ANKBONDQUOTE_SClass (Name, id, Value) values ('weighted debt behavior indicator ', 5, ''); www.2cto.com insert into INTERBANKBONDQUOTE_SClass (Name, id, Value) values ('basic Index', 6, ''); insert into INTERBANKBONDQUOTE_SClass (Name, id, Value) values ('bilateral quote quantity ', 7 ,''); insert into INTERBANKBONDQUOTE_SClass (Name, id, Parent, Value) values ('quote', 8, 1, 'col4'); insert into INTERBANKBONDQUOTE_SClass (Name, id, Parent, Value) values ('quotor Member No. ', 9, 1, 'col5 '); Insert into INTERBANKBONDQUOTE_SClass (Name, id, Parent, Value) values ('quotation time ', 10, 1, 'col6'); insert into INTERBANKBONDQUOTE_SClass (Name, id, Parent, Value) values ('anonymous', 11, 1, 'col7'); Step 2:/* --- create a table that displays the table title --- */drop table if exists INTERBANKBONDQUOTE_T; create table INTERBANKBONDQUOTE_T as select concat ('\',. name, ',', B. name, '\' _ TITLE _ ', B. value); Step 3: There are two implementation methods. In mysql, the variable length is limited. In this case, longtext should be long enough, but only the limited length is returned. If there are too many fields, use the second method. Stored Procedure A: the length of the variable return value is limited here, and there are not many fields. Www.2cto.com DELIMITER $ drop procedure if exists 'dzhappdb _ bond '. 'interbankbondquote _ TSP '$ create procedure INTERBANKBONDQUOTE_TSP () begin declare objs1 TEXT; DECLARE objs TEXT; SELECT GROUP_CONCAT (col1) INTO objs1 from t; SET objs = CONCAT ('create TABLE Title AS select', objs1); SET @ SQL _txt = objs;
PREPARE stmt FROM @ SQL _txt; EXECUTE stmt; deallocate prepare stmt; END $ call INTERBANKBONDQUOTE_TSP; www.2cto.com stored procedure B: No matter the number of fields, the database can be created successfully. But it is not as simple as the first method. Drop procedure if exists partition; drop table if EXISTS Title; create procedure INTERBANKBONDQUOTE_TSP_Title () proc: begin DECLARE add_ SQL LONGTEXT; DECLARE insert_ SQL LONGTEXT; DECLARE nhh_ SQL varchar (200 ); DECLARE column_name varchar (100); DECLARE column_value varchar (100); DECLARE mycount int; DECLARE len int; DECLARE strlen int; DECLARE cursor_Title CURSOR for select col1 from INTERBANKBONDQUOTE_T; www.2cto.com create table Title (mid int); insert into Title values (100); select count (col1) into @ mycount from INTERBANKBONDQUOTE_T; OPEN cursor_Title; repeat fetch cursor_Title INTO nhh_ SQL; begin set @ mycount = @ mycount-1; set @ strlen = CHARACTER_LENGTH (nhh_ SQL); set @ len = INSTR (nhh_ SQL, ''); set @ column_name = RIGHT (nhh_ SQL, @ strlen-@ len); set @ column_value = LEFT (nhh_ SQL, @ len); set @ add_ SQL = CONCAT ('alter table Title add column', @ column_name, 'varchar (100) '); set @ insert_ SQL = CONCAT ('Update Title set', @ column_name,' = ', @ column_value, 'where mid = 100 '); PREPARE stmt1 FROM @ add_ SQL; EXECUTE stmt1; PREPARE stmt2 FROM @ insert_ SQL; EXECUTE stmt2; PREPARE stmt1; PREPARE stmt2; end; restore until @ mycount <= 0 END REPEAT; CLOSE cursor_Title; ALTER table Title drop column mid; end proc; call INTERBANKBONDQUOTE_TSP_Title;