MYSQL大量建立表的預存程序

標籤：批量   php   comment   prepare   class   creates   mysq   $$   com   

 

因為業務需要，建立了100個表，但是這些表的結構都是一樣的，作為程式員，就是要解決這種重複勞動。然而這種事情還要單獨寫個php指令碼的話太麻煩了吧，所以就乾脆學了一下直接用Mysql預存程序怎麼實現：

首先是建立表的：（ LPAD(`@i`, 2, ‘0‘)的作用 是將1,2,3這些數字轉化為‘01‘,‘02‘,‘03‘）

DROP PROCEDURE IF EXISTS `create_tables`;CREATE PROCEDURE create_tables ()BEGINDECLARE `@i` INT (11);DECLARE `@createSql` VARCHAR (2560);SET `@i` = 0;WHILE `@i` < 100 DO    -- 建立表        SET @createSql = CONCAT(    "CREATE TABLE IF NOT EXISTS guess_record",    LPAD(`@i`, 2, ‘0‘),    "(  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `userid` int(12) unsigned NOT NULL COMMENT ‘使用者ID‘,  `issue` int(10) unsigned NOT NULL COMMENT ‘期號‘,  `options_id` int(10) unsigned NOT NULL COMMENT ‘選項ID‘,  `guess_time` int(10) unsigned NOT NULL COMMENT ‘答題時間‘,  `is_stat` tinyint(4) NOT NULL COMMENT ‘是否統計過‘,  PRIMARY KEY (`id`),  KEY `issue` (`issue`) USING BTREE,  KEY `userid` (`userid`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;");PREPARE stmt FROM @createSql;EXECUTE stmt;SET `@i` = `@i` + 1;END WHILE;END

然後是大量刪除表的（嘿嘿，搞事情）

DROP PROCEDURE IF EXISTS `drop_tables`;CREATE PROCEDURE drop_tables ()BEGINDECLARE `@i` INT (11);DECLARE `@dropSql` VARCHAR (2560);SET `@i` = 0;WHILE `@i` < 100 DO    -- 建立表        SET @dropSql = CONCAT("DROP TABLE IF EXISTS guess_record",LPAD(`@i`, 2, ‘0‘));PREPARE stmt FROM @dropSql;EXECUTE stmt;SET `@i` = `@i` + 1;END WHILE;END

我用這個預存程序其實就是一次性的，所以完整的過程是 1、建立預存程序 2、執行預存程序（大量建立表）3、刪除預存程序

 

#1:建立預存程序DROP PROCEDURE IF EXISTS `create_tables`;DELIMITER $$ //定義結束符 CREATE PROCEDURE create_tables ()BEGINDECLARE `@i` INT (11);DECLARE `@createSql` VARCHAR (2560);SET `@i` = 0;WHILE `@i` < 100 DO    -- 建立表        SET @createSql = CONCAT(    "CREATE TABLE IF NOT EXISTS guess_record",    LPAD(`@i`, 2, ‘0‘),    "(  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `userid` int(12) unsigned NOT NULL COMMENT ‘使用者ID‘,  `issue` int(10) unsigned NOT NULL COMMENT ‘期號‘,  `options_id` int(10) unsigned NOT NULL COMMENT ‘選項ID‘,  `guess_time` int(10) unsigned NOT NULL COMMENT ‘答題時間‘,  `is_stat` tinyint(4) NOT NULL COMMENT ‘是否統計過‘,  PRIMARY KEY (`id`),  KEY `issue` (`issue`) USING BTREE,  KEY `userid` (`userid`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;");PREPARE stmt FROM @createSql;EXECUTE stmt;SET `@i` = `@i` + 1;END WHILE;END $$DELIMITER ;  //結束符改為； #2:執行預存程序  CALL `create_tables`();#3:刪除預存程序DROP PROCEDURE IF EXISTS `create_tables`;

 

MYSQL大量建立表的預存程序