MySQL: 8.0.28

ShardinSphere-Proxy: 5.3.0

0. 前置条件

1. 完成 Proxy 安装部署，lib 路径下添加 MySQL 驱动包后启动服务
2. 两个 MySQL 数据库中分别创建 mysql_0 和 mysql_1 数据库

• mysql_0

mysql> create database mysql_0;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_0            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

• mysql_1

mysql> create database mysql_1;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_1            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

1. 绑定表操作过程

创建逻辑库

mysql -uroot -proot -P3307
create database testdb;
use testdb;

注册数据源

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.56.103:3306/mysql_0?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="Test@123"
), ds_1 (
    URL="jdbc:mysql://192.168.56.104:3306/mysql_1?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="Test@123"
);

SHOW STORAGE UNITS;

创建分片规则

CREATE SHARDING TABLE RULE t_order (
  STORAGE_UNITS(ds_0,ds_1),
  SHARDING_COLUMN=order_id,
  TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4"))
);

SHOW SHARDING TABLE RULE t_order\G

CREATE SHARDING TABLE RULE t_order_item (
  STORAGE_UNITS(ds_0,ds_1),
  SHARDING_COLUMN=order_id,
  TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4"))
);

SHOW SHARDING TABLE RULE t_order_item\G

建表

CREATE TABLE `t_order` (
 `order_id` int NOT NULL,
 `status` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`order_id`)
);

CREATE TABLE `t_order_item` (
 `order_id` int NOT NULL,
 `status` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`order_id`)
);

创建表关联

CREATE SHARDING TABLE REFERENCE RULE ref_0 (t_order,t_order_item);

SHOW SHARDING TABLE REFERENCE RULES;

2. 复合行表达式

创建规则

CREATE SHARDING TABLE RULE t_order_inline (
  DATANODES("ds_${0..1}.t_order_inline${0..1}"),
  TABLE_STRATEGY(TYPE=COMPLEX,SHARDING_COLUMNS=user_id,order_id,
    SHARDING_ALGORITHM(TYPE(NAME=COMPLEX_INLINE,
      PROPERTIES("algorithm-expression"="t_order_${user_id % 2}_${order_id % 2}"))))
);

SHOW SHARDING TABLE RULE t_order_inline\G

创建表

CREATE TABLE `t_order_inline` (
 `user_id` int NOT NULL,
 `order_id` int NOT NULL,
 `status` varchar(45) DEFAULT NULL
);