创建user1和user2两个分表
建表语句如下:只是表名不一样,其他字段信息及主键一致。
CREATE TABLE IF NOT EXISTS user1(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50) DEFAULT NULL,
sex INT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS user2(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50) DEFAULT NULL,
sex INT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
加入测试数据:
INSERT INTO user1(NAME,sex) VALUES('huangbaokang',0)
INSERT INTO user2(NAME,sex) VALUES('zhanglulu',0)
创建总表:
CREATE TABLE IF NOT EXISTS t_user (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) DEFAULT NULL,
sex INT(1) NOT NULL DEFAULT '0',
INDEX(id)
) ENGINE = MRG_MYISAM UNION =(user1,user2) INSERT_METHOD LAST CHARSET UTF8;
采用merge类型,insert_method为last
查询结果如下:
往t_user表中插入一条数据:
INSERT INTO t_user(NAME,sex) VALUES('猪八戒',1);
1
插入到了user2表中,因为INSERT_METHOD为last,最后插入的是user2表。
业务分表实现
当一个项目数据库表设计的时候没有考虑到分表时,时间久而久之,表的数据量会非常巨大,如某某平台注册信息表。
假如我有一张用户表user,有50W条数据,现在要拆成二张表user1和user2,每张表25W条数据,
INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000
INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000
这样我就成功的将一张user表,分成了二个表,这个时候有一个问题,代码中的sql语句怎么办,以前是一张表,现在变成二张表了,代码改动很大,这样给程序员带来了很大的工作量,有没有好的办法解决这一点呢?办法是把以前的user表备份一下,然后删除掉,上面的操作中我建立了一个t_user表,只把这个t_user表的表名改成user就行了。但是,不是所有的mysql操作都能用的。
如:
如果你使用 alter table 来把 merge 表变为其它表类型,到底层表的映射就被丢失了。取而代之的,来自底层 myisam 表的行被复制到已更换的表中,该表随后被指定新类型。
更新t_user表,看会不会影响其他表数据。执行如下:
UPDATE t_user SET sex=1
再次查询,发现是可以修改存储在其他表的数据。