存储过程批量创建数据库表&批量新增字段(mysql)

1、批量创建数据库表(mysql)

delimiter $$

CREATE PROCEDUER `batch_create_table`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(64)
SET i = 0;
WHILE i < 128 DO
  SET table_name = CONCAT('price_store_detail_',i);
  SET @sql = CONCAT(
      'CREATE TABLE',table_name,'(
          ID bigint(18) UNSIGNED NOT NULL auto_increment COMMENT "主键",
          USERNAME VARCHAR(32) COMMENT "名字",
          PRIMARY KEY(ID)
      )ENGINE=Innodb default charset=utf8;'
  );
  PREPARE create_stmt FROM @sql;
  EXECUTE create_stmt;
  SET i = i + 1;
END WHILE;
END $$

delimiter;

CALL batch_create_table();

2、批量往多个表里添加新的字段

DROP PROCEDURE IF EXISTS batch_insert_column;

delimiter $$
CREATE PROCEDURE batch_insert_column()
BEGIN
DECLARE i INT;
SET i = 0;
WHILE i < 128 DO
    SET @sql = CONCAT(
     'ALTER TABLE price_store_detail_',
     i,
     ' ADD age INT(3) COMMENT "年龄";'
    );
    SET i = i + 1;
    PREPARE alter_table FROM @sql;
    EXECUTE alter_table;
    END WHILE;
END $$
delimiter;

CALL batch_insert_column();

3、批量删除多个表

delimiter $$
CREATE PROCEDURE batch_delete_table()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(64);
SET i = 0;
WHILE i < 128 DO
    SET table_name = CONCAT('price_store_detail_',i);
    SET @sql = CONCAT('DROP TABLE ',table_name,';'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET i = i + 1;
    END WHILE;
END $$
delimiter;

CALL batch_delete_table();

在分表的时候,可以采用存储过程进行创建分表的多张表。