mycat 跨库间连表查询解决方案

川长思鸟来 2023-10-08 13:01 8阅读 0赞

#### 文章目录 ####

*  一、应对场景
 *  二、解决方案
 *  三、效果
 *  四、实现
 *   *  1. 过程概述
     *  2. 建库、建表、插数据、创建视图
     *  3. 配置 mycat 的 schema.xml
     *  4. 测试
     *   *  navicat直接测试
         *  java编程测试
 *  总结

--------------------

## 一、应对场景 ##

在微服务开发中，假设我们有两个服务，一个是作业系统，一个是admin用户权限系统，他们分别连接不同的库，里面有对应不同的业务表。

admin用户权限系统：  
![在这里插入图片描述][0c90c07529a73c6ed721657e9ddc730f.png]

作业系统数据库：  
![在这里插入图片描述][c07d5f7a5561da8ad2b5293c2b9f26f9.png]  
这时我们想查询出指定学校下的，所有学员提交的作业，并且里面带上作业详情。

这个操作必须要连表查询，但是表都在不同的数据库中，怎么解决？

## 二、解决方案 ##

Mycat中间件来做垂直分库

## 三、效果 ##

我们先看看效果，后面再看实现

这里我们用mycat连接上面的两台数据库，直接在mycat那里做的连表查询  
（只在mycat1.6.5版本及以上支持跨库连表，并且目前只支持两个表连接）

![在这里插入图片描述][f1eaa02fe9281687cd1eef07ef51a312.png]

如果我们还需要返回其他表的信息，也就是我们不止要连接两个表，我们是要跨库关联多个表查询。

这时我们可以在每个数据库本身创建视图，然后用mycat对两个视图跨库关联查询  
![在这里插入图片描述][c10df34fa4b12c974fb7e855aedfe830.png]

## 四、实现 ##

### 1. 过程概述 ###

*  首先我们每个服务有自己单独的数据库，里面的表都不一样
 *  然后我们用mycat对这些表进行配置垂直方向的分库
 *  对外我们的服务访问的db连接是mycat（他会自动分发你的增删改查请求）
 *  查询的sql语句如果是跨库的，必须在这条语句前面加上mycat指定的一句注释

### 2. 建库、建表、插数据、创建视图 ###

admin用户权限系统的 **testdb1**

create database testdb1;
    
    user testdb1;
    
    CREATE TABLE `organization`  (
      `id` bigint(20) NOT NULL,
      `name` varchar(255) DEFAULT NULL COMMENT '机构名称',
      PRIMARY KEY (`id`) USING BTREE
    );
    CREATE TABLE `user`  (
      `id` bigint(20) NOT NULL,
      `username` varchar(255) DEFAULT NULL COMMENT '用户名',
      `password` varchar(255) DEFAULT NULL COMMENT '密码',
      `full_name` varchar(255) DEFAULT NULL COMMENT '中文名（全名）',
      `organization_id` bigint(20) DEFAULT NULL COMMENT '机构id',
      PRIMARY KEY (`id`) USING BTREE
    );
    
    INSERT INTO `organization` VALUES (1, '茂名市第一中学');
    INSERT INTO `user` VALUES (1, '1159140456@qq.com', '123456', '李启业', 1);
    
    create view v_usermsg as (
    	select a.*,b.name as org_name from user a left join organization b on a.organization_id = b.id
    );

作业系统的 **testdb2**

create database testdb2;
    
    user testdb2;
    
    CREATE TABLE `homework`  (
      `id` bigint(20) NOT NULL,
      `name` varchar(255)  DEFAULT NULL COMMENT '作业名',
      PRIMARY KEY (`id`) USING BTREE
    );
    CREATE TABLE `homework_user`  (
      `homework_id` bigint(20) NOT NULL COMMENT '作业id',
      `user_id` bigint(20) NOT NULL COMMENT '用户id',
      PRIMARY KEY (`homework_id`, `user_id`) USING BTREE
    );
    
    INSERT INTO `homework` VALUES (1, '一班的周五语文作业');
    INSERT INTO `homework_user` VALUES (1, 1);
    
    create view v_homework_user as (
    	select a.*,b.name as homework_name from homework_user a left join homework b on a.homework_id = b.id
    );

我这里创建视图是因为：mycat最多只支持跨库的两个表的关联查询，想多个表我这里是通过视图

### 3. 配置 mycat 的 schema.xml ###

<mycat:schema xmlns:mycat="http://io.mycat/">
    
    	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    		<table name="user" dataNode="dn1"></table>
    		<table name="organization" dataNode="dn1"></table>
    		<table name="homework" dataNode="dn2"></table>
    		<table name="homework_user" dataNode="dn2"></table>
    		<table name="v_usermsg" dataNode="dn1"></table>
    		<table name="v_homework_user" dataNode="dn2"></table>
    	</schema>
    	
    	<dataNode name="dn1" dataHost="host1" database="testdb1" />
    	<dataNode name="dn2" dataHost="host2" database="testdb2" />
    
    	<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
    			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM1" url="localhost:3306" user="root"
    				   password="123456">
    		</writeHost>
    	</dataHost>
    	
    	<dataHost name="host2" 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.89.176:3306" user="root"
    				   password="root">
    		</writeHost>
    	</dataHost>
    	
    </mycat:schema>

到这里就已经好了，启动mycat ！

### 4. 测试 ###

#### navicat直接测试 ####

注意：必须带上注释！

/*!mycat:catlet=io.mycat.catlets.ShareJoin */ 
    select * from homework_user a left join user b on a.user_id = b.id;
    
    /*!mycat:catlet=io.mycat.catlets.ShareJoin */ 
    select * from v_homework_user a left join v_usermsg b on a.user_id = b.id;

![在这里插入图片描述][784f4cdff1e01d6127355eb4a060407a.png]  
![在这里插入图片描述][6d6a9923214856ad02ec15c2cd47ed1b.png]

#### java编程测试 ####

我这里用的 mybatis-plus ，直接截图带过  
![在这里插入图片描述][d623ffca3c66a4422dff0e9f3b802442.png]  
![在这里插入图片描述][2fa55c518f35ffa615209eedcb99925b.png]

![在这里插入图片描述][0c61fc95e578d30b5a8136fa367845b2.png]  
![在这里插入图片描述][53afddedce1ddf41a9e2ee4c3169e2e3.png]

--------------------

## 总结 ##

欢迎指出我的错误！

[0c90c07529a73c6ed721657e9ddc730f.png]: https://img-blog.csdnimg.cn/img_convert/0c90c07529a73c6ed721657e9ddc730f.png
[c07d5f7a5561da8ad2b5293c2b9f26f9.png]: https://img-blog.csdnimg.cn/img_convert/c07d5f7a5561da8ad2b5293c2b9f26f9.png
[f1eaa02fe9281687cd1eef07ef51a312.png]: https://img-blog.csdnimg.cn/img_convert/f1eaa02fe9281687cd1eef07ef51a312.png
[c10df34fa4b12c974fb7e855aedfe830.png]: https://img-blog.csdnimg.cn/img_convert/c10df34fa4b12c974fb7e855aedfe830.png
[784f4cdff1e01d6127355eb4a060407a.png]: https://img-blog.csdnimg.cn/img_convert/784f4cdff1e01d6127355eb4a060407a.png
[6d6a9923214856ad02ec15c2cd47ed1b.png]: https://img-blog.csdnimg.cn/img_convert/6d6a9923214856ad02ec15c2cd47ed1b.png
[d623ffca3c66a4422dff0e9f3b802442.png]: https://img-blog.csdnimg.cn/img_convert/d623ffca3c66a4422dff0e9f3b802442.png
[2fa55c518f35ffa615209eedcb99925b.png]: https://img-blog.csdnimg.cn/img_convert/2fa55c518f35ffa615209eedcb99925b.png
[0c61fc95e578d30b5a8136fa367845b2.png]: https://img-blog.csdnimg.cn/img_convert/0c61fc95e578d30b5a8136fa367845b2.png
[53afddedce1ddf41a9e2ee4c3169e2e3.png]: https://img-blog.csdnimg.cn/img_convert/53afddedce1ddf41a9e2ee4c3169e2e3.png