A
tlas在库内完成分表,使用参数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 exist
select * from tb1 where name = 'a';
ERROR 1146 (42S02): Table 'test.tb1' doesn't exist
MySQL [test]> select * from tb1 where age = 20;
ERROR 1146 (42S02): Table 'test.tb1' doesn't exist
MySQL [test]> update tb1 set age=30 where age =20;
ERROR 1146 (42S02): Table 'test.tb1' doesn't exist
MySQL [test]<span id="transmark"></span>> delete from tb1 where age=20;
ERROR 1146 (42S02): Table 'test.tb1' doesn't exist
MySQL [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 |
+----+------+------+