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

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

下載安裝

點擊 GitHub 下載 mysql_fdw 源代碼。

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

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

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

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

$ 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 環(huán)境變量的設置。默認設置可以參考 mysql_init.sh 腳本。

使用示例

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

-- 加載擴展插件
CREATE EXTENSION mysql_fdw;

-- 創(chuàng)建服務器對象
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í)行計劃
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 外部服務器對象：

• host：MySQL 服務器的地址或者主機名，默認為 127.0.0.1；
• port：MySQL 服務器的端口，默認為 3306；
• secure_auth：啟用或者禁用安全認證，默認為 true；
• init_command：連接 MySQL 服務器之后執(zhí)行的初始 SQL 語句；
• use_remote_estimate：是否執(zhí)行遠程 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：指定每次讀取的數(shù)據(jù)行數(shù)。該參數(shù)可以基于外部表或者外部服務器進行指定，基于外部表指定的參數(shù)優(yōu)先級更高。默認為 100；
• character_set：MySQL 連接使用的字符集。默認為 auto，表示基于操作系統(tǒng)設置進行自動配置。在引入該選項之前，字符集設置為 PostgreSQL 數(shù)據(jù)庫字符集. To get this 。如果想要兼容歷史配置，可以將 character_set 設置為特殊值 PGDatabaseEncoding。

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

• dbname：MySQL 數(shù)據(jù)庫名，這是一個必填項；
• table_name：MySQL 表名，默認與 PostgreSQL 外部表同名；
• max_blob_size：不會被截斷讀取的最大 blob 大小；
• fetch_size：與外部服務器對象的 fetch_size 參數(shù)相同。

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

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

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

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

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

最新評論