https://blog.csdn.net/csdn_heshangzhou/article/details/82963237
https://mp.weixin.qq.com/s/BDXrSFG5KwIi9OjOZcOaYw
一、水平分表
这里做的是我的一个笔记。
水平分表比较简单, 理解就是:
- 合并的表使用的必须是MyISAM引擎
- 表的结构必须一致，包括索引、字段类型、引擎和字符集
数据表
user1
|
1
2
3
4
5
6
|
CREATE TABLE `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; |
user2
|
1
|
create table user2 like user1; |
user
|
1
2
3
4
5
6
|
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `sex` int(1) NOT NULL DEFAULT '0', KEY `id` (`id`)) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`user1`,`user2`); |
1) ENGINE = MERGE 和 ENGINE = MRG_MyISAM是一样的意思，都是代表使用的存储引擎是 Merge。
2) INSERT_METHOD，表示插入方式，取值可以是：0 和 1，0代表不允许插入，1代表可以插入；
3) FIRST插入到UNION中的第一个表，LAST插入到UNION中的最后一个表。
触发器
|
1
2
3
|
create table tb_ids(id int);insert into tb_ids values(1); |
如果user1和user2中有数据的话先清除
|
1
2
|
delete from user1;delete from user2; |
然后在user1和user2表中分别建立一个触发器(tr_seq和tr_seq2)，触发器的功能是 当在user1或者user2表中增加一条记录时，取出tb_ids中的id值，赋给user1和user2的id，然后将tb_ids的id值加1.
user1的触发器:tr_seq
|
1
2
3
4
5
6
7
8
9
|
DELIMITER $$ CREATE TRIGGER tr_seq BEFORE INSERT on user1 FOR EACH ROW BEGIN select id into @testid from tb_ids limit 1; update tb_ids set id = @testid + 1; set new.id = @testid; END$$ DELIMITER; |
user2的触发器:tr_seq2
|
1
2
3
4
5
6
7
8
9
|
DELIMITER $$ CREATE TRIGGER tr_seq2 BEFORE INSERT on user2 FOR EACH ROW BEGIN select id into @testid from tb_ids limit 1; update tb_ids set id = @testid + 1; set new.id = @testid; END$$ DELIMITER; |
我是直接扔进一个sql文件source执行的, 效果都一样.
然后查询一下触发器
|
1
|
select * from information_schema.triggers where TRIGGER_NAME='tr_seq' G; |
此时已经分表成功, 下面插入数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.02 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.00 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('张飞',2);Query OK, 1 row affected (0.00 sec)mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.01 sec)mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.01 sec)mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.10 sec)mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.01 sec)mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.01 sec)mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.01 sec)mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.01 sec) |
查询一下所有数据库
|
1
|
show tables; |
查询user1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> select * from user1;+----+--------+-----+| id | name | sex |+----+--------+-----+| 1 | 王五 | 1 || 11 | 王五 | 1 || 12 | 王五 | 1 || 13 | 王五 | 1 || 14 | 王五 | 1 || 15 | 王五 | 1 || 16 | 王五 | 1 || 17 | 王五 | 1 |+----+--------+-----+8 rows in set (0.00 sec) |
查询user2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> select * from user2;+----+--------+-----+| id | name | sex |+----+--------+-----+| 2 | 张飞 | 2 || 3 | 张飞 | 2 || 4 | 张飞 | 2 || 5 | 张飞 | 2 || 6 | 张飞 | 2 || 7 | 张飞 | 2 || 8 | 张飞 | 2 || 9 | 张飞 | 2 || 10 | 张飞 | 2 |+----+--------+-----+9 rows in set (0.00 sec) |
再插入几条数据, 前面插入的太偏向了
|
1
2
3
4
5
6
7
8
|
mysql> insert into user1(name,sex) values('王五',1);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('王五',1);Query OK, 1 row affected (0.01 sec)mysql> insert into user2(name,sex) values('王五',4);Query OK, 1 row affected (0.04 sec)mysql> insert into user1(name,sex) values('王五',4);Query OK, 1 row affected (0.01 sec) |
此时查看user
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
mysql> select * from user order by id asc;+----+--------+-----+| id | name | sex |+----+--------+-----+| 1 | 王五 | 1 || 2 | 张飞 | 2 || 3 | 张飞 | 2 || 4 | 张飞 | 2 || 5 | 张飞 | 2 || 6 | 张飞 | 2 || 7 | 张飞 | 2 || 8 | 张飞 | 2 || 9 | 张飞 | 2 || 10 | 张飞 | 2 || 11 | 王五 | 1 || 12 | 王五 | 1 || 13 | 王五 | 1 || 14 | 王五 | 1 || 15 | 王五 | 1 || 16 | 王五 | 1 || 17 | 王五 | 1 || 18 | 王五 | 1 || 19 | 王五 | 1 || 20 | 王五 | 4 || 21 | 王五 | 4 |+----+--------+-----+21 rows in set (0.00 sec) |
user1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> select * from user1 order by id asc;+----+--------+-----+| id | name | sex |+----+--------+-----+| 1 | 王五 | 1 || 11 | 王五 | 1 || 12 | 王五 | 1 || 13 | 王五 | 1 || 14 | 王五 | 1 || 15 | 王五 | 1 || 16 | 王五 | 1 || 17 | 王五 | 1 || 18 | 王五 | 1 || 21 | 王五 | 4 |+----+--------+-----+10 rows in set (0.00 sec) |
user2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> select * from user2 order by id asc;+----+--------+-----+| id | name | sex |+----+--------+-----+| 2 | 张飞 | 2 || 3 | 张飞 | 2 || 4 | 张飞 | 2 || 5 | 张飞 | 2 || 6 | 张飞 | 2 || 7 | 张飞 | 2 || 8 | 张飞 | 2 || 9 | 张飞 | 2 || 10 | 张飞 | 2 || 19 | 王五 | 1 || 20 | 王五 | 4 |+----+--------+-----+11 rows in set (0.00 sec) |
下面我们update一下
|
1
2
3
|
mysql> update user set name='刘备' where id ='9';Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0 |
再看一下user
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
mysql> select * from user order by id asc;+----+--------+-----+| id | name | sex |+----+--------+-----+| 1 | 王五 | 1 || 2 | 张飞 | 2 || 3 | 张飞 | 2 || 4 | 张飞 | 2 || 5 | 张飞 | 2 || 6 | 张飞 | 2 || 7 | 张飞 | 2 || 8 | 张飞 | 2 || 9 | 刘备 | 2 || 10 | 张飞 | 2 || 11 | 王五 | 1 || 12 | 王五 | 1 || 13 | 王五 | 1 || 14 | 王五 | 1 || 15 | 王五 | 1 || 16 | 王五 | 1 || 17 | 王五 | 1 || 18 | 王五 | 1 || 19 | 王五 | 1 || 20 | 王五 | 4 || 21 | 王五 | 4 |+----+--------+-----+21 rows in set (0.00 sec) |
user1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> select * from user1;+----+--------+-----+| id | name | sex |+----+--------+-----+| 1 | 王五 | 1 || 11 | 王五 | 1 || 12 | 王五 | 1 || 13 | 王五 | 1 || 14 | 王五 | 1 || 15 | 王五 | 1 || 16 | 王五 | 1 || 17 | 王五 | 1 || 18 | 王五 | 1 || 21 | 王五 | 4 |+----+--------+-----+10 rows in set (0.00 sec) |
user2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> select * from user2;+----+--------+-----+| id | name | sex |+----+--------+-----+| 2 | 张飞 | 2 || 3 | 张飞 | 2 || 4 | 张飞 | 2 || 5 | 张飞 | 2 || 6 | 张飞 | 2 || 7 | 张飞 | 2 || 8 | 张飞 | 2 || 9 | 刘备 | 2 || 10 | 张飞 | 2 || 19 | 王五 | 1 || 20 | 王五 | 4 |+----+--------+-----+11 rows in set (0.00 sec) |
水平分表完成!