Atlas分表

来源：互联网发布：淘宝店招尺寸950 编辑：程序博客网时间：2024/05/21 17:39

Atlas在库内完成分表，使用参数tables指定

tables = test.tb1.id.3

首先创建三张结构完成相同的子表tb1_0、tb1_1、tb1_2

CREATE TABLE `tb1_0` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后使用tb1插入

insert into tb1 values (9,'a',20);insert into tb1 values (10,'a',20);insert into tb1 values (11,'a',20);

查询子表，数据已按id列取模分表存储

MySQL [test]> select * from tb1_0;+----+------+------+| id | name | age  |+----+------+------+|  9 | a    |   20 |+----+------+------+MySQL [test]> select * from tb1_1;+----+------+------+| id | name | age  |+----+------+------+| 10 | a    |   20 |+----+------+------+MySQL [test]> select * from tb1_2;+----+------+------+| id | name | age  |+----+------+------+| 11 | a    |   20 |+----+------+------+

主表查询

MySQL [test]> select * from tb1 where id =11;+----+------+------+| id | name | age  |+----+------+------+| 11 | a    |   20 |

限制

1、多行插入不分表

MySQL [test]> insert into tb1 values (1,'a',20),(2,'b',21),(3,'c',21),(4,'d',22),(5,'e',20),(6,'f',21),(7,'g',20),(8,'h',22);MySQL [test]> select * from tb1_1;+----+------+------+| id | name | age  |+----+------+------+|  1 | a    |   20 ||  2 | b    |   21 ||  3 | c    |   21 ||  4 | d    |   22 ||  5 | e    |   20 ||  6 | f    |   21 ||  7 | g    |   20 ||  8 | h    |   22 || 10 | a    |   20 |+----+------+------+

2、不支持不加分表字段的语句

MySQL [test]> select * from tb1;ERROR 1146 (42S02): Table 'test.tb1' doesn't existselect * from tb1 where name = 'a';ERROR 1146 (42S02): Table 'test.tb1' doesn't existMySQL [test]> select * from tb1 where age = 20;ERROR 1146 (42S02): Table 'test.tb1' doesn't existMySQL [test]> update tb1 set age=30 where age =20;ERROR 1146 (42S02): Table 'test.tb1' doesn't existMySQL [test]<span id="transmark"></span>> delete from tb1 where age=20;ERROR 1146 (42S02): Table 'test.tb1' doesn't existMySQL [test]> update tb1 set age=30 ;ERROR 1105 (07000): Proxy Warning - Syntax Forbidden

3、更新后，数据不会移动，仍在原子表中

MySQL [test]> update tb1 set id=12 where id =11;MySQL [test]> select * from tb1_2;+----+------+------+| id | name | age  |+----+------+------+| 12 | a    |   20 |+----+------+------+

0 0