CREATE TABLE user1 (
id INT auto_increment NOT NULL,
name varchar(50) NULL,
sex INT DEFAULT 0 NOT NULL,
CONSTRAINT user1_pk PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE user2 (
id INT auto_increment NOT NULL,
name varchar(50) NULL,
sex INT DEFAULT 0 NOT NULL,
CONSTRAINT user1_pk PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE user3 (
id INT auto_increment NOT NULL,
name varchar(50) NULL,
sex INT DEFAULT 0 NOT NULL,
CONSTRAINT user1_pk PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE alluser (
id INT auto_increment NOT NULL,
name varchar(50) NULL,
sex INT DEFAULT 0 NOT null,
index(id)
)
ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
INSERT_METHOD=LAST UNION=(`user1`,`user2`,`user3`)
auto_increment=1 COMMENT='一种分表实现方法';
insert into alluser(name,sex)values('童童',1);
select * from alluser;
delete from alluser where id=1;
select * from user1 ;
select * from user2 ;
select * from user3 ;
INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 5000000;
INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 5000000 and user.id <= 10000000;
INSERT INTO user3(user3.id,user3.name,user3.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 10000000;