概述
PostgreSQL具有插件功能，通过不同的插件拓展，实现数据库本身不包含的功能，以满足用户的需求。mysql_fdw 就是一个强大的外部表功能，所谓外部表，就是在PG数据库中通过SQL访问外部数据源数据，就像访问本地数据库一样，下面就来测试一下使用mysql_fdw 来访问mysql中的数据
安装使用步骤
下载安装插件
可以直接下载rpm包进行安装，编译需要各种依赖，比较麻烦，下面链接，选择mysql_fdw 的包下载
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/
下载完成后安装：
[root@stephen ~]# rpm -ivh mysql_fdw_11-2.5.0-1.rhel7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mysql_fdw_11-2.5.0-1.rhel7 ################################# [100%]
AI 代码解读
安装完成之后创建拓展：
postgres=# create extension mysql_fdw ;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
-----------+---------+------------+--------------------------------------------------
mysql_fdw | 1.1 | public | Foreign data wrapper for querying a MySQL server
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
AI 代码解读
可以看到插件拓展就安装完成了
创建server
在PG端创建sever，外部服务定义了具体外部数据源的连接信息，如果外部源是数据库，通常会包含数据库的IP，端口号，数据库名称等信息，如下：
postgres=# CREATE SERVER mysql_server_test FOREIGN DATA WRAPPER mysql_fdw OPTIONS (HOST '172.16.21.167', PORT '3306');
CREATE SERVER
postgres=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
-------------------+----------+----------------------
mysql_server_test | postgres | mysql_fdw
(1 row)
AI 代码解读
创建用户映射
OPTIONS 是指外部拓展的选项，指定了访问外部数据标的本地用户和远程用户信息
postgres=# CREATE USER MAPPING FOR PUBLIC SERVER mysql_server_test OPTIONS (username 'root', password 'xxxxx');
CREATE USER MAPPING
AI 代码解读
如果想修改
alter user MAPPING FOR public server mysql_server options ( set password 'xxxxxx');
AI 代码解读
注：一些高版本的mysql需要修改密码策略
将mysql的密码策略改为mysql_native_password
alter user root@localhost identified with mysql_native_password by 'password';
AI 代码解读
创建外部表
理论上外部表的字段只要包含于远程mysql表字段内就可以，但是一般的，都建议将外部表与远程表的字段一致。但是mysql和pg字段类型有时会有些许区别，需要确认好字段类型，如下：
postgres=# create foreign table test01(id int,name text,age int,wherefrom text ) server mysql_server_test options(dbname 'test',table_name 'test01');
CREATE FOREIGN TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+---------------+----------
public | test01 | foreign table | postgres
(1 row)
AI 代码解读
可以看到表的类型是外部表，和普通的表有区别
创建完成后，时就可以在PG端访问这个表
postgres=# select * from test01 ;
id | name | age | wherefrom
----+----------+-----+-----------
1 | dwqdw | 34 | dadsa
2 | dwdsaqdw | 24 | dsadadsa
3 | dwdsaqdw | 20 | dsadadsa
4 | dwdsdadw | 20 | dsadaadsa
(4 rows)
AI 代码解读
此时mysql端发生的变化
mysql> insert into test01 values(122,'测试',20,'再次测试');
Query OK, 1 row affected (0.01 sec)
AI 代码解读
PG 再次查询时这边马上就能看到
postgres=# select * from test01 ;
id | name | age | wherefrom
-----+----------+-----+-----------
1 | dwqdw | 34 | dadsa
2 | dwdsaqdw | 24 | dsadadsa
3 | dwdsaqdw | 20 | dsadadsa
4 | dwdsdadw | 20 | dsadaadsa
122 | 测试 | 20 | 再次测试
(5 rows)
AI 代码解读
反写数据
一般情况下，如果没有唯一键限制，反写数据就会报错
ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation
AI 代码解读
如果想反写到mysql，需要在mysql上添加表的限制
ALTER TABLE test01 ADD CONSTRAINT idx_id UNIQUE (id);（示例）
AI 代码解读
我这边是ID已经是主键唯一键了，所以直接能使用
postgres=# insert into test01 values(123,'反写测试',221,'再次测试');
INSERT 0 1
mysql> select * from test01;
+-----+--------------+------+--------------+
| id | name | age | wherefrom |
+-----+--------------+------+--------------+
| 1 | dwqdw | 34 | dadsa |
| 2 | dwdsaqdw | 24 | dsadadsa |
| 3 | dwdsaqdw | 20 | dsadadsa |
| 4 | dwdsdadw | 20 | dsadaadsa |
| 122 | 测试 | 20 | 再次测试 |
| 123 | 反写测试 | 221 | 再次测试 |
+-----+--------------+------+--------------+
6 rows in set (0.00 sec)
AI 代码解读
事实上现在不仅仅是支持insert语句，update与delete语句均支持，前提是提供给PG的mysql用户是有这些权限的。
物化数据
mysql_fdw 实现的一个关键特性就是支持持久连接的能力。查询执行后，不会删除与远程MySQL的连接。相反，它保留来自同一会话的下次查询连接。然而，在某些情况下，因为网络查询等原因不能及时查询数据，则可以考虑在本地实现数据保留。可以通过外部表创建物化视图，如下
postgres=# CREATE MATERIALIZED VIEW test_view as select * from test01;
SELECT 6
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------+-------------------+----------
public | test01 | foreign table | postgres
public | test_view | materialized view | postgres
(2 rows)
postgres=# select * from test_view;
id | name | age | wherefrom
-----+----------+-----+-----------
1 | dwqdw | 34 | dadsa
2 | dwdsaqdw | 24 | dsadadsa
3 | dwdsaqdw | 20 | dsadadsa
4 | dwdsdadw | 20 | dsadaadsa
122 | 测试 | 20 | 再次测试
123 | 反写测试 | 221 | 再次测试
(6 rows)
AI 代码解读
可以将刷新任务放到定时任务中，定时去刷新视图
REFRESH MATERIALIZED VIEW test_view;
AI 代码解读
新特性
如今mysql_fdw 已经支持
Where子句下推：
最新版本将外部表where子句下推到外部服务器。外部表上的where条件将在外部服务器上执行，因此将有更少的行传递给PostgreSQL。这是一个性能功能。
列下推：
以前的版本是从目标外表中获取所有列。最新版本执行列下推，仅返回属于选择目标列表的列。这是一个性能功能。
