爱技术 & 爱分享
爱蛋蛋 & 爱生活

MYSQL 批量建库、建表

工作中在处理大量的数据时,因为单表性能的问题,所以不可避免的进行分库分表操作,其实也就是按某种规则进行哈希。比如我们按QQ号的个十位进行分表,那么我们需要创建100个表,0~99或者说00~99,但是这么多表的创建,我们不可能手动来创建吧,所以就需要一些自动化操作了。

比如我们需要创建一个100张这样子的表,以第一张表为例:

CREATE TABLE IF NOT EXISTS `player_info_0`(
   `c_id` INT UNSIGNED AUTO_INCREMENT,
   `c_uin` INT UNSIGNED NOT NULL,
   `c_name` VARCHAR(40) NOT NULL,
   `c_level` INT UNSIGNED  NOT NULL,
   `c_coin` VARCHAR(40) ,
   PRIMARY KEY ( `c_id` ),
   INDEX Uin (`c_uin`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

想要批量创建,有几种比较常用的做法,存储过程,shell脚本,python脚本

这里主要介绍一下前面两种方法:

存储过程法:

delimiter //
create procedure IF NOT EXISTS CreateTableBatch()
BEGIN 
        DECLARE `@i` int(11);
        DECLARE `@createSql` VARCHAR(2560);


        set `@i`=0;
        WHILE  `@i`< 100 DO
             -- 创建表
             SET @createSql = CONCAT('CREATE TABLE IF NOT EXISTS player_info_',`@i`,'(
                   c_id INT UNSIGNED AUTO_INCREMENT,
                   c_uin INT UNSIGNED NOT NULL,
                   c_name VARCHAR(40) NOT NULL,
                   c_level INT UNSIGNED  NOT NULL,
                   c_coin VARCHAR(40) ,
                   PRIMARY KEY ( c_id ),
                   INDEX Uin (c_uin)
                    )ENGINE=InnoDB DEFAULT CHARSET=utf8;'
                    );

                    prepare stmt from @createSql;
                    execute stmt;
SET `@i`= `@i`+1; 
            END WHILE;
END //

delimiter ;

call  CreateTableBatch;
drop procedure CreateTableBatch;

关于 delimiter 的解释请看 MySQL中的delimiter

当然也有把建表与建索引分开来的写法(不推荐):

delimiter //
create procedure IF NOT EXISTS CreateTableBatch()
BEGIN 
        DECLARE `@i` int(11);
        DECLARE `@createSql` VARCHAR(2560);
        DECLARE `@createIndexSql` VARCHAR(2560);

        set `@i`=0;
        WHILE  `@i`< 100 DO
             -- 创建表
             SET @createSql = CONCAT('CREATE TABLE IF NOT EXISTS player_info_',`@i`,'(
                   c_id INT UNSIGNED AUTO_INCREMENT,
                   c_uin INT UNSIGNED NOT NULL,
                   c_name VARCHAR(40) NOT NULL,
                   c_level INT UNSIGNED  NOT NULL,
                   c_coin VARCHAR(40) ,
                   PRIMARY KEY ( c_id )
                    )ENGINE=InnoDB DEFAULT CHARSET=utf8;'
                    );

                    prepare stmt from @createSql;
                    execute stmt;

                     -- 创建索引
                    set @createIndexSql  = CONCAT('create index `Index_uin` on player_info_',`@i`,'(`c_uin`);');

                    prepare stmt from @createIndexSql;
                    execute stmt;
SET `@i`= `@i`+1;
            END WHILE;
END //

delimiter ;

call  CreateTableBatch;
drop procedure CreateTableBatch;

shell脚本法

#!/bin/bash

for((i=0;i<100;i++))
do
     mysql -A -hxxx.xxx.xxx.xxx -P8023 -Dd_player_info -uplayer_info -pe51ddfa50 -e" CREATE TABLE IF NOT EXISTS player_info_$i ( \
                    c_id INT UNSIGNED AUTO_INCREMENT,\
                    c_uin INT UNSIGNED NOT NULL,\
                    c_name VARCHAR(40) NOT NULL,\
                    c_level INT UNSIGNED  NOT NULL,\
                    c_coin VARCHAR(40) ,\
                    PRIMARY KEY ( c_id ),\
                    INDEX Uin (c_uin)\
                    )ENGINE=InnoDB DEFAULT CHARSET=utf8;"
done

这种写法的话,会连接一百次数据库,然后每次只执行一条语句,并不理想,我们希望连接一次执行100条语句。

 #!/bin/bash

 for((i=0;i<100;i++))
 do
         str=$str"CREATE TABLE IF NOT EXISTS player_info_$i ( \
                    c_id INT UNSIGNED AUTO_INCREMENT,\
                    c_uin INT UNSIGNED NOT NULL,\
                    c_name VARCHAR(40) NOT NULL,\
                    c_level INT UNSIGNED  NOT NULL,\
                    c_coin VARCHAR(40) ,\
                    PRIMARY KEY ( c_id ),\
                    INDEX Uin (c_uin)\
                    )ENGINE=InnoDB DEFAULT CHARSET=utf8;"

 done

 mysql -A -hxxx.xxx.xxx.xxx -P8023 -Dd_player_info -uplayer_info -pe51ddfa50  -e" $str" >output

这样子的话就是连接一次,执行100个表的创建,还有一种做法就是将要执行的语句写到文件中之后,然后连接mysql 执行 source /path/CreateTableBatch.sql

这种方法留给读者自行实现。

参考文章:

http://www.ywnds.com/?p=7664

http://outofmemory.cn/code-snippet/1614/shell-execution-mysql-jizhong-scheme

http://www.cnblogs.com/onmyway20xx/p/4222296.html

赞(0) 打赏 传送门
本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。ShadowInk » MYSQL 批量建库、建表
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

游戏 && 后端

传送门传送门

觉得文章写的还行就打赏一下呗~~~

支付宝扫一扫打赏

微信扫一扫打赏

%d 博主赞过: