Mycat Horizontal sub table E-R surface

1.E-R surface

 Relational database is based on entity relationship model （Entity-Relationship Model) above , It describes things and relationships in the real world ,
Mycat  Medium  ER  Tables come from this . According to this idea , Based on  E-R  Data fragmentation strategy for relationships , The records of the sub table are associated with 
 The parent table records are stored on the same data partition , That is, the child table depends on the parent table , Group by table （Table Group） Guaranteed data  Join  No cross library operation 
 do .
 Table grouping （Table Group） It's about solving cross slice data  join  It's a very good idea of , It is also an important rule of data segmentation planning .

2. modify schema.xml The configuration file

  <table name = "orders_hor" dataNode = "dn1,dn2" rule = "mod_rule">
    <!-- Add sub table configuration -->
        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
  </table>

3. Create table orders_detail

• master1 and master2 On

CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);

4. insert data

INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);

5. verification

mysql> select * from orders_detail;
+----+---------+----------+
| id | detail  | order_id |
+----+---------+----------+
|  1 | detail1 |        1 |
|  2 | detail1 |        2 |
|  6 | detail1 |        6 |
|  3 | detail1 |        3 |
|  4 | detail1 |        4 |
|  5 | detail1 |        5 |
+----+---------+----------+
6 rows in set (0.01 sec)

mysql> Select o.*,od.detail  from orders_hor o inner join orders_detail od  on o.id=od.order_id;
+----+------------+-------------+-----------+---------+
| id | order_type | customer_id | amount    | detail  |
+----+------------+-------------+-----------+---------+
|  3 |        101 |         101 | 120000.00 | detail1 |
|  4 |        101 |         101 | 103000.00 | detail1 |
|  5 |        102 |         101 | 100400.00 | detail1 |
|  1 |        101 |         100 | 100100.00 | detail1 |
|  2 |        101 |         100 | 100300.00 | detail1 |
|  6 |        102 |         100 | 100020.00 | detail1 |
+----+------------+-------------+-----------+---------+
6 rows in set (0.00 sec)