mysql_fdw: MySQL 的外部数据包装器

一月 3, 2024

摘要mysql_fdw是一个 PostgreSQL 扩展,它为 MySQL 实现了一个外部数据包装器(FDW)。

用法

可以在一个 MySQL 外部服务器对象上设置以下参数:

  • host:MySQL 服务器的地址或主机名。默认为127.0.0.1
  • port:MySQL服务器的端口号。默认为3306
  • secure_auth:启用或禁用安全身份验证。默认值为true
  • init_command:连接到 MySQL 服务器时要执行的SQL语句。
  • use_remote_estimate:控制 mysql_fdw 是否发出远程 EXPLAIN 命令来获取成本估算。默认值为false
  • reconnect:启用或禁用自动重连,如果发现现有连接已丢失,可以自动重连到 MySQL 服务器。默认值为false
  • sql_mode:为已建立的 MySQL 连接设置 sql_mode。默认值为ANSI_QUOTES
  • ssl_key:客户端私钥文件的路径名。
  • ssl_cert:客户端公钥证书文件的路径名。
  • ssl_ca:证书颁发机构(CA)证书文件的路径名。如果使用此选项,则必须指定服务器使用的相同证书。
  • ssl_capath:包含受信任的 SSL CA 证书文件的目录的路径名。
  • ssl_cipher:用于 SSL 加密的准许密码列表。
  • fetch_size:此选项指定 mysql_fdw 每次获取结果时抓取的行数。可以为一个外表或外部服务器指定它。在表上指定的选项可覆盖服务器指定的选项。默认值为100
  • character_set:用于 MySQL 连接的字符集。默认值auto表示基于操作系统设置进行自动检测。在引入 character_set 选项之前,字符集的设置类似于 PostgreSQL 数据库编码。要获得此旧行为,请将 character_set 设置为特殊值PGDatabaseEncoding

可以在一个 MySQL 外表对象上设置以下参数:

  • dbname:要查询的 MySQL 数据库的名称。这是一个必填选项。
  • table_name:MySQL 表的名称,默认与外表相同。
  • 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 类型映射到 TEXT 类型,否则会为要创建的类型发出一条警告。默认值为false
  • import_generated:从外部服务器导入外部表的定义时,此选项控制是否包含 GENERATED 列表达式。默认值为true。如果导入的生成表达式,使用了 PostgreSQL 上不存在的函数或运算符,则 IMPORT 将完全失败。

示例

-- load extension first time after install
CREATE EXTENSION mysql_fdw;

-- create server object
CREATE SERVER mysql_server
	FOREIGN DATA WRAPPER mysql_fdw
	OPTIONS (host '127.0.0.1', port '3306');

-- create user mapping
CREATE USER MAPPING FOR postgres
	SERVER mysql_server
	OPTIONS (username 'foo', password 'bar');

-- create foreign table
CREATE FOREIGN TABLE warehouse
	(
		warehouse_id int,
		warehouse_name text,
		warehouse_created timestamp
	)
	SERVER mysql_server
	OPTIONS (dbname 'db', table_name 'warehouse');

-- insert new rows in table
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 table
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 row from table
DELETE FROM warehouse where warehouse_id = 3;

-- update a row of table
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- explain a table with verbose option
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'))