有一类业务,例如订单表和订单明细表,明细表通常会依赖于订单表,这类关系可以用Mycat的ER分片表来表示,字表的记录与所关联的父表记录存放在同一个数据分片上,也是为了避免了跨库Join。下面我们再构造一个order_detail表来实现ER分片规则。
<tablename="order"dataNode="dn1,dn2"rule="mod-long"> <childTablename="order_detail"primaryKey="id"joinKey="order_id"parentKey="id"/> </table>
ORDER_DETAIL.HISIDS= ORDER_DETAIL.CURID=0 ORDER_DETAIL.MINID=1 ORDER_DETAIL.MAXID=1000000000
cd /opt/mycat ./bin/mycat restart
mysql -h127.0.0.1 -P8066 -uroot -pbboyjing use OrderDB; mysql> CREATE TABLE order_detail ( -> `id` int(11) NOT NULL, -> `order_id` int(11) NOT NULL, -> `remark` varchar(50) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@Data publicclassOrder{ privateInteger id; privateInteger status; privateInteger price; privateLong createTime; } @Mapper publicinterfaceOrderDetailMapper{ @Insert("<script>"+ "insert into `order_detail`(id, order_id,remark) " + "values " + "(next value for MYCATSEQ_ORDER_DETAIL,#{orderDetail.orderId},#{orderDetail.remark}) " + "</script>") voidinsertOrderDetail(@Param("orderDetail")OrderDetail orderDetail); } publicvoidaddOrderDetail(){ OrderDetail orderDetail; for(inti =1; i <=10; i++){ orderDetail = newOrderDetail(); intorderId = i %2==1?1:2; orderDetail.setOrderId(orderId); orderDetail.setRemark("this is detail from order--"+ orderId); orderDetailMapper.insertOrderDetail(orderDetail); } }
分别连上dn1和dn2看下数据: