mysql使用Federated引擎实现跨库联表查询

本文地址:http://www.04007.cn/article/591.html

    今天有一个需求是要根据一批指定的ID数据(百万级，存在一个txt文件中，一行一个ID值)从线上数据库表(亿级)上拉下对应表相关几个字段的内容，于是开始想用什么办法来实现比较好，最后想着使用mysql的跨库联表查询修改。Mysql跨库查询需要用到FEDERATED引擎，所以需要先检查已安装的MYSQL是否支持Federated引擎。通过show engines命令查看是否有FEDERATED这行信息，如果没有这行，则需要重新安装MYSQL(一般都会有)。如果有FEDERATED引擎，但Support是NO，mysql已安装FEDERATED引擎但没有启用，修改my.cnf在mysqld这项配置中 federated，重启mysql即可显示如下信息：本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

如下为在my.cnf中添加federated并重启后显示的信息：也明确显示了不支持事务。本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

    FEDERATED官方简介:FEDERATED存储引擎访问在远程数据库的表中的数据，而不是本地的表。这个特性给某些开发应用带来了遍历，你可以直接在本地构建一个federated表来连接远程数据表，配置好了之后本地表的数据可以直接跟远程数据表同步。实际上这个引擎里面是不真实存放数据的，所需要的数据都是连接到其他MySQL服务器上。 本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

MYSQL使用federated引擎建表语句格式：本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

#show engines;
#注意填写账号数据后以下的[]号不需要
CREATE TABLE `table_name`(......) ENGINE =FEDERATED CONNECTION='mysql://[username]:[password]@[location]:[port]/[数据库名称]/[表名]'
#DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(40) NOT NULL DEFAULT '' COMMENT '用户名',
  PRIMARY KEY (`uid`)
 #只保留这行PRIMARY KEY，后面的KEY设置都不需要(如下的name就不需要)，不然数据查询不能成功
 # KEY `name` (`name`),
) ENGINE=FEDERATED CONNECTION='mysql://read:aaaaa@192.168.10.10:3306/userdb/user'

    通过FEDERATED引擎创建的表只是在本地有张空表，数据文件存在远程数据库中，需要注意的是，在使用建表语句建表的时候，除字段数据外只需要保留这行PRIMARY KEY，后面的KEY设置都不需要(如上面的name字段索引就不需要），不然数据查询不能成功。表建好之后，可以使用联表查询几行记录看看是否能查到数据。然后便可以使用SQL语句进行数据修改：本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

比如我这里的需求：根据两表的相同ID字段将另一张大表的字段值写入此表
UPDATE users_temp t LEFT JOIN user_all a ON t.id = a.id SET t.name= a.name;本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

其它在update 查询时可以执行不带join,带join,right join 等方式可以按需求使用
UPDATE users_temp t user_all a ON t.id = a.id SET t.name= a.name;
UPDATE users_temp t right join user_all a ON t.id = a.id SET t.name= a.name .... ;本文地址:http://www.04007.cn/article/591.html,未经许可,不得转载.

本文地址:http://www.04007.cn/article/591.html 未经许可,不得转载. 手机访问本页请扫描下方二维码：