PostgreSQL 提供了一種訪問(wèn)和操作外部數(shù)據(jù)源的機(jī)制，稱為外部數(shù)據(jù)包裝器（Foreign Data Wrapper）。利用這種外部數(shù)據(jù)管理機(jī)制，我們可以在 PostgreSQL 中訪問(wèn)各種同構(gòu)數(shù)據(jù)庫(kù)（PostgreSQL）、異構(gòu)數(shù)據(jù)庫(kù)（MySQL、Oracle、SQL Server、SQLite、HBase、Cassandra、ClickHouse、CouchDB、MongoDB、Neo4j、Redis、Hadoop、HIve等）以及文本文件（CSV、JSON、XML等）。

本文我們要介紹的就是連接 MySQL 數(shù)據(jù)庫(kù)的外部數(shù)據(jù)包裝器：mysql_fdw。mysql_fdw 提供了讀寫(xiě) MySQL 外部表、連接池、WHERE 條件下推、返回字段下推、預(yù)編譯語(yǔ)句、JOIN 下推、聚合函數(shù)（min、max、sum、avg、count）下推、ORDER BY 下推以及 LIMIT OFFSET 下推等功能。

下載安裝

點(diǎn)擊 GitHub 下載 mysql_fdw 源代碼。

編譯 mysql_fdw 之前，需要安裝 MySQL C 語(yǔ)言客戶端庫(kù)。這個(gè)程序庫(kù)可以從 MySQL 官方網(wǎng)站下載。

對(duì)于 POSIX 兼容系統(tǒng)，運(yùn)行 make 進(jìn)行編譯時(shí)需要確保 pg_config 程序位于 path 環(huán)境變量中。該程序通常位于 PostgreSQL 安裝目錄下的 bin 目錄中，例如：

$ export PATH=/usr/local/pgsql/bin/:$PATH

另外，mysql_config 程序頁(yè)需要位于 path 環(huán)境變量中：

$ export PATH=/usr/local/mysql/bin/:$PATH

然后，使用 make 命令編譯程序：

$ make USE_PGXS=1

最后，安裝 mysql_fdw：

$ make USE_PGXS=1 install

運(yùn)行回歸測(cè)試：

$ make USE_PGXS=1 installcheck

注意確保 MYSQL_HOST、MYSQL_PORT、MYSQL_USER_NAME 以及 MYSQL_PWD 環(huán)境變量的設(shè)置。默認(rèn)設(shè)置可以參考 mysql_init.sh 腳本。

使用示例

以下是一個(gè)簡(jiǎn)單的使用示例，所有命令在 PoatgreSQL 中執(zhí)行：

-- 加載擴(kuò)展插件
CREATE EXTENSION mysql_fdw;

-- 創(chuàng)建服務(wù)器對(duì)象
CREATE SERVER mysql_server
	FOREIGN DATA WRAPPER mysql_fdw
	OPTIONS (host '127.0.0.1', port '3306');

-- 創(chuàng)建用戶映射
CREATE USER MAPPING FOR postgres
	SERVER mysql_server
	OPTIONS (username 'foo', password 'bar');

-- 創(chuàng)建外部表
CREATE FOREIGN TABLE warehouse
	(
		warehouse_id int,
		warehouse_name text,
		warehouse_created timestamp
	)
	SERVER mysql_server
	OPTIONS (dbname 'db', table_name 'warehouse');

-- 插入數(shù)據(jù)
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);

-- 查詢數(shù)據(jù)
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

-- 刪除數(shù)據(jù)
DELETE FROM warehouse where warehouse_id = 3;

-- 更新數(shù)據(jù)
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- 查看執(zhí)行計(jì)劃
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'))

配置參數(shù)

以下參數(shù)用于 MySQL 外部服務(wù)器對(duì)象：

• host：MySQL 服務(wù)器的地址或者主機(jī)名，默認(rèn)為 127.0.0.1；
• port：MySQL 服務(wù)器的端口，默認(rèn)為 3306；
• secure_auth：?jiǎn)⒂没蛘呓冒踩J(rèn)證，默認(rèn)為 true；
• init_command：連接 MySQL 服務(wù)器之后執(zhí)行的初始 SQL 語(yǔ)句；
• use_remote_estimate：是否執(zhí)行遠(yuǎn)程 EXPLAIN 命令獲取成本評(píng)估，默認(rèn)為 false；
• reconnect：?jiǎn)⒂没蛘呓米詣?dòng)重新連接功能，默認(rèn)為 false；
• sql_mode：設(shè)置 MySQL sql_mode 變量，默認(rèn)為 ANSI_QUOTES；
• ssl_key：客戶端私鑰文件名；
• ssl_cert：客戶端公鑰認(rèn)證文件名；
• ssl_ca：證書(shū)授權(quán)（CA）認(rèn)證文件名。如果使用該選項(xiàng)，必須和 MySQL 服務(wù)器的證書(shū)一致；
• ssl_capath：包含可信 SSL CA 認(rèn)證文件的目錄；
• ssl_cipher：SSL 加密允許的密碼列表；
• fetch_size：指定每次讀取的數(shù)據(jù)行數(shù)。該參數(shù)可以基于外部表或者外部服務(wù)器進(jìn)行指定，基于外部表指定的參數(shù)優(yōu)先級(jí)更高。默認(rèn)為 100；
• character_set：MySQL 連接使用的字符集。默認(rèn)為 auto，表示基于操作系統(tǒng)設(shè)置進(jìn)行自動(dòng)配置。在引入該選項(xiàng)之前，字符集設(shè)置為 PostgreSQL 數(shù)據(jù)庫(kù)字符集. To get this 。如果想要兼容歷史配置，可以將 character_set 設(shè)置為特殊值 PGDatabaseEncoding。

以下參數(shù)用于 MySQL 外部表對(duì)象：

• dbname：MySQL 數(shù)據(jù)庫(kù)名，這是一個(gè)必填項(xiàng)；
• table_name：MySQL 表名，默認(rèn)與 PostgreSQL 外部表同名；
• max_blob_size：不會(huì)被截?cái)嘧x取的最大 blob 大小；
• fetch_size：與外部服務(wù)器對(duì)象的 fetch_size 參數(shù)相同。

以下參數(shù)用于創(chuàng)建用戶映射：

• username：連接 MySQL 服務(wù)器的用戶名；
• password：連接 MySQL 服務(wù)器的密碼。

以下參數(shù)用于 IMPORT FOREIGN SCHEMA 命令：

• import_default：導(dǎo)入外部表定義時(shí)是否包含字段的 DEFAULT 屬性，默認(rèn)為 false；
• import_not_null：導(dǎo)入外部表定義時(shí)是否包含字段的 NOT NULL 約束，默認(rèn)為 true；
• import_enum_as_text：導(dǎo)入外部表定義時(shí)將 MySQL ENUM 類型映射為 PostgreSQL TEXT 類型，或者創(chuàng)建一個(gè)新的枚舉類型并提示警告，默認(rèn)為 false；
• import_generated：導(dǎo)入外部表定義時(shí)是否包含字段的 GENERATED 表達(dá)式，默認(rèn)為 true。如果生成列表達(dá)式中使用了 PostgreSQL 中不存在的函數(shù)或者操作符，IMPORT 將會(huì)失敗。

到此這篇關(guān)于PostgreSQL使用MySQL作為外部表（mysql_fdw）的文章就介紹到這了,更多相關(guān)PostgreSQL使用MySQL外表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論