以前写过一些在PostgreSQL中创建file, CouchDB, Redis, PostgreSQL, Oracle等外部表的BLOG.
一直没有写MySQL的，刚好最近有一个项目要从MySQL迁移到PostgreSQL。今天补上如下 :
下载
1.
修改PATH和LD_LIBRARY_PATH包含mysql和postgresql的相关目录, 例如
2. mysql中的测试表
3. 在库中创建extension
su - root
export MYSQLHOME=/usr/local/mysql
export PGHOME=/usr/local/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:$MYSQLHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=$PGHOME/bin:$MYSQLHOME/bin:$PATH:.
make USE_PGXS=1
make USE_PGXS=1 install2. mysql中的测试表
mysql> desc CMSArticles
-> ;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| articleid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| position | int(11) | NO | MUL | 0 | |
| cateid | int(11) unsigned | NO | MUL | 0 | |
| db_id | varchar(20) | YES | MUL | NULL | |
| title | varchar(50) | YES | | NULL | |
| link | varchar(200) | YES | | NULL | |
| icon | varchar(200) | YES | | NULL | |
| tag | varchar(50) | YES | | NULL | |
| keyword | varchar(50) | YES | | NULL | |
| tag_url | varchar(200) | YES | | NULL | |
| keyword_url | varchar(200) | YES | | NULL | |
| summary | varchar(300) | YES | | NULL | |
| content | text | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+3. 在库中创建extension
psql -h 127.0.0.1 -U postgres -d digoal
# create extension mysql_fdw;
# create server mysql_server1 foreign data wrapper mysql_fdw options(address '127.0.0.1', port '3306');
# CREATE USER MAPPING FOR digoal server mysql_server1 options (username 'root', password 'Roote');
# grant usage on FOREIGN server mysql_server1 to digoal;
\c digoal digoal
> create FOREIGN TABLE mysql_foreign_table1 (articleid int8, position int8, cateid int8, db_id varchar(20), title varchar(50), link varchar(200), icon varchar(200), tag varchar(50), keyword varchar(50), tag_url varchar(200), keyword_url varchar(200), summary varchar(300), content text)
SERVER mysql_server1
OPTIONS (database 'my_digoal', table 'CMSArticles');
> select count(*) from CMSArticles;
与MySQL结果一致.
前面使用的是table选项, 另外一个创建外部表的参数, query. 如下 :
【参考】
create FOREIGN TABLE mysql_foreign_table2 (articleid int8, position int8, cateid int8, db_id varchar(20), title varchar(50), link varchar(200), icon varchar(200), tag varchar(50), keyword varchar(50), tag_url varchar(200), keyword_url varchar(200), summary varchar(300), content text)
SERVER mysql_server1
OPTIONS (database 'my_digoal', query 'select articleid, position, cateid, db_id, title, link, icon, tag, keyword, tag_url, keyword_url, summary, content from CMSArticles where articleid=1');【参考】
