在PostgreSQL数据库中通过MySQL外部数据封装器（FDW）访问MySQL数据

东风破137

于 2024-10-08 12:52:06 发布

阅读量92

点赞数 3

文章标签：数据库 postgresql mysql

本文链接：https://blog.csdn.net/qq_61920297/article/details/142756774

版权

标题：在PostgreSQL数据库中通过MySQL外部数据封装器（FDW）访问MySQL数据

摘要：
本文详细介绍了如何在PostgreSQL数据库中配置MySQL外部数据封装器（FDW），以便在PostgreSQL环境中直接访问和操作MySQL数据库中的数据。通过这一设置，用户可以跨数据库系统实现数据集成和查询。

一、引言

随着数据技术的不断发展，跨数据库系统的数据集成需求日益增加。MySQL和PostgreSQL作为两种流行的开源数据库管理系统，各自具有独特的优势。通过MySQL外部数据封装器（FDW），PostgreSQL可以无缝地访问MySQL数据库中的数据，从而实现跨数据库的数据查询和操作。

二、在PostgreSQL中配置MySQL FDW

步骤1：创建PostgreSQL数据库

首先，在PostgreSQL中创建一个新的数据库，用于存放外部数据封装器的配置信息。

CREATE DATABASE mysql_fdw;

连接到新创建的数据库：

\c mysql_fdw;

然后，安装MySQL外部数据封装器扩展：

CREATE EXTENSION mysql_fdw;

步骤2：在MySQL中配置用户权限和数据库

在MySQL数据库中，创建一个具有远程访问权限的用户，并授予其所有数据库的所有权限。

CREATE USER 'root'@'%' IDENTIFIED BY 'Linux123!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

创建一个名为school的数据库，并切换到该数据库：

CREATE DATABASE school;
USE school;

在school数据库中创建两个表：students和employees。

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    class VARCHAR(50) NOT NULL
);

INSERT INTO students (name, age, class) VALUES ('Alice', 14, '8A');
INSERT INTO students (name, age, class) VALUES ('Bob', 15, '8B');
INSERT INTO students (name, age, class) VALUES ('Charlie', 13, '7A');
INSERT INTO students (name, age, class) VALUES ('Diana', 14, '8C');

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

步骤3：在PostgreSQL中配置MySQL服务器和用户映射

在PostgreSQL数据库中，创建一个服务器对象，指定MySQL服务器的IP地址和端口号。

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

创建一个用户映射，将PostgreSQL用户（例如hgbase）映射到MySQL用户（例如root），并指定MySQL用户的密码。

CREATE USER MAPPING FOR hgbase
SERVER mysql_server
OPTIONS (username 'root', password 'your_password');

步骤4：创建外部表并插入数据

在PostgreSQL中创建一个外部表，该表将映射到MySQL中的employees表。

CREATE FOREIGN TABLE employees (
    id INT,
    name VARCHAR(100),
    department VARCHAR(100)
) SERVER mysql_server
OPTIONS (dbname 'school', table_name 'employees');

通过生成一系列数据，并将其插入到外部表中。这里，我们使用了一个简单的案例，根据ID值将员工分配到不同的部门。

INSERT INTO employees (id, name, department)
SELECT 
    s.id,
    'Employee ' || s.id AS name,
    CASE 
        WHEN s.id % 3 = 0 THEN 'Engineering'
        WHEN s.id % 3 = 1 THEN 'Marketing'
        ELSE 'Sales'
    END AS department
FROM generate_series(1, 100) AS s(id);