mysql 數據源 · PHP/Python/前端/Linux 等等學習筆記

[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')) ```