MyCat分表配置

前言

分库分表是两种感念
可以只分库，也可只分表

准备

环境准备：mysql 5.7、mycat 1.6.7.1-release
准备三个表结构相同的表：分别为user1、user2、user3

CREATE TABLE `user1` (
  `id` int(11) NOT NULL COMMENT '主键id',
  `name` varchar(32) DEFAULT NULL COMMENT '名称',
  `sex` char(1) DEFAULT NULL COMMENT '性别 0-女 1-男',
  `phone` varchar(32) DEFAULT NULL COMMENT '手机号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置

mycat主要配置文件schema.xml、server.xml、rule.xml
shcema.xml:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TEST_DB" checkSQLschema="false" sqlMaxLimit="100">
        <table name="user" primaryKey="id" dataNode="dn1" rule="rulel" subTables="user1,user$2-3" />
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="db_1" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="10.0.60.8:3306" user="root" password="teamway123456">
        </writeHost>
    </dataHost>
</mycat:schema>

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
       <property name="sequnceHandlerType">0</property>
    </system>
    <user name="test">
       <property name="password">test</property>
       <property name="schemas">TEST_DB</property>
    </user>
</mycat:server>

分片规则

rule.xml
范围分片：

<tableRule name="algorithm-long">
   <rule>
      <columns>id</columns>
      <algorithm>rang-long</algorithm>
   </rule>
</tableRule>
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
   <property name="mapFile">autopartition-long.txt</property>
   <property name="defaultNode">0</property>
</function>

分片枚举：

<tableRule name="sharding-by-intfile">
   <rule>
      <columns>type</columns>
      <algorithm>hash-int</algorithm>
   </rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
        <!-- 配置文件名称 -->
        <property name="mapFile">partition-hash-int.txt</property>
        <!-- type 默认值为 0，0 表示 Integer，非零表示 String -->
        <property name="type">1</property>
        <!-- 默认节点 -->
        <property name="defaultNode">2</property>
</function>