【Mysql】数据库分库后跨库join解决方案

港控/mmm° 2022-06-02 05:17 387阅读 0赞

# 一、前言 #

近几个月项目中数据库开始分库了，把一个原本60多张表的数据库分成了6个部分，每个部分有自己业务之内的表，整个数据库只是物理分开，逻辑依然是链接的，通过mycat这个数据库中间件对整个数据库进行路由转发。但是分库后随之而来的问题之一就是跨库join，可以说网上的解决方案已经烂大街了。但是小编还是觉得有必要总结分析一下。

# 二、数据库结构示例 #

为了公司数据保密，小编建立两个新的数据库，来做这个实验。

![这里写图片描述][SouthEast]

*  数据库db1  
    
    
     *  只有一个`t_student`表，表中有db2中的`t_school`表的外键

![这里写图片描述][SouthEast 1]

*  数据库db2
    
     *  只有`t_school`表

![这里写图片描述][SouthEast 2]

# 三、如何进行跨库连表查询呢？ #

就上面的考虑来看，我想要查询到这个学生的信息和所在学校的信息，如果t\_student和t\_school在一个库中，就可以通过join进行查询：

SELECT * from t_student s1 LEFT JOIN t_school s2 on s2.id = s1.school_id where s1.id='1'

但是，现在两张表不在一个数据库中，如何解决呢？有没有跨库查询SQL Join的语句呢？

## 【解决方案】 ##

## 3.1 开启FEDERATED引擎 ##

在前面的博客中，小编分享过，Mysql的常见的引擎类型有MyIsam，InnoDB等。并且我们可以通过`show engines;`命令来查看，数据库支持的所有的引擎。

如果要想实现跨库Join查询，我们可以使用FEDERATED引擎来实现。但是默认情况下，FEDERATED引擎是关闭的，所以就需要我们去my.cnf的配置文件中进行相关的配置。

*  在my.cnf 文件中添加federated，然后重启启动mysql服务

my.cnf:

![这里写图片描述][SouthEast 3]

*  重启mysql服务

service mysqld restart

## 3.2 建立链接表 ##

限制：

1.本地结构字段可以是远程表的部分字段，字段必须相同

2.不支持事务

3.不支持表结构修改

建表语句格式：

CREATE TABLE xxx(...) ENGINE=FEDERATED CONNECTION='mysql://[name]:[password]@[location]:[port]/[db-name]/[table-name]'

说明：  
      name：远程数据库账号  
      password：远程数据库密码  
      location：远程数据库Ip地址  
      port：数据库端口  
      db-name：数据库名字  
      table-name：表名

【例子】  
      建立链接t\_school表：

CREATE TABLE `t_school` ( `id` varchar(22) NOT NULL, `school_name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COMMENT='学校表－链接表' CONNECTION='mysql://root:root@192.168.22.177:3306/db2/t_school';

经过这些步骤，我们就把另外一个库的表，链接到了我们操作的库上了。这样就可以使用JOIN等语句，间接进行跨库操作啦

# 四、一些操作 #

## 4.1 更新本地链接表 ##

update t_school set school_name ='langfang' where id ='1'

![这里写图片描述][SouthEast 4]

对本地进行更新操作，本地和远程数据库都改变了。

## 4.2 增加字段 ##

alter table t_school add column idtest INT DEFAULT 0;

![这里写图片描述][SouthEast 5]

错误：federated存储引擎不支持alter table 操作

## 4.3 删除表 ##

删除表

drop table t_school;

删除本地表对远程表无影响

## 五、小结 ##

数据库的操作还是有很多的，有问题就解决问题，操作就是这样。加油！

[SouthEast]: /images/20220602/3351aaa88ab04353bd8a59e8a8fa07d4.png
[SouthEast 1]: /images/20220602/cee9c3f472e646c2834d815eface1645.png
[SouthEast 2]: /images/20220602/f38f76a303114d5285a3f23ecf95f8a3.png
[SouthEast 3]: /images/20220602/37844df278b447bdb31d444e7b665624.png
[SouthEast 4]: /images/20220602/f03d8f897ee148cda374d45806f8c9d6.png
[SouthEast 5]: /images/20220602/40b53fe5f1db426f879fb7cd12660993.png