mysql分表一使用MRG_MyISAM(MyISAM引擎有效)
1、先创建子表,再创建总表
DROP TABLE IF EXISTS `admin_log0`;
CREATE TABLE `admin_log0` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '' COMMENT '名字',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='日志表'
create table admin_log1 like admin_log0;
create table admin_log2 like admin_log0;
总表:
DROP TABLE IF EXISTS `admin_log`;
CREATE TABLE `admin_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '' COMMENT '名字',
PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`admin_log0`,`admin_log1`,`admin_log2`) COMMENT='日志表';
注:子表、总表的表结构必须一致
2、数据入库(这里入库可以自定义规则,如id%3)
例如:先程序计算好表后缀,再分别插入字表
INSERT INTO admin_log0 VALUES (3,'test1');
INSERT INTO admin_log1 VALUES (1,'test1');
INSERT INTO admin_log2 VALUES (2,'test1');
3、数据查询
a.直接查询全部数据select * from admin_log;
b.只查询admin_log0表数据select * from admin_log0;
优点:便于查询数据
缺点:更改表结构麻烦(需要逐一修改子表,后重新创建总表)
ALTER TABLE admin_log0 CHANGE name `name` char(100) NOT NULL DEFAULT '' COMMENT '名字';
ALTER TABLE admin_log1 CHANGE name `name` char(100) NOT NULL DEFAULT '' COMMENT '名字';
ALTER TABLE admin_log2 CHANGE name `name` char(100) NOT NULL DEFAULT '' COMMENT '名字';
DROP TABLE IF EXISTS `admin_log`;
CREATE TABLE `admin_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(100) NOT NULL DEFAULT '' COMMENT '名字',
PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`admin_log0`,`admin_log1`,`admin_log2`) COMMENT='日志表';