功能需求
首先要理解原始需求是什么,为什么要跨库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等语句,间接进行跨库操作啦