PolarDB：mysql_fdw（MySQL外部表格）

注意事項

• 使用之前需要將PolarDB PostgreSQL版的專用網路網段（例如，172.xx.xx.xx/16）添加到MySQL執行個體的白名單中，允許PolarDB PostgreSQL版訪問MySQL執行個體。

• 不能在PolarDB PostgreSQL版的OPTIONS中指定host、port等關鍵字，請使用channel_name替代。每個叢集預設配置一個channel為localhost，即串連本地的channel。如果需要串連其他資料庫叢集，請先建立channel，配置channel請聯絡我們處理。

使用指南

1. 建立mysql_fdw外掛程式。

   CREATE EXTENSION IF NOT EXISTS mysql_fdw;

   說明

   僅限高許可權帳號可以執行此命令。

2. 建立MySQL伺服器定義。

   CREATE SERVER <server名稱>
       FOREIGN DATA WRAPPER mysql_fdw
       OPTIONS (channel_name 'channel名稱');

   說明

   PolarDB PostgreSQL版在OPTIONS中只能使用channel_name指定MySQL執行個體的串連資訊。

   樣本如下（已經提前配置好名為mysqlchannel的polar_channel）：

   CREATE SERVER mysql_server
        FOREIGN DATA WRAPPER mysql_fdw
        OPTIONS (channel_name 'mysqlchannel');

3. 建立使用者映射，將MySQL伺服器定義映射到PolarDB PostgreSQL版的某個使用者上，後續使用該使用者訪問MySQL執行個體的資料。

   CREATE USER MAPPING FOR <PolarDB PostgreSQL使用者名稱>
   SERVER <server名稱>
   OPTIONS (username '<MySQL使用者名稱>', password '<MySQL使用者對應密碼>');

   樣本如下：

   CREATE USER MAPPING FOR pgtest
   SERVER mysql_server
   OPTIONS (username 'mysqltest', password 'Test1234!');

4. 使用步驟3中PolarDB PostgreSQL版使用者建立MySQL執行個體的外部表格。

   說明

   外部表格的欄位名要與MySQL資料庫中表的欄位名相同，同時可以僅建立您想要查詢的欄位。例如，MySQL資料庫中的表有3個欄位ID、NAME、AGE，您可以僅建立其中2個欄位ID、NAME。

   CREATE FOREIGN TABLE <表名> (<欄位名> <資料類型>,<欄位名> <資料類型>...) server <server名稱> options (dbname '<MySQL資料庫名>', table_name '<MySQL表名>');

   樣本如下：

   CREATE FOREIGN TABLE ft_test (id1 int, name1 text) server mysql_server options (dbname 'test123', table_name 'test');

5. 測試讀寫。

   • 您可以通過外部表格讀寫MySQL執行個體的資料。

     說明

     MySQL執行個體對應的表必須有主鍵才可以寫入資料，否則會出現如下錯誤：

     ERROR:  first column of remote table must be unique for INSERT/UPDATE/DELETE operation.

     SELECT * FROM ft_test ;
     INSERT INTO ft_test VALUES (2,'abc');
     INSERT 0 1
     INSERT INTO ft_test SELECT generate_series(3,100),'abc';
     INSERT 0 98
     SELECT count(*) FROM ft_test ;
      count
     -------
         99
     (1 row)

   • 檢查執行計畫，即PolarDB PostgreSQL版查詢MySQL執行個體的資料的請求在MySQL中是如何執行的。

     explain verbose SELECT count(*) FROM ft_test ;
                                       QUERY PLAN
     -------------------------------------------------------------------------------
      Aggregate  (cost=1027.50..1027.51 rows=1 width=8)
        Output: count(*)
        ->  Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=0)
              Output: id, info
              Remote server startup cost: 25
              Remote query: SELECT NULL FROM `test123`.`test`
     (6 rows)
     
     explain verbose SELECT id FROM ft_test WHERE id=2;
                                    QUERY PLAN
     -------------------------------------------------------------------------
      Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)
        Output: id
        Remote server startup cost: 25
        Remote query: SELECT `id` FROM `test123`.`test` WHERE ((`id` = 2))
     (4 rows)