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