Mycat sharding rule sharding-by-date
1. rule. xml
- <TableRule name = "sharding-by-date-test">
- <Rule>
- <Columns> createtime </columns>
- <Algorithm> sharding-by-date </algorithm>
- </Rule>
- </TableRule>
- <Function name = "sharding-by-date" class = "io. mycat. route. function. PartitionByDate">
- <Property name = "dateFormat"> yyyy-MM-dd </property>
- <Property name = "sBeginDate"> 2016-01-01 </property>
- <Property name = "sPartionDay"> 10 </property>
- </Function>
The part date starts from January 1.
2. schema. xml
- <! DOCTYPE mycat: schema SYSTEM "schema. dtd">
- <Mycat: schema xmlns: mycat = "http://io.mycat/">
- <Schema name = "logdb" checkSQLschema = "false" sqlMaxLimit = "100">
- <Table name = "tb_log_t" primaryKey = "ID" autoIncrement = "true" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6 "rule =" mod-long "/>
- <Table name = "tb_user_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "sharding-by-intfile-provcode"/>
- <Table name = "tb_user_detail_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "auto-sharding-long-userid"/>
- <Table name = "tb_user_murmur_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "sharding-by-murmur"/>
- <Table name = "tb_user_murmur_string_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "sharding-by-murmur-userid"/>
- <Table name = "tb_user_crc32slot_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "crc32slot"/>
- <Table name = "tb_user_partbymonth_t" dataNode = "dn $1-13" rule = "sharding-by-month"/>
- <Table name = "tb_user_latestmonth_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "latest-month-calldate"/>
- <! -- <Table name = "t_subtable" primaryKey = "id" autoIncrement = "true" subTables = "t_subtable _ $ (20170730,20170806)" dataNode = "d
- N1 "/> -->
- <Table name = "tb_user_shardingbydate_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "sharding-by-date-test"/>
- </Schema>
- <Schema name = "coss03" checkSQLschema = "false" sqlMaxLimit = "100" dataNode = "dnoss03"/>
- <DataNode name = "dn1" dataHost = "localhost1" database = "log01"/>
- <DataNode name = "dn2" dataHost = "localhost1" database = "log02"/>
- <DataNode name = "dn3" dataHost = "localhost1" database = "log03"/>
- <DataNode name = "dn4" dataHost = "localhost1" database = "log04"/>
- <DataNode name = "dn5" dataHost = "localhost1" database = "log05"/>
- <DataNode name = "dn6" dataHost = "localhost1" database = "log06"/>
- <DataNode name = "dn7" dataHost = "localhost1" database = "log01"/>
- <DataNode name = "dn8" dataHost = "localhost1" database = "log02"/>
- <DataNode name = "dn9" dataHost = "localhost1" database = "log03"/>
- <DataNode name = "dn10" dataHost = "localhost1" database = "log04"/>
- <DataNode name = "dn11" dataHost = "localhost1" database = "log05"/>
- <DataNode name = "dn12" dataHost = "localhost1" database = "log06"/>
- <DataNode name = "dn13" dataHost = "localhost1" database = "log01"/>
- <DataNode name = "dnoss03" dataHost = "localhost1" database = "oss03"/>
- <DataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "0"
- WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1" slaveThreshold = "100">
- <Heartbeat> select user ()
- <! -- Can have multi write hosts -->
- <WriteHost host = "hostM1" url = "192.168.56.141: 3306" user = "root"
- Password = "mysql">
- <! -- Can have multi read hosts -->
- <ReadHost host = "hostS2" url = "192.168.56.142: 3306" user = "root" password = "mysql"/>
- </WriteHost>
- </DataHost>
- </Mycat: schema>
3. Create a table
Create table 'tb _ user_shardingbydate_t '(
'Id' varchar (32) not null,
'Name' varchar (64) default null,
'Createtime' varchar (10)
) ENGINE = InnoDB default charset = utf8;
Write Data:
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0001 ', 'name1', '2017-01-01 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0002', 'name1', '2017-01-02 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0003', 'name1', '2017-01-03 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0004', 'name1', '2017-01-04 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0005 ', 'name1', '2017-01-05 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0006', 'name1', '2017-01-06 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0007', 'name1', '2017-01-07 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0005 ', 'name1', '2017-01-08 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0006', 'name1', '2017-01-09 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0007', 'name1', '2017-01-10 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0001 ', 'name1', '2017-01-11 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0002', 'name1', '2017-01-12 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0003', 'name1', '2017-01-13 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0004', 'name1', '2017-01-14 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0005 ', 'name1', '2017-01-15 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0006', 'name1', '2017-01-16 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0007', 'name1', '2017-01-17 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0005 ', 'name1', '2017-01-18 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0006', 'name1', '2017-01-19 ');
Insert into tb_user_shardingbydate_t (id, name, createtime) values ('a0007', 'name1', '2017-01-20 ');
-- The End --