Mysql 批量建表存储过程

最新推荐文章于 2023-11-21 10:06:02 发布

weixin_33982670

最新推荐文章于 2023-11-21 10:06:02 发布

阅读量109

点赞数

文章标签：数据库

最近项目中用到了使用存储过程批量建表的功能，记录下来：

USE db_test_3;
drop procedure if EXISTS `createTablesWithIndex`;
create procedure createTablesWithIndex()
BEGIN 
        DECLARE `@i` int(11);     
        DECLARE `@createSql` VARCHAR(2560); 
        DECLARE `@createIndexSql1` VARCHAR(2560);     
        DECLARE `@createIndexSql2` VARCHAR(2560);
        DECLARE `@createIndexSql3` VARCHAR(2560);


        set `@i`=0; 
        WHILE  `@i`< 64 DO                 
    
                            -- `M_ID` bigint AUTO_INCREMENT PRIMARY KEY NOT NULL,
                            -- 创建表        
                            SET @createSql = CONCAT('CREATE TABLE IF NOT EXISTS test_',`@i`,'(
                                `t_ID` bigint   AUTO_INCREMENT PRIMARY KEY NOT NULL,
                                `t_CODE` varchar(30) DEFAULT NULL,
                                )'
                            ); 
                            prepare stmt from @createSql; 
                            execute stmt;                             
                        
                            -- 创建索引    
                            set @createIndexSql1  = CONCAT('create index `t_code` on test_',`@i`,'(`t_code`);');
                            prepare stmt from @createIndexSql1; 
                            execute stmt; 
SET `@i`= `@i`+1; 
            END WHILE;
END