mysql创建多张表结构一样的表(分表时用)

五郎.

一、存储过程创建多张表
1.利用存储过程,动态创建多张表：
创建16张表，从score_stream_00到score_stream_15
DELIMITER $$
CREATE
PROCEDURE `score`.`p`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=1;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<16 DO
IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);
ELSE SET table_name=CONCAT(table_pre,i);
END IF;
SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
id VARCHAR(32) NOT NULL COMMENT \'主键\',
user_id VARCHAR(32) NOT NULL COMMENT \'用户ID\',
direction VARCHAR(8) NOT NULL COMMENT \'收支方向 收入-IN 支出-OUT\',
operate_score INT(11) NOT NULL COMMENT \'操作的积分数\',
operator VARCHAR(32) NOT NULL COMMENT \'操作人：如果是系统，则为system\',
operate_time DATETIME NOT NULL COMMENT \'操作时间，首次生成积分流水的操作时间，用来排序显示\',
score_status INT(4) NOT NULL',
score_rule_id VARCHAR(32) DEFAULT NULL COMMENT \'积分规则ID\',
member_level INT(4) DEFAULT NULL COMMENT \'会员等级1：普通会员，2：VIP会员\',  
score_type INT(4) NOT NULL COMMENT \'获取积分的类型，和score_task表中的score_type一个含义\',
product_id VARCHAR(32) DEFAULT NULL COMMENT \'产品ID\',
remark VARCHAR(256) DEFAULT NULL COMMENT \'积分流水的备注(用来给内部人看的)\',
operate_source INT(4) DEFAULT NULL COMMENT \'操作来源：1：系统操作触发，2：人工操作触发\',
gmt_modify DATETIME NOT NULL COMMENT \'表改修时间\',
gmt_create DATETIME NOT NULL COMMENT \'表创建时间\',
PRIMARY KEY (id),
KEY idx_user_id (user_id),
KEY idx_direction (direction),
KEY idx_operate_time (operate_time),
KEY idx_score_status (score_status)
) ENGINE=INNODB DEFAULT CHARSET=utf8' );
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$$
DELIMITER ;


2.执行存储过程，创建表
CALL p();

二、利用sql语句创建多张表
创建16张表：
CREATE TABLE `usermng_daily_earning_0000` (
`id` VARCHAR(24) NOT NULL COMMENT '主键 15位时间戳+3位随机数+userId后两位+4位表示所在表',
`user_id` VARCHAR(22) NOT NULL COMMENT '用户ID（根据userId来分表）',
`occur_date` INT(8) NOT NULL COMMENT '收益发生日期',
`ta_id` VARCHAR(22) DEFAULT NULL COMMENT '交易模式',
`product_id` VARCHAR(20) NOT NULL COMMENT '产品ID',
`product_name` VARCHAR(50) NOT NULL COMMENT '产品名称',
`order_id` VARCHAR(20) DEFAULT NULL COMMENT '订单ID',
`assets_id` VARCHAR(22) NOT NULL COMMENT'资产ID',
`earning` DECIMAL(16,2) NOT NULL COMMENT '产生的收益',
`modify_time` DATETIME NOT NULL COMMENT '表修改时间',
`create_time` DATETIME NOT NULL COMMENT '表创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`) USING BTREE,
KEY `IDX_occur_date` (`occur_date`) USING BTREE,
KEY `IDX_create_time` (`create_time`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `usermng_daily_earning_0008` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0016` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0024` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0032` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0040` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0048` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0056` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0064` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0072` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0080` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0088` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0096` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0104` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0112` LIKE usermng_daily_earning_0000;
CREATE TABLE `usermng_daily_earning_0120` LIKE usermng_daily_earning_0000;

注意：额外说一句，第二种表命名的方式更加便于以后数据迁移和扩容