PostgreSQL 提供了一种访问和操作外部数据源的机制,称为外部数据包装器(Foreign Data Wrapper)。利用这种外部数据管理机制,我们可以在 PostgreSQL 中访问各种同构数据库(PostgreSQL)、异构数据库(MySQL、Oracle、SQL Server、SQLite、HBase、Cassandra、ClickHouse、CouchDB、MongoDB、Neo4j、Redis、Hadoop、HIve等)以及文本文件(CSV、JSON、XML等)。
本文我们要介绍的就是连接 MySQL 数据库的外部数据包装器:mysql_fdw。mysql_fdw 提供了读写 MySQL 外部表、连接池、WHERE 条件下推、返回字段下推、预编译语句、JOIN 下推、聚合函数(min、max、sum、avg、count)下推、ORDER BY 下推以及 LIMIT OFFSET 下推等功能。
点击 GitHub 下载 mysql_fdw 源代码。
编译 mysql_fdw 之前,需要安装 MySQL C 语言客户端库。这个程序库可以从 MySQL 官方网站下载。
对于 POSIX 兼容系统,运行 make 进行编译时需要确保 pg_config 程序位于 path 环境变量中。该程序通常位于 PostgreSQL 安装目录下的 bin 目录中,例如:
$ export PATH=/usr/local/pgsql/bin/:$PATH
另外,mysql_config 程序页需要位于 path 环境变量中:
$ export PATH=/usr/local/mysql/bin/:$PATH
然后,使用 make 命令编译程序:
$ make USE_PGXS=1
最后,安装 mysql_fdw:
$ make USE_PGXS=1 install
运行回归测试:
$ make USE_PGXS=1 installcheck
注意确保 MYSQL_HOST、MYSQL_PORT、MYSQL_USER_NAME 以及 MYSQL_PWD 环境变量的设置。默认设置可以参考 mysql_init.sh 脚本。
以下是一个简单的使用示例,所有命令在 PoatgreSQL 中执行:
-- 加载扩展插件 CREATE EXTENSION mysql_fdw; -- 创建服务器对象 CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306'); -- 创建用户映射 CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'foo', password 'bar'); -- 创建外部表 CREATE FOREIGN TABLE warehouse ( warehouse_id int, warehouse_name text, warehouse_created timestamp ) SERVER mysql_server OPTIONS (dbname 'db', table_name 'warehouse'); -- 插入数据 INSERT INTO warehouse values (1, 'UPS', current_date); INSERT INTO warehouse values (2, 'TV', current_date); INSERT INTO warehouse values (3, 'Table', current_date); -- 查询数据 SELECT * FROM warehouse ORDER BY 1; warehouse_id | warehouse_name | warehouse_created -------------+----------------+------------------- 1 | UPS | 10-JUL-20 00:00:00 2 | TV | 10-JUL-20 00:00:00 3 | Table | 10-JUL-20 00:00:00 -- 删除数据 DELETE FROM warehouse where warehouse_id = 3; -- 更新数据 UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1; -- 查看执行计划 EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Limit (cost=10.00..11.00 rows=1 width=36) Output: warehouse_id, warehouse_name -> Foreign Scan on public.warehouse (cost=10.00..1010.00 rows=1000 width=36) Output: warehouse_id, warehouse_name Local server startup cost: 10 Remote query: SELECT `warehouse_id`, `warehouse_name` FROM `db`.`warehouse` WHERE ((`warehouse_name` LIKE BINARY 'TV'))
以下参数用于 MySQL 外部服务器对象:
以下参数用于 MySQL 外部表对象:
以下参数用于创建用户映射:
以下参数用于 IMPORT FOREIGN SCHEMA 命令: