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