mysql批量创建分表,批量创建分表 - vinci321的个人空间 - OSCHINA - 中文开源技术交流社区...

有两种方法：

１，在mysql中创建存储过程，然后执行

存储过程如下:

USE tobresume;

DROP PROCEDURE IF EXISTS Proc;

DELIMITER $$

CREATE PROCEDURE Proc()

BEGIN

DECLARE db_name VARCHAR(20);

DECLARE table_name VARCHAR(20);

DECLARE table_pro CHAR(18);

DECLARE sql_text VARCHAR(3000);

DECLARE i INT;

DECLARE j VARCHAR(2);

SET db_name='tobresume';

SET table_name='';

SET table_pro ='_talent_dir_resume';

SET sql_text='';

SET i=0;

SET j='';

WHILE i<=128 DO

IF i<10 THEN

SET j=CONCAT('0',i);

ELSE

SET j=LPAD(LCASE(HEX(i)),2,'0');

END IF;

SET table_name=CONCAT(j,table_pro);

SET sql_text=CONCAT('CREATE TABLE IF NOT EXISTS ', db_name,'.',table_name, '(

id int(11) NOT NULL AUTO_INCREMENT,

uid int(11) unsigned NOT NULL DEFAULT 0 COMMENT '用户账号ID',

top_id int(11) unsigned NOT NULL DEFAULT 0 COMMENT '集团账号ID',

dir_id int(11) unsigned NOT NULL DEFAULT 0 COMMENT '自定义文件夹ID',

parent_id int(11) unsigned NOT NULL DEFAULT 0 COMMENT '父账号ID',

resume_id bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '简历ID',

is_deleted tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否被删除：1.是 0.否',

created int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',

updated int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',

PRIMARY KEY (id),

UNIQUE KEY unique_uid_dirid_cvid (uid,dir_id,resume_id)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='人才库简历文件夹对应关系表'');

SELECT sql_text;

SET @sql_text=sql_text;

PREPARE stmt FROM @sql_text;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET i=i+1;

END WHILE;

END$$

DELIMITER ;

call Proc();

结果如下:

| sql_text |

+--------------------------------------------------------------------------------------------------

| CREATE TABLE IF NOT EXISTS tobresume.80_talent_dir_resume(

id int(11) NOT NULL AUTO_INCREMENT,

uid int(11) unsigned NOT NULL DEFAULT 0 COMMENT '用户账号ID',

top_id int(11) unsigned NOT NULL DEFAULT 0 COMMENT '集团账号ID',

dir_id int(11) unsigned NOT NULL DEFAULT 0 COMMENT '自定义文件夹ID',

parent_id int(11) unsigned NOT NULL DEFAULT 0 COMMENT '父账号ID',

resume_id bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '简历ID',

is_deleted tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否被删除：1.是 0.否',

created int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',

updated int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',

PRIMARY KEY (id),

UNIQUE KEY unique_uid_dirid_cvid (uid,dir_id,resume_id)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='人才库简历文件夹对应关系表' |

+---------------------------------------------------------------------------------------------------

1 row in set (38.44 sec)

Query OK, 0 rows affected (38.90 sec)

生成129张分表共耗时38.44秒.

第二种方法是shell脚本

#!/bin/bash

MYSQLPATH=`echo 'mysql -h192.168.1.201 -udevuser -pdevuser;'`;

DB_NAME='tobresume';

TABLES_NAME='talent_dir_resume';

for i in `seq 128`;

do

j=`echo 'obase=16;' $i | bc | tr '[A-z]' '[a-z]'`;

x=`echo $j | awk '{print length($0)}'`;

if [ $x -lt 2 ];then

j="0"$j

fi

ALTER_SQL_1="USE $DB_NAME;set names utf8;CREATE TABLE IF NOT EXISTS ${j}_$TABLES_NAME (

id int(11) NOT NULL AUTO_INCREMENT,

uid int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户账号ID',

top_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '集团账号ID',

dir_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '自定义文件夹ID',

parent_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '父账号ID',

resume_id bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '简历ID',

is_deleted tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否被删除：1.是 0.否',

created int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',

updated int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',

PRIMARY KEY (id),

UNIQUE KEY unique_uid_dirid_cvid (uid, dir_id, resume_id)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='人才库简历文件夹对应关系表'"

echo "正在创建${j}_$TABLES_NAME表"

$MYSQLPATH -e "$ALTER_SQL_1"

done

这种方法的缺点就是每次创建都要去连数据库，很有可能被拒绝访问。如下

正在创建80_talent_dir_resume表

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'devuser'@'192.168.1.140' (using password: YES)

参考:

http://zhaoshijie.iteye.com/blog/2280570

https://blog.csdn.net/a__yes/article/details/52795793