概述

本文进行日期分片实验。

配置

分片路由：

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
    <table name="t_testtime" primaryKey="id" dataNode="node1,node2" rule="sharding-by-date" ></table>
</schema>

<dataNode name="node1" dataHost="host1" database="db1" />
<dataNode name="node2" dataHost="host2" database="db2" />

<!-- 物理机的 url -->
<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <writeHost host="server1" url="192.168.201.33:3306" user="root" password="123456"/>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <writeHost host="server2" url="192.168.201.35:3306" user="root" password="123456"/>
</dataHost>
</mycat:schema>

分片规则

<tableRule name="sharding-by-date">
                 <rule>
                         <columns>CREATE_TIME</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">2020-08-22</property> <!--开始日期-->
                <property name="sEndDate">2020-08-23</property>
                <property name="sPartionDay">1</property>  <!--每分片天数-->
        </function>

配置说明：

• columns ：标识将要分片的表字段
• algorithm ：分片函数
• dateFormat ：日期格式
• sBeginDate ：开始日期
• sEndDate：结束日期
• sPartionDay ：分区天数，即默认从开始日期算起，分隔1天一个分区

测试

MySQL [TESTDB]> insert into t_testtime(id, create_time) values(1, '2020-8-22');
Query OK, 1 row affected (0.13 sec)
//错误测试例子
MySQL [TESTDB]> insert into t_testtime(id, create_time) values(2, '2020-7-22'); 
ERROR 1064 (HY000): Can't find a valid data node for specified node index :T_TESTTIME -> CREATE_TIME -> 2020-7-22 -> Index : -31
//后面的日期竟然可以
MySQL [TESTDB]> insert into t_testtime(id, create_time) values(2, '2020-12-22');
Query OK, 1 row affected (0.01 sec)


MySQL [TESTDB]> select * from t_testtime;
+----+-------------+
| id | create_time |
+----+-------------+
|  1 | 2020-8-22   |
|  2 | 2020-12-22  |
+----+-------------+
2 rows in set (0.06 sec)

MySQL [TESTDB]> explain select * from t_testtime;
+-----------+------------------------------------+
| DATA_NODE | SQL                                |
+-----------+------------------------------------+
| node1     | SELECT * FROM t_testtime LIMIT 100 |
| node2     | SELECT * FROM t_testtime LIMIT 100 |
+-----------+------------------------------------+
2 rows in set (0.01 sec)

MariaDB [db1]> select * from t_testtime;
+----+-------------+
| id | create_time |
+----+-------------+
|  1 | 2020-8-22   |
|  2 | 2020-12-22  |
+----+-------------+

MariaDB [db2]> select * from t_testtime;
Empty set (0.00 sec)