04 按日期分片 思想5 按天分片， 按每年每月分片，按小时分片（字符串）

1. MyCat 分表分库分片项目实战水平分库-按日期天分片

 

规划计算：要分几个库

表里面是2010-2016的数据，7年 ，保留3年到2020年底，3个月一分片，11*12/3 = 44 ，所以分45个库

<function    name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">

<property name="dateFormat">yyyy-MM-dd</property> <!-- 日期格式-->

<property name="sBeginDate">2010-01-01</property> <!-- 开始日期-->

<property name="sEndDate">2020-12-31</property> <!-- 结束日期-->

<property name="sPartionDay">90</property> <!-- 每分片天数-->

</function>

 

 

create database itpuxdb01 default character set utf8;

create database itpuxdb02 default character set utf8;

create database itpuxdb03 default character set utf8;

create database itpuxdb04 default character set utf8;

create database itpuxdb05 default character set utf8;

create database itpuxdb06 default character set utf8;

create database itpuxdb07 default character set utf8;

create database itpuxdb08 default character set utf8;

create database itpuxdb09 default character set utf8;

create database itpuxdb10 default character set utf8;

create database itpuxdb11 default character set utf8;

create database itpuxdb12 default character set utf8;

create database itpuxdb13 default character set utf8;

create database itpuxdb14 default character set utf8;

create database itpuxdb15 default character set utf8;

 

 

create database itpuxdb16 default character set utf8;

create database itpuxdb17 default character set utf8;

create database itpuxdb18 default character set utf8;

create database itpuxdb19 default character set utf8;

create database itpuxdb20 default character set utf8;

create database itpuxdb21 default character set utf8;

create database itpuxdb22 default character set utf8;

create database itpuxdb23 default character set utf8;

create database itpuxdb24 default character set utf8;

create database itpuxdb25 default character set utf8;

create database itpuxdb26 default character set utf8;

create database itpuxdb27 default character set utf8;

create database itpuxdb28 default character set utf8;

create database itpuxdb29 default character set utf8;

create database itpuxdb30 default character set utf8;

 

 

create database itpuxdb31 default character set utf8;

create database itpuxdb32 default character set utf8;

create database itpuxdb33 default character set utf8;

create database itpuxdb34 default character set utf8;

create database itpuxdb35 default character set utf8;

create database itpuxdb36 default character set utf8;

create database itpuxdb37 default character set utf8;

create database itpuxdb38 default character set utf8;

create database itpuxdb39 default character set utf8;

create database itpuxdb40 default character set utf8;

create database itpuxdb41 default character set utf8;

create database itpuxdb42 default character set utf8;

create database itpuxdb43 default character set utf8;

create database itpuxdb44 default character set utf8;

create database itpuxdb45 default character set utf8;

 

vi schema.xml

 

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<!--  sqlMaxLimit="10000"       显示的多一点 -->

<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="10000">         

<table    name="itpux_member"    dataNode="dnitpuxdb$1-45" rule="sharding-by-date-joindate"></table>

</schema>

 

<!-- 设定数据结点 dnitpuxdb1-dnitpuxdb15 对应的 192.168.0.51 服务 以及对应的物理 schema    -->

<dataNode name="dnitpuxdb1" dataHost="192.168.0.51" database="itpuxdb01"

/>

<dataNode name="dnitpuxdb2" dataHost="192.168.0.51" database="itpuxdb02"

/>

<dataNode name="dnitpuxdb3" dataHost="192.168.0.51" database="itpuxdb03"

/>

<dataNode name="dnitpuxdb4" dataHost="192.168.0.51" database="itpuxdb04"

/>

<dataNode name="dnitpuxdb5" dataHost="192.168.0.51" database="itpuxdb05"

/>

<dataNode name="dnitpuxdb6" dataHost="192.168.0.51" database="itpuxdb06"

/>

<dataNode name="dnitpuxdb7" dataHost="192.168.0.51" database="itpuxdb07"

/>

<dataNode name="dnitpuxdb8" dataHost="192.168.0.51" database="itpuxdb08"

/>

<dataNode name="dnitpuxdb9" dataHost="192.168.0.51" database="itpuxdb09"/>

<dataNode    name="dnitpuxdb10"    dataHost="192.168.0.51" database="itpuxdb10" />

<dataNode    name="dnitpuxdb11"    dataHost="192.168.0.51" database="itpuxdb11" />

<dataNode    name="dnitpuxdb12"    dataHost="192.168.0.51" database="itpuxdb12" />

<dataNode    name="dnitpuxdb13"    dataHost="192.168.0.51" database="itpuxdb13" />

<dataNode    name="dnitpuxdb14"    dataHost="192.168.0.51" database="itpuxdb14" />

<dataNode    name="dnitpuxdb15"    dataHost="192.168.0.51" database="itpuxdb15" />

 

 

 

<!-- 设定数据结点 dnitpuxdb16-dnitpuxdb30 对应的 192.168.0.52 服务 以及对应的物理 schema    -->

<dataNode    name="dnitpuxdb16"    dataHost="192.168.0.52" database="itpuxdb16" />

<dataNode    name="dnitpuxdb17"    dataHost="192.168.0.52" database="itpuxdb17" />

<dataNode    name="dnitpuxdb18"    dataHost="192.168.0.52" database="itpuxdb18" />

<dataNode    name="dnitpuxdb19"    dataHost="192.168.0.52" database="itpuxdb19" />

<dataNode    name="dnitpuxdb20"    dataHost="192.168.0.52" database="itpuxdb20" />

<dataNode    name="dnitpuxdb21"    dataHost="192.168.0.52" database="itpuxdb21" />

<dataNode    name="dnitpuxdb22"    dataHost="192.168.0.52" database="itpuxdb22" />

<dataNode    name="dnitpuxdb23"    dataHost="192.168.0.52" database="itpuxdb23" />

<dataNode    name="dnitpuxdb24"    dataHost="192.168.0.52" database="itpuxdb24" />

<dataNode    name="dnitpuxdb25"    dataHost="192.168.0.52" database="itpuxdb25" />

<dataNode    name="dnitpuxdb26"    dataHost="192.168.0.52" database="itpuxdb26" />

<dataNode    name="dnitpuxdb27"    dataHost="192.168.0.52" database="itpuxdb27" />

<dataNode    name="dnitpuxdb28"    dataHost="192.168.0.52" database="itpuxdb28" />

<dataNode    name="dnitpuxdb29"    dataHost="192.168.0.52" database="itpuxdb29" />

<dataNode    name="dnitpuxdb30"    dataHost="192.168.0.52" database="itpuxdb30" />

 

 

 

<!-- 设定数据结点 dnitpuxdb31-dnitpuxdb45 对应的 192.168.0.53 服务 以及对应的物理 schema    -->

<dataNode    name="dnitpuxdb31"    dataHost="192.168.0.53" database="itpuxdb31" />

<dataNode    name="dnitpuxdb32"    dataHost="192.168.0.53" database="itpuxdb32" />

<dataNode    name="dnitpuxdb33"    dataHost="192.168.0.53" database="itpuxdb33" />

<dataNode    name="dnitpuxdb34"    dataHost="192.168.0.53" database="itpuxdb34" />

<dataNode    name="dnitpuxdb35"    dataHost="192.168.0.53" database="itpuxdb35" />

<dataNode    name="dnitpuxdb36"    dataHost="192.168.0.53" database="itpuxdb36" />

<dataNode    name="dnitpuxdb37"    dataHost="192.168.0.53" database="itpuxdb37" />

<dataNode    name="dnitpuxdb38"    dataHost="192.168.0.53" database="itpuxdb38" />

<dataNode    name="dnitpuxdb39"    dataHost="192.168.0.53" database="itpuxdb39" />

<dataNode    name="dnitpuxdb40"    dataHost="192.168.0.53" database="itpuxdb40" />

<dataNode    name="dnitpuxdb41"    dataHost="192.168.0.53" database="itpuxdb41" />

<dataNode    name="dnitpuxdb42"    dataHost="192.168.0.53" database="itpuxdb42" />

<dataNode    name="dnitpuxdb43"    dataHost="192.168.0.53" database="itpuxdb43" />

<dataNode    name="dnitpuxdb44"    dataHost="192.168.0.53" database="itpuxdb44" />

<dataNode    name="dnitpuxdb45"    dataHost="192.168.0.53" database="itpuxdb45" />

 

 

<dataHost name="192.168.0.51" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

<!-- can haVe multi write hosts -->

<writeHost  host="192.168.0.51"  url="192.168.0.51:3306"  user="root" password="root" />

</dataHost>

 

<dataHost name="192.168.0.52" maxCon="1000" minCon="10" balance="1" writeType