写脚本创建分表（存储过程和like关键字）

分表存储过程

CREATE TABLE tb_demo_credit (
id varchar(255) NOT NULL,
batch_id_inline varchar(255) DEFAULT NULL,
field1 varchar(255) DEFAULT NULL,
field2 varchar(255) DEFAULT NULL,
field3 varchar(255) DEFAULT NULL,
field4 varchar(255) DEFAULT NULL,
field5 varchar(255) DEFAULT NULL,
field6 varchar(255) DEFAULT NULL,
state tinyint(4) DEFAULT NULL,
PRIMARY KEY (id),
KEY id (id) USING BTREE,
KEY batch_id_inline (batch_id_inline) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘模拟demo生成服务的债权记录’;

存储过程参数 ‘tb_demo_credit’,0,3

分表存储过程代码

BEGIN
		 DECLARE i INT; 
     DECLARE table_name VARCHAR(30); 
     DECLARE sql_text VARCHAR(2000); 
     SET i=min; 
     SET table_name=''; 
     SET sql_text=''; 
     WHILE i<=max DO 
      SET table_name=CONCAT(table_pre,i); 
      
      SET sql_text=CONCAT('CREATE TABLE ' ,table_name, " 
(
  `id` varchar(255) NOT NULL,
  `batch_id_inline` varchar(255) DEFAULT NULL,
  `field1` varchar(255) DEFAULT NULL,
  `field2` varchar(255) DEFAULT NULL,
  `field3` varchar(255) DEFAULT NULL,
  `field4` varchar(255) DEFAULT NULL,
  `field5` varchar(255) DEFAULT NULL,
  `field6` varchar(255) DEFAULT NULL,
  `state` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`) USING BTREE,
  KEY `batch_id_inline` (`batch_id_inline`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='模拟demo生成服务的债权记录' " ); 
        
      SELECT sql_text; 
      SET @sql_text=sql_text; 
      PREPARE stmt FROM @sql_text; 
      EXECUTE stmt; 
      DEALLOCATE PREPARE stmt;  
      SET i=i+1; 
    END WHILE;

END

---

方法2 用like语句

CREATE TABLE tb_demo_credit1 LIKE tb_demo_credit0;
CREATE TABLE tb_demo_credit2 LIKE tb_demo_credit0;
CREATE TABLE tb_demo_credit3 LIKE tb_demo_credit0;
CREATE TABLE tb_demo_credit4 LIKE tb_demo_credit0;