一、求模分片规则(mod-long)
- 表中必须有名叫id的表头
- 根据id表头与设定数字取余的结果存储数据
余数是 0 数据存储到 dn1
余数是 1 数据存储到 dn2
余数是 2 数据存储到 dn3
二、相关配置
- schema.xml文件中设置mod-long规则
# 查看使用mod-long分片规则的表名：
[root@maxscale56 ~]# vim /usr/local/mycat/conf/schema.xml
<table name="hotnews" primaryKey="ID" autoIncrement="true"
dataNode="dn1,dn2,dn3" rule="mod-long" />
- rule.xml存储分片规则对应的 分片字段名 以及 求模的数值
# 设置取余计算的数字
# <colums>存储分片字段名
# <algorithm>存储算法规则
# <property>定义分片字段做求模计算的数字
[root@maxscale56 ~]# vim /usr/local/mycat/conf/rule.xml
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long"
class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
# 定义分片字段 做求模计算的数字（是数据库服务器的台数）
<property name="count">3</property>
</function>
三、示例：
# 客户端进入分片服务器
[root@host50 ~]# mysql -h192.168.88.60 -P8066 -uroot -p123456
# 进入虚拟库
mysql> use TESTDB;
mysql> create table hotnews(id int ,
title char(30),
comment varchar(150) ,
worker char(3)
);
# 插入数据
mysql> insert into hotnews(id,title,comment,worker)
values(9,"a","a","a");
mysql> insert into hotnews(id,title,comment,worker)
values(10,"b","a","a");
mysql> insert into hotnews(id,title,comment,worker)
values(11,"b","a","a");
mysql> insert into hotnews(id,title,comment,worker)
values(7,"b","a","a");
mysql> insert into hotnews(id,title,comment,worker)
values(1000,"d","a","a");
- 在数据库服务器查看数据(确认不同的数据根据不同的id值被分配到不同的数据库即为成功)
[root@host63 ~]# mysql -uroot -p123qqq...A -e 'select * from db1.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+--------+
| id | title | comment | worker |
+------+-------+---------+--------+
| 9 | a | a | a |
| 0 | d | a | a |
+------+-------+---------+--------+
[root@host64 ~]# mysql -uroot -p123qqq...A -e 'select * from db2.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+--------+
| id | title | comment | worker |
+------+-------+---------+--------+
| 10 | b | a | a |
| 1000 | d | a | a |
+------+-------+---------+--------+
[root@host65 ~]# mysql -uroot -p123qqq...A -e 'select * from db3.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+--------+
| id | title | comment | worker |
+------+-------+---------+--------+
| 11 | c | a | a |
+------+-------+---------+--------+
