MySql分片规则·枚举分片

一、枚举分片规则(sharding-by-intfile) ：

• 表里必须有名字叫sharding_id的表头
• 且表头的值必须在分片规则配置文件定义的值里选择。
• 配置规则文件：/usr/local/mycat/conf/partition-hash-int.txt

# 设置sharding_id表头的值
Mycat60~ ]# vim  /usr/local/mycat/conf/partition-hash-int.txt
10000=0      
10010=1     
10020=2      

• 分片字段值=第几台数据库服务器
  0对应dn1
  1对应dn2
  2对应dn3
• 10000,10010,10020虽然为自定义
  但是需要与存储sharding_id存储的数据对应

二、设置sharding-by-intfile分片规则的表

vim  /usr/local/mycat/conf/schema.xml  
 <table name="employee" primaryKey="ID" 
dataNode="dn1,dn2,dn3" rule="sharding-by-intfile" />

三、存储sharding-by-intfile分片规则数据

• 连接mycat服务建表并存储数据

[root@host50 ~]# mysql -h192.168.88.60 -P8066 -uroot -p123456        
mysql> use TESTDB;
mysql> create table employee(
     sharding_id int,
     name char(10), 
     addr char(20) 
);

• 插入数据记录

mysql> insert into employee(sharding_id,name,addr) 
values(10000,"bob","bj");

mysql> insert into employee(sharding_id,name,addr) 
values(10000,"bob2","bj");

mysql> insert into employee(sharding_id,name,addr) 
values(10010,"bob3","bj");

mysql> insert into employee(sharding_id,name,addr) 
values(10010,"tom","bj");

mysql> insert into employee(sharding_id,name,addr) 
values(10020,"ABC","bj");

mysql> insert into employee(sharding_id,name,addr) 
values(10020,"ABC","bj");