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

一、前言

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

二、数据库结构示例

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

数据库db1

  • 只有一个t_student表,表中有db2中的t_school表的外键
「转」Mysql数据库分库后跨库join解决方案


数据库db2

  • 只有t_school表
「转」Mysql数据库分库后跨库join解决方案


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

就上面的考虑来看,我想要查询到这个学生的信息和所在学校的信息,如果t_student和t_school在一个库中,就可以通过join进行查询:

「转」Mysql数据库分库后跨库join解决方案


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

解决方案】

3.1 开启FEDERATED引擎

Mysql的常见的引擎类型有MyIsam,InnoDB等。并且我们可以通过show engines;命令来查看,数据库支持的所有的引擎。

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

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

my.cnf:

「转」Mysql数据库分库后跨库join解决方案


  • 重启mysql服务
「转」Mysql数据库分库后跨库join解决方案


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'
「转」Mysql数据库分库后跨库join解决方案


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

4.2 增加字段

alter table t_school add column idtest INT DEFAULT 0;
「转」Mysql数据库分库后跨库join解决方案


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

4.3 删除表

删除表

drop table t_school;

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

五、小结

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

您可能还会对下面的文章感兴趣:

最新评论

  1. 发布于:2019-10-05 20:51
    @Helenteeli:http://www.xevil.net
  2. 发布于:2019-10-05 12:55
    你好!. 我可以在哪里免费下载XEvil在您的网站? 从你的支持得到的信息。 XEvil确实是解决验证码的最佳方案,但我需要最新版本。 多謝。.