mycat单库分表

mycat是国内著名的java后端开源中间件，工作有点类似于nginx，大致就是拦截原始sql，通过配置的分库分表规则处理sql语句，使得业务层无需考虑数据库和表的

拆分逻辑。

以下是sprinboot, mybatis,mysql,mycat实现的单库分表，mycat版本:1.6.5-release,os:windows10,mysql大小写不敏感！

数据脚本(page_0~9 分表为page_0  ...  page_9总共10张表):

CREATE TABLE `page_0~9`  (
  `uid` int(11) NULL DEFAULT NULL,
  `id` int(11) NULL DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `url` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

schema配置（单库，10表）

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

    <schema name="auditbus" checkSQLschema="true" sqlMaxLimit="100">
        <!-- auto sharding by id (long) -->
        <table name="page" subTables="page_$0-9" dataNode="dn1" rule="mod-long"></table>
    </schema>
    <dataNode name="dn1" dataHost="datahost1" database="auditbus" />
    <dataHost name="datahost1" maxCon="100" minCon="4" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="tan1997">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="127.0.0.1:3306" user="root" password="tan1997"/>
        </writeHost>
    </dataHost>
</mycat:schema>

server.xml，配置访问认证，端口等

<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
        <property name="defaultSqlParser">druidparser</property>
        <property name="serverPort">8066</property>
    </system>
    <user name="root">
        <property name="password">pwd</property>
        <property name="schemas">auditbus</property>
    </user>
</mycat:server>

rule.xml，配置分表规则

<tableRule name="mod-long">
        <rule>
            <columns>uid</columns>
            <algorithm>mod-long</algorithm>
        </rule>
</tableRule>


<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes or subTables -->
        <property name="count">10</property>
</function>

示例所用mycat+配置：https://download.csdn.net/download/qq_41633199/14929669

springboot配置（数据库地址写mycat的，其余不需要修改）:

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      url: jdbc:mysql://localhost:8066/auditbus?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: pwd
    name: auditbus
  http:
    encoding:
      charset: UTF-8

mybatis:
  mapper-locations: classpath:/mybatis/mybatis-*.xml

业务应用这边需要注意mycat与mysql连接jar包的兼容性问题，笔者之前使用8.X.X的版本导致无法连接mycat，后来切换到如下版本才得以连上:

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.34</version>
            <scope>runtime</scope>
</dependency>

mycat可能会出现表名大小写问题导致schema找不到的bug，这点需要注意，笔者使用的mycat版本会将表名在代码中转成大写，因此mysql要是表名大小写敏感的话需要留意下。