最近有点忙更新的太慢抱歉了。
一、实验环境
实验环境 服务内容
192.168.0.1 mycat
192.168.0.2 mysql
192.168.0.3 mysql
二、准备的实验数据如下:
192.168.0.2: create database order_db; create user im_mycat@'192.168.0.%' identified by '123456'; grant select ,insert,update,delete on *.* to im_mycat@'192.168.0.%'; --垂直分库 CREATE TABLE order_master ( order_id INT UNSIGNED NOT NULL COMMENT '订单ID', order_sn BIGINT UNSIGNED NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn', customer_id INT UNSIGNED NOT NULL COMMENT '下单人ID', shipping_user VARCHAR (20) NOT NULL COMMENT '收货人姓名', PRIMARY KEY pk_orderid (order_id) ) ENGINE = INNODB COMMENT '订单主表'; insert into order_master(order_id,order_sn,password,age,position)values('1','20190845125425', '1' ,'张三'); insert into order_master(order_id,order_sn,password,age,position)values('2','20190845125426', '2' ,'李四'); commit; --全局表 CREATE TABLE region_info ( region_id SMALLINT NOT NULL AUTO_INCREMENT COMMENT '主键id', region_name VARCHAR (150) NOT NULL COMMENT '城市名称', PRIMARY KEY (region_id) ) ENGINE = INNODB COMMENT '地区信息表'; insert into region_info(region_name )values('乌鲁木齐'); insert into region_info(region_name )values('昌吉'); commit; --水平分片 CREATE TABLE shipping_info ( ship_id int UNSIGNED NOT NULL COMMENT '主键id', ship_name VARCHAR (20) NOT NULL COMMENT '物流公司名称', PRIMARY KEY pk_shipid (ship_id) ) ENGINE = INNODB COMMENT '物流公司信息表'; insert into shipping_info(ship_id,ship_name )values(0,'第1公司'); insert into shipping_info(ship_id,ship_name )values(1,'第1公司'); insert into shipping_info(ship_id,ship_name )values(2,'第2公司'); insert into shipping_info(ship_id,ship_name )values(3,'第3公司'); insert into shipping_info(ship_id,ship_name )values(4,'第4公司'); insert into shipping_info(ship_id,ship_name )values(5,'第5公司'); insert into shipping_info(ship_id,ship_name )values(6,'第6公司'); insert into shipping_info(ship_id,ship_name )values(7,'第7公司'); insert into shipping_info(ship_id,ship_name )values(8,'第8公司'); insert into shipping_info(ship_id,ship_name )values(9,'第9公司'); commit; --ER关系表 CREATE TABLE shipping_detail ( shipping_detail_id INT UNSIGNED NOT NULL COMMENT '物流公司详情表ID', ship_id INT UNSIGNED NOT NULL COMMENT '物流公司id', address VARCHAR (100) NOT NULL COMMENT '地址', PRIMARY KEY pk_orderdetailid (shipping_detail_id) ) ENGINE = INNODB COMMENT '物流公司详情表'; insert into shipping_detail(shipping_detail_id,ship_id,address )values('10','0', '乌鲁木齐'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('11','1', '昌吉'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('12','2', '石河子'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('13','3', '伊犁'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('14','4', '巴州'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('15','5', '阿克苏'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('16','6', '奎屯'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('17','7', '图木舒克'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('18','8', '五家渠'); insert into shipping_detail(shipping_detail_id,ship_id,address )values('19','9', '阿拉尔'); commit; CREATE TABLE goobal_master ( goobal_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID', order_status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态', PRIMARY KEY pk_orderid (goobal_id) ) ENGINE = INNODB COMMENT '全局自增表'; insert into goobal_master(order_status )values(0); insert into goobal_master(order_status )values(1); insert into goobal_master(order_status )values(2); insert into goobal_master(order_status )values(3); insert into goobal_master(order_status )values(4); insert into goobal_master(order_status )values(5); insert into goobal_master(order_status )values(6); insert into goobal_master(order_status )values(7); insert into goobal_master(order_status )values(8); insert into goobal_master(order_status )values(9); 192.168.0.3: create database customer_db; create user im_mycat@'172.25.66.%' identified by '123456'; grant select ,insert,update,delete on *.* to im_mycat@'172.25.66.%'; --垂直分库 create table customer_inf( customer_inf_id int not null comment '自增主键ID', customer_id int unsigned not null comment 'customer_login表的自增ID', customer_name varchar(20) not null comment '用户真实姓名', primary key pk_custoemrinfid (customer_inf_id) ) engine=innodb comment '用户信息表'; insert into customer_inf(customer_inf_id ,customer_id,customer_name)values('3','5','王五'); insert into customer_inf(customer_inf_id ,customer_id,customer_name)values('4','6','杨六'); commit; --全局表 CREATE TABLE region_info ( region_id SMALLINT NOT NULL AUTO_INCREMENT COMMENT '主键id', region_name VARCHAR (150) NOT NULL COMMENT '城市名称', PRIMARY KEY (region_id) ) ENGINE = INNODB COMMENT '地区信息表'; --水平分片 CREATE TABLE shipping_info ( ship_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id', ship_name VARCHAR (20) NOT NULL COMMENT '物流公司名称', ship_contact VARCHAR (20) NOT NULL COMMENT '物流公司联系人', PRIMARY KEY pk_shipid (ship_id) ) ENGINE = INNODB COMMENT '物流公司信息表';