MySQL bulk create table stored procedure

100 tables were created because of business needs, but the structure of these tables is the same, and as programmers, it is to solve this repetitive work. However, this kind of thing to write a PHP script alone is too much trouble, so I simply learned how to do it directly with the MySQL stored procedure:

The first is to create the table: (Lpad (' @i ', 2, ' 0 ') is the function of converting these numbers into ' 01 ', ' 02 ', ' 03 ')

DROP PROCEDURE IF EXISTS' create_tables ';CREATE PROCEDUREcreate_tables ()BEGINDECLARE`@i`INT( One);DECLARE`@createSql`VARCHAR(2560);SET`@i`= 0; while`@i`<  - Do--Create a tableSET @createSql =CONCAT ("CREATE TABLE IF  not EXISTSGuess_record ", Lpad ('@i`,2,'0'), "(' ID 'int(Ten) unsigned not NULLauto_increment, ' userid 'int( A) unsigned not NULLCOMMENT'User ID', ' issue 'int(Ten) unsigned not NULLCOMMENT'issue No.', ' options_id 'int(Ten) unsigned not NULLCOMMENT'option ID', ' Guess_time 'int(Ten) unsigned not NULLCOMMENT'Answer Time', ' Is_stat 'tinyint(4) not NULLCOMMENT'have you ever counted',  PRIMARY KEY(' id '),KEY' issue ' (' issue ') USING BTREE,KEY' userid ' (' userid ') USING BTREE) ENGINE=InnoDBDEFAULTCHARSET=UTF8; ");PREPAREstmt from @createSql;EXECUTEstmt;SET`@i`=`@i`+ 1;END  while;END

And then delete the table in bulk (hehe, do things)

DROP PROCEDURE IF EXISTS' drop_tables ';CREATE PROCEDUREdrop_tables ()BEGINDECLARE`@i`INT( One);DECLARE`@dropSql`VARCHAR(2560);SET`@i`= 0; while`@i`<  - Do--Create a tableSET @dropSql =CONCAT ("DROP TABLE IF EXISTSGuess_record ", Lpad ('@i`,2,'0'));PREPAREstmt from @dropSql;EXECUTEstmt;SET`@i`=`@i`+ 1;END  while;END

I use this stored procedure is actually a one-time, so the complete process is 1, create stored procedure 2, execute the Stored procedure (batch CREATE TABLE) 3, delete stored procedure

#1: Creating a Stored procedureDROP PROCEDURE IF EXISTS' create_tables ';D elimiter $$//define TerminatorCREATE PROCEDUREcreate_tables ()BEGINDECLARE`@i`INT( One);DECLARE`@createSql`VARCHAR(2560);SET`@i`= 0; while`@i`<  - Do--Create a tableSET @createSql =CONCAT ("CREATE TABLE IF  not EXISTSGuess_record ", Lpad ('@i`,2,'0'), "(' ID 'int(Ten) unsigned not NULLauto_increment, ' userid 'int( A) unsigned not NULLCOMMENT'User ID', ' issue 'int(Ten) unsigned not NULLCOMMENT'issue No.', ' options_id 'int(Ten) unsigned not NULLCOMMENT'option ID', ' Guess_time 'int(Ten) unsigned not NULLCOMMENT'Answer Time', ' Is_stat 'tinyint(4) not NULLCOMMENT'have you ever counted',  PRIMARY KEY(' id '),KEY' issue ' (' issue ') USING BTREE,KEY' userid ' (' userid ') USING BTREE) ENGINE=InnoDBDEFAULTCHARSET=UTF8; ");PREPAREstmt from @createSql;EXECUTEstmt;SET`@i`=`@i`+ 1;END  while;END$ $DELIMITER; //change Terminator to; #2: Execute stored procedure call ' create_tables ' (); #3: Delete a stored procedureDROP PROCEDURE IF EXISTS' Create_tables ';

MySQL bulk create table stored procedure