# 创建存储过程
# 声明语句开始符
delimiter $
drop procedure if exists `creteTableByIndex`;
create procedure creteTableByIndex()
begin
declare i int(11);
set i = 0;
while i <= 127
do
-- 创建表
set @createSql = CONCAT('CREATE TABLE ykq_order_', i, '(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ''自增ID'',
`order_no` varchar(100) NOT NULL DEFAULT '''' COMMENT ''订单号'',
`remark` varchar(1000) DEFAULT '''' COMMENT ''备注/说明'',
`ext` varchar(3000) DEFAULT '''' COMMENT ''扩展字段'',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ''创建时间'',
`create_pin` varchar(100) NOT NULL DEFAULT '''' COMMENT ''创建人'',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ''更新时间'',
`modify_pin` varchar(100) NOT NULL DEFAULT '''' COMMENT ''更新人'',
`version` int(11) unsigned NOT NULL DEFAULT ''1'' COMMENT ''版本 默认1'',
`yn` bit(1) NOT NULL DEFAULT b''0'' COMMENT ''是否删除:0:否;1:是 默认0'',
PRIMARY KEY (`id`),
KEY `idx_orderNo` (`order_no`) USING BTREE COMMENT ''订单号索引''
) ENGINE = InnoDB AUTO_INCREMENT = 95829732 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMMENT = ''订单表'''
);
prepare stmt from @createSql;
execute stmt;
SET i = i + 1;
end while;
end $
# 声明语句结束符
delimiter ;
# 调用存储过程
call creteTableByIndex();
分库分表时使用存储过程创建表
最新推荐文章于 2022-11-19 12:23:56 发布