mycat同机多库分表

一个逻辑表分到一台主机的多个数据库中。

建表

在一台主机上见3个数据库，在三个数据库中分别建立TongPerson表

USE `tongjifenku1`;/*Table structure for table `TongPerson` */DROP TABLE IF EXISTS `TongPerson`;CREATE TABLE `TongPerson` (  `Id` int(11) NOT NULL,  `Name` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,  `UserId` int(11) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;USE `tongjifenku2`;/*Table structure for table `TongPerson` */DROP TABLE IF EXISTS `TongPerson`;CREATE TABLE `TongPerson` (  `Id` int(11) NOT NULL,  `Name` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,  `UserId` int(11) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;USE `tongjifenku3`;/*Table structure for table `TongPerson` */DROP TABLE IF EXISTS `TongPerson`;CREATE TABLE `TongPerson` (  `Id` int(11) NOT NULL,  `Name` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,  `UserId` int(11) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

配置server.xml

给root用增加逻辑库tongjifenbiao

<user name="root" defaultAccount="true">       <property name="password">123456</property>       <property name="schemas">Test1,dankufenbiao,tongjifenbiao</property></user>

配置scheam.xml

<schema name="tongjifenbiao" checkSQLschema="false" sqlMaxLimit="100">        <table name="TongPerson"  dataNode="dn3,dn4,dn5" rule="tongjifenbiao-mod-long" /></schema><dataNode name="dn3" dataHost="localhost3" database="tongjifenku1" /><dataNode name="dn4" dataHost="localhost3" database="tongjifenku2" /><dataNode name="dn5" dataHost="localhost3" database="tongjifenku3" /><dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql"          dbDriver="native" switchType="1" slaveThreshold="100">         <heartbeat>select user()</heartbeat>         <writeHost host="hostS4" url="192.168.101.149:3306" user="root" password="123456" /></dataHost>

配置rule.xml

<dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql"          dbDriver="native" switchType="1" slaveThreshold="100">         <heartbeat>select user()</heartbeat>         <writeHost host="hostS4" url="192.168.101.149:3306" user="root" password="123456" /></dataHost><function name="tongjifenbiao-mod-long" class="io.mycat.route.function.PartitionByMod">         <!-- how many data nodes -->        <property name="count">3</property></function>

验证

EXPLAIN INSERT INTO TongPerson (`Id`,`Name`,`UserId` ) VALUES(11,'2345623',11);

DATA_NODE SQL dn5 INSERT INTO TongPerson (Id,Name,UserId ) VALUES(11,’2345623’,11)

EXPLAIN INSERT INTO TongPerson (`Id`,`Name`,`UserId` ) VALUES(11,'2345623',12);

DATA_NODE SQL dn3 INSERT INTO TongPerson (Id,Name,UserId ) VALUES(11,’2345623’,12)

EXPLAIN INSERT INTO TongPerson (`Id`,`Name`,`UserId` ) VALUES(11,'2345623',13);

DATA_NODE SQL dn4 INSERT INTO TongPerson (Id,Name,UserId ) VALUES(11,’2345623’,13)

EXPLAIN select * from TongPerson 

DATA_NODE SQL dn3 SELECT * FROM TongPerson LIMIT 100 dn4 SELECT * FROM TongPerson LIMIT 100 dn5 SELECT * FROM TongPerson LIMIT 100