PostgreSQL使用MySQL作为外部表(mysql_fdw)（post后面）新鲜出炉

— 创建服务器对象
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’);

— 创建外部表
CREATE FOREIGN TABLE warehouse
(
warehouse_id int,
warehouse_name text,
warehouse_created timestamp
)
SERVER mysql_server
OPTIONS (dbname ‘db’, table_name ‘warehouse’);

— 插入数据
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 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 FROM warehouse where warehouse_id=3;

— 更新数据
UPDATE warehouse set warehouse_name=’UPS_NEW’ where warehouse_id=1;

— 查看执行计划
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’))