PostgreSQL使用MySQL作为外部表(mysql_fdw)

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 外部服务器对象：

• host：MySQL 服务器的地址或者主机名，默认为 127.0.0.1；
• port：MySQL 服务器的端口，默认为 3306；
• secure_auth：启用或者禁用安全认证，默认为 true；
• init_command：连接 MySQL 服务器之后执行的初始 SQL 语句；
• use_remote_estimate：是否执行远程 EXPLAIN 命令获取成本评估，默认为 false；
• reconnect：启用或者禁用自动重新连接功能，默认为 false；
• sql_mode：设置 MySQL sql_mode 变量，默认为 ANSI_QUOTES；
• ssl_key：客户端私钥文件名；
• ssl_cert：客户端公钥认证文件名；
• ssl_ca：证书授权（CA）认证文件名。如果使用该选项，必须和 MySQL 服务器的证书一致；
• ssl_capath：包含可信 SSL CA 认证文件的目录；
• ssl_cipher：SSL 加密允许的密码列表；
• fetch_size：指定每次读取的数据行数。该参数可以基于外部表或者外部服务器进行指定，基于外部表指定的参数优先级更高。默认为 100；
• character_set：MySQL 连接使用的字符集。默认为 auto，表示基于操作系统设置进行自动配置。在引入该选项之前，字符集设置为 PostgreSQL 数据库字符集. To get this 。如果想要兼容历史配置，可以将 character_set 设置为特殊值 PGDatabaseEncoding。

以下参数用于 MySQL 外部表对象：

• dbname：MySQL 数据库名，这是一个必填项；
• table_name：MySQL 表名，默认与 PostgreSQL 外部表同名；
• max_blob_size：不会被截断读取的最大 blob 大小；
• fetch_size：与外部服务器对象的 fetch_size 参数相同。

以下参数用于创建用户映射：

• username：连接 MySQL 服务器的用户名；
• password：连接 MySQL 服务器的密码。

以下参数用于 IMPORT FOREIGN SCHEMA 命令：

• import_default：导入外部表定义时是否包含字段的 DEFAULT 属性，默认为 false；
• import_not_null：导入外部表定义时是否包含字段的 NOT NULL 约束，默认为 true；
• import_enum_as_text：导入外部表定义时将 MySQL ENUM 类型映射为 PostgreSQL TEXT 类型，或者创建一个新的枚举类型并提示警告，默认为 false；
• import_generated：导入外部表定义时是否包含字段的 GENERATED 表达式，默认为 true。如果生成列表达式中使用了 PostgreSQL 中不存在的函数或者操作符，IMPORT 将会失败。

到此这篇关于PostgreSQL使用MySQL作为外部表（mysql_fdw）的文章就介绍到这了,更多相关PostgreSQL使用MySQL外表内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！