利用sharding-proxy实现分库分表
文章目录
一、启动一个mysql
在上面创建两个库:ds_shop_1
和 ds_shop_2
,真实的环境中,应当使用两个mysql服务。
create schema ds_shop_1;
create schema ds_shop_2;
CREATE USER 'shoper'@'%' IDENTIFIED BY 'shoper^pw';
GRANT ALL ON ds_shop_1.* TO 'shoper'@'%';
GRANT ALL ON ds_shop_2.* TO 'shoper'@'%';
flush privileges;
分库分表的表结构:
CREATE TABLE ds_shop_1.`t_order_1` (
`o_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键UUID',
`p_code` varchar(125) NOT NULL COMMENT '订单编号',
`P_num` int(11) NOT NULL COMMENT '数量',
`total_price` decimal(7,2) NOT NULL COMMENT '总价',
`create_time` bigint(20) NOT NULL COMMENT '创建时间',
`update_time` bigint(20) NOT NULL COMMENT '更新时间',
`o_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 代付款, 1 已付款, 2 删除'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
CREATE TABLE ds_shop_1.`t_order_2` (
`o_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键UUID',
`p_code` varchar(125) NOT NULL COMMENT '订单编号',
`P_num` int(11) NOT NULL COMMENT '数量',
`total_price` decimal(7,2) NOT NULL COMMENT '总价',
`create_time` bigint(20) NOT NULL COMMENT '创建时间',
`update_time` bigint(20) NOT NULL COMMENT '更新时间',
`o_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 代付款, 1 已付款, 2 删除'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
CREATE TABLE ds_shop_1.`t_order_3` (
`o_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键UUID',
`p_code` varchar(125) NOT NULL COMMENT '订单编号',
`P_num` int(11) NOT NULL COMMENT '数量',
`total_price` decimal(7,2) NOT NULL COMMENT '总价',
`create_time` bigint(20) NOT NULL COMMENT '创建时间',
`update_time` bigint(20) NOT NULL COMMENT '更新时间',
`o_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 代付款, 1 已付款, 2 删除'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
CREATE TABLE ds_shop_2.`t_order_1` (
`o_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键UUID',
`p_code` varchar(125) NOT NULL COMMENT '订单编号',
`P_num` int(11) NOT NULL COMMENT '数量',
`total_price` decimal(7,2) NOT NULL COMMENT '总价',
`create_time` bigint(20) NOT NULL COMMENT '创建时间',
`update_time` bigint(20) NOT NULL COMMENT '更新时间',
`o_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 代付款, 1 已付款, 2 删除'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
CREATE TABLE ds_shop_2.`t_order_2` (
`o_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键UUID',
`p_code` varchar(125) NOT NULL COMMENT '订单编号',
`P_num` int(11) NOT NULL COMMENT '数量',
`total_price` decimal(7,2) NOT NULL COMMENT '总价',
`create_time` bigint(20) NOT NULL COMMENT '创建时间',
`update_time` bigint(20) NOT NULL COMMENT '更新时间',
`o_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 代付款, 1 已付款, 2 删除'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
CREATE TABLE ds_shop_2.`t_order_3` (
`o_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键UUID',
`p_code` varchar(125) NOT NULL COMMENT '订单编号',
`P_num` int(11) NOT NULL COMMENT '数量',
`total_price` decimal(7,2) NOT NULL COMMENT '总价',
`create_time` bigint(20) NOT NULL COMMENT '创建时间',
`update_time` bigint(20) NOT NULL COMMENT '更新时间',
`o_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 代付款, 1 已付款, 2 删除'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
二、在sharding-proxy上面配置分库、分表的规则
在sharding-proxy上面配置分库分表的规则:
schemaName: sharding_db
dataSources:
ds_shop_1:
url: jdbc:mysql://127.0.0.1:3307/ds_shop_1?useUnicode=true&characterEncoding=utf8&useSSL=false
username: shoper
password: shoper^pw
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize