实现数据库的跨库join

功能需求

首先要理解原始需求是什么，为什么要跨库join。举个简单的例子，在日志数据库log_db有一份充值记录表pay_log，里面的用户信息只有一个userid；而用户的详细信息放在主库main_db，里面有用户的详细信息表user_info，如用户名、登录时间、注册时间、会员等级 等等。如果只是按用户名查找充值记录，那可以很简单地分两次查询即可。但是更复杂的需求，比如按注册时间排序，按会员等级统计等等，稍微复杂一点的功能，分次查询就相当难以做到了。如果在同一个库里，我们可以很方便的使用join关键字实现这些功能，但是在不同的数据库里，并没有跨库查询的SQL JOIN语句。那怎么办呢？

方案一、字段冗余。也就是说把一部分信息重复存放，比如 pay_log表里除了userid字段还有user_name字段，等等。但是这只适合加少量字段，如果把注册、登录时间、等级等等都字段都重复一遍，那是显然不科学的。

方案二、表复制和同步。也就是说把main_db里面的user_info表复制一份到log_db中，然后设置定时任务让这两个表进行同步。这样确实是可以在一个库里，然后可以进行 JOIN 等的复杂语句操作了。但是这两个表的内容是重复的，未免会比较浪费空间。

方案三、链接表。什么是链接表呢？简单来说，就是在log_db里有一个user_info表，但这个表并没有存储数据，而是直接链接到了 main_db里的user_info表。这样的话，我们可以既无需定期同步，又可以像在同一个库里使用JOIN等操作。

开启 FEDERATED 引擎

以MySQL为例，链接表与常见的 MyISAM, InnoDB 等等，都是一种表的结构类型（称之为 存储引擎）。使用  show engines;  命令即可看到数据库所支持的存储引擎，默认 FEDERATED 引擎 是关闭的，我们需要去mysql配置文件开启它。

开启的方法很简单，在windows下只需要在mysql的配置文件 my.ini 最末尾加上一句  federated ，然后重启mysql即可。重启后输入 show engines;命令即可看到 FEDERATED 的 Support 变为 YES，表示federated引擎已经开启。

新建链接表（federated引擎的表）

既然是链接的表，那么会有以下几个限制：

1.本地的表结构必须与远程的完全一样
2.远程数据库目前仅限MySQL
3.不支持事务
4.不支持表结构修改

建表的语句如下

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

当然不建议这样自己手工从头拼装SQL语句，可以把mian_db的user_info表的结构导出来，然后修改最后的半句，再导进去就行啦。建表语句示例如下：

CREATE TABLE `user_info` (
  `userid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(60) NOT NULL DEFAULT '' COMMENT '帐号',
  `level` int(11) NOT NULL DEFAULT '1' COMMENT '等级',
  `log_dt` timestamp NOT NULL DEFAULT '2016-11-30 00:00:00' COMMENT '最后登录时间',
  `reg_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`userid`),
  KEY `idx_name` (`username`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT CONNECTION='mysql://batsing:b8a7t4@localhost:3306/main_db/user_info'  COMMENT='用户基本信息表－链接表';

 

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