最近小小的研究了一番mysql数据库的分表,发现一共有三种方式,但是其他俩种都比较繁琐,不如使用merge引擎来的方便
1.使用存储过程创建若干张表,
CREATE procedure fen(IN db_name VARCHAR(20),IN table_pre VARCHAR(20), IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET table_name='';
SET sql_text='';
WHILE i<max_num DO
SET table_name=CONCAT(table_pre,i);
SET sql_text=CONCAT('CREATE TABLE ',db_name ,'.',table_name, '(
id VARCHAR(32) NOT NULL,
name VARCHAR(32),
age VARCHAR(8) ,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8' );
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;
2.运行存储过程,我这里是创建了100张表
call fen("exam","tb_pan",1,101);
3.为这些字表创建一张主表并且加入merge引擎
CREATE TABLE tb_pan
(
id VARCHAR(32) NOT NULL,
name VARCHAR(32),
age VARCHAR(8) ,
PRIMARY KEY (id)
) ENGINE = MERGE UNION = (tb_pan1, tb_pan2, tb_pan3, tb_pan4) INSERT_METHOD = LAST;
这样就完成,最后查询一下
select * from tb_pan