PostgreSQL接MySQL外表

Postgresql挂MySQL外表

Postgresql下安装mysql_fdw

设置环境变量（根据实际安装路径变更）

export PATH=/usr/pgsql-14/bin/:$PATH
export PATH=/usr/local/mysql/bin/:$PATH
make USE_PGXS=1    #编译 
make USE_PGXS=1 install  "  	#安装 

创建EXTENSION

CREATE EXTENSION mysql_fdw;

创建SERVER

 CREATE SERVER mysql_server
               FOREIGN DATA WRAPPER mysql_fdw
               OPTIONS (host '127.0.0.1', port '3306');

创建USER MAPPING

CREATE USER MAPPING FOR postgres
              SERVER mysql_server
              OPTIONS (username 'root', password '123456');"

创建FOREIGN TABLE

CREATE FOREIGN TABLE warehouse(
              warehouse_id int,
              warehouse_name text,
              warehouse_created timestamp)
              SERVER mysql_server
              OPTIONS (dbname 'mysql', table_name 'warehouse');"

创建TABLE

 CREATE TABLE warehouse(
              warehouse_id int primary key not null,
              warehouse_name text,
              warehouse_created timestamp
              );"

插入数据

INSERT INTO warehouse values (1, 'UPS', now());
INSERT INTO warehouse values (2, 'TV', now());
INSERT INTO warehouse values (3, 'Table', now());

修改数据

update warehouse set warehouse_name='new name' where warehouse_id=2;
select * from warehouse;

删除数据

delete from warehouse where warehouse_id=3;
select * from warehouse;

查询 PG数据

select * from warehouse;

插入数据

INSERT INTO warehouse values (3, 'Table', now());
INSERT INTO warehouse values (4, 'NEWS', '2020-06-02 10:00:00');
select * from warehouse;

查询mysql数据

select * from warehouse;

修改数据

update warehouse set warehouse_name='New Name' where warehouse_id=3;
select * from warehouse;

查询mysql数据

select * from warehouse;

删除数据

delete from warehouse where warehouse_id=4;

查询mysql数据

select * from warehouse;