实现数据库的跨库join联表查询

PHP 同时被 2 个专栏收录
80 篇文章 0 订阅
10 篇文章 0 订阅

功能需求

首先要理解原始需求是什么,为什么要跨库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配置文件开启它。

实现数据库的跨库join

开启的方法很简单,在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等语句,间接进行跨库操作啦

  • 0
    点赞
  • 0
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值