mysql批量建表和批量插入数据

1. 批量建表

使用存储过程来实现

-- 1. 先删除存储过程
DROP PROCEDURE IF EXISTS `create_tables`;
-- 2. 结束符改为$
DELIMITER $
-- 3. 创建存储过程
CREATE PROCEDURE create_tables ()
BEGIN

DECLARE `@i` INT (11);
DECLARE `@createSql` VARCHAR (2560);
SET `@i` = 0;
WHILE `@i` < 100 DO
    -- 创建表        
SET @createSql = CONCAT(
    "CREATE TABLE IF NOT EXISTS guess_record_", `@i`,
    "(
  `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 $
-- 4. 结束符改为;
DELIMITER ;  
-- 5. 执行存储过程  
CALL `create_tables`();
-- 6. 删除存储过程
DROP PROCEDURE IF EXISTS `create_tables`;

2. 批量插入数据

DELIMITER $
create procedure batchInsert(in args int)
begin
declare i int default 1;
declare `@createSql` varchar(2560);
while i <= args do
set @createSql = CONCAT(
'insert into t_mysql_info (id, table_name) value(', i, ', "t_message_info_', i-1, '");'
);
prepare stmt from @createSql; 
execute stmt;
set i = i+1;
end while;
end
$
DELIMITER ;
call batchInsert(100);

3. 批量添加字段

DELIMITER $
create procedure addColumn()
begin
declare `@i` int(11);
DECLARE `@createSql` VARCHAR (2560);
SET `@i` = 0;
WHILE `@i` < 100 DO
set @createSql = concat(
'ALTER TABLE t_message_info_', `@i`, ' ADD submit_time varchar(256);'
);
PREPARE stmt FROM @createSql;
EXECUTE stmt;
SET `@i` = `@i` + 1;
END WHILE;
END $
DELIMITER ;
call addColumn();

4. 批量清空表格

DELIMITER $
create procedure deleteAll()
begin
declare `@i` int(11);
DECLARE `@createSql` VARCHAR (2560);
SET `@i` = 0;
WHILE `@i` < 100 DO
set @createSql = concat(
'delete from t_message_info_', `@i`, ';'
);
PREPARE stmt FROM @createSql;
EXECUTE stmt;
SET `@i` = `@i` + 1;
END WHILE;
END $
DELIMITER ;
call deleteAll();