[TOC] > [github](https://github.com/EnterpriseDB/mysql_fdw) ## 概述 可以通过 gpsql 去访问 mysql ## 安装扩展 ``` yum install mysql_fdw_15 ``` ## 添加扩展 ``` CREATE EXTENSION mysql_fdw; ``` ## 配置 server ``` 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'); ``` mysql设置外部源 > 注意外部库和表并不会自动创建,需要先手动创建 > 并且创建的表必须要有**主键ID**,否则插入会失败 > 由于 pgsql 中的 text 与varchar 通用,在mysql 中不通用,所以pg创建中,尽量使用varchar,便于理解 创建mysql 表 ``` CREATE TABLE warehouse ( warehouse_id int, warehouse_name text, warehouse_created timestamp ) PRIMARY KEY (`warehouse_id`) ``` 创建表 ``` 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')) ```