mycat单库分表

    单库分表功能要在mycat1.6版本以上，同库分表：所有的分表都在一个数据库中，由于数据库中表名不能重复，因此需要把数据表名起成不同的名字。将t_source 表按照月份分成12张表

  

1、server.xml配置,添加mycat登陆账户，mycat端口8066

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
     <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
	<property name="nonePasswordLogin">0</property> 
	<property name="useHandshakeV10">1</property>
    <!-- 1为开启实时统计、0为关闭 -->
	<property name="useSqlStat">0</property>  
     <!-- 1为开启全加班一致性检测、0为关闭 -->
	<property name="useGlobleTableCheck">0</property> 
    <property name="sequnceHandlerType">2</property>
   <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
	<property name="subqueryRelationshipCheck">false</property> 
    <property name="processorBufferPoolType">0</property>
		<!--默认是65535 64K 用于sql解析时最大文本长度 -->
    <property name="handleDistributedTransactions">0</property>
		<property name="useOffHeapForMerge">1</property>
        <property name="memoryPageSize">64k</property>
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<property name="systemReserveMemorySize">384m</property>
		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">false</property>
		<property name="strictTxIsolation">false</property>		
		<property name="useZKSwitch">true</property>	
	</system>
	
    <!--schemas与schema.xml的name一致-->
	<user name="root">
		<property name="password">123456</property>
		<property name="schemas">split_db</property>
	</user>

</mycat:server>

2、schema.xml配置，配置t_source的分片，物理数据库关联

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	
	<!-- name: 逻辑数据库名 -->      
    <schema  name="split_db" checkSQLschema="false" sqlMaxLimit="100">
	    <!-- 号源表单库分表   
		   <table  name="t_source" primaryKey="ID" autoIncrement="true"  subTables="t_source$1-3"  dataNode="dn1" rule="mod-long"/>
         --> 
        <!--  -->  
		<table  name="t_source" primaryKey="ID" subTables="$t_source$1-12"  dataNode="dn1"   rule="sharding-by-month"></table>
		
	</schema>
	
	<!-- database:物理数据库名 -->                                                                                   
    <dataNode name="dn1" dataHost="localhost1" database="split_db" /> 

    <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="192.168.1.164:3306" user="root" password="root">    
              <readHost host="hostS1" url="192.168.1.164:3306" user="root" password="root" /> 		              
        </writeHost>                                                                                             
    </dataHost>          

</mycat:schema>

3、rule.xml配置分表规则

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">

	<tableRule name="mod-long">
		<rule>
			<columns>id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
	</tableRule>
	
	
	<tableRule name="sharding-by-month">
		<rule>
			<columns>apply_date</columns>
			<algorithm>partbymonth</algorithm>
		</rule>
	</tableRule>
  
	<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<property name="count">3</property>
	</function>
	
	<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
		<property name="dateFormat">yyyy-MM-dd</property>
		<property name="sBeginDate">2018-08-01</property>
	</function>
	
</mycat:rule>

4、连接mycat