iT邦幫忙

0

巧用 Foreign Data Wrapper,由PostgreSQL加強MySQL處理資料

參考此問題
https://ithelp.ithome.com.tw/questions/10195030

既然PostgreSQL可以輕易的使用函數做半形全形轉換,但原本資料是在MySQL時,
由MySQL匯出,再匯入PostgrSQL處理後,再由PostgreSQL匯出,再匯回MySQL,ㄧ來繁瑣,
二來缺乏即時性.
所幸PostgreSQL有Foreign Data Wrapper這一好物.
接著來看實例,怎樣運用.

-- in MySQL
create table ithelp190828 (
  id int not null auto_increment primary key
, single_bytes varchar(30)
, multi_bytes varchar(30)
);

insert into ithelp190828 (single_bytes) values
('1234567890'),('abcdefghji'),('!@#$%^&*()');

-- in PostgreSQL
create server mysql_server
foreign data wrapper mysql_fdw
options (host '127.0.0.1', port '3306');

create user mapping for miku
server mysql_server
options (username 'miku1', password 'your$passwd');

create foreign table wrap_ithelp190828 (
  id int
, single_bytes varchar(30)
, multi_bytes varchar(30)
)
server mysql_server
options (dbname 'miku1', table_name 'ithelp190828');

select *
  from wrap_ithelp190828;

+----+--------------+-------------+
| id | single_bytes | multi_bytes |
+----+--------------+-------------+
|  1 | 1234567890   | ¤           |
|  2 | abcdefghji   | ¤           |
|  3 | !@#$%^&*()   | ¤           |
+----+--------------+-------------+
(3 rows)

-- 由 PostgreSQL 這邊利用函數更新 MySQL的Table.

update wrap_ithelp190828
   set multi_bytes = to_multi_byte(single_bytes);

commit;

-- 在MySQL這邊查看
(miku1) [miku1]> select * from ithelp190828;
+----+--------------+--------------------------------+
| id | single_bytes | multi_bytes                    |
+----+--------------+--------------------------------+
|  1 | 1234567890   | 1234567890           |
|  2 | abcdefghji   | abcdefghji           |
|  3 | !@#$%^&*()   | !@#$%^&*()           |
+----+--------------+--------------------------------+

-- 由 PostgreSQL 將 MySQL Table的 single_bytes 清為 NULL
update wrap_ithelp190828
   set single_bytes = NULL;

commit;

-- 在MySQL這邊查看
(miku1) [miku1]> select * from ithelp190828;
+----+--------------+--------------------------------+
| id | single_bytes | multi_bytes                    |
+----+--------------+--------------------------------+
|  1 | NULL         | 1234567890           |
|  2 | NULL         | abcdefghji           |
|  3 | NULL         | !@#$%^&*()           |
+----+--------------+--------------------------------+

-- 由 PostgreSQL 這邊利用函數更新 MySQL的Table.
update wrap_ithelp190828
   set single_bytes = to_single_byte(multi_bytes);

commit;

-- 在MySQL這邊查看
(miku1) [miku1]> select * from ithelp190828;
+----+--------------+--------------------------------+
| id | single_bytes | multi_bytes                    |
+----+--------------+--------------------------------+
|  1 | 1234567890   | 1234567890           |
|  2 | abcdefghji   | abcdefghji           |
|  3 | !@#$%^&*()   | !@#$%^&*()           |
+----+--------------+--------------------------------+


1 則留言

0
ckp6250
iT邦研究生 4 級 ‧ 2019-09-05 20:40:38

思路奇妙,大開眼界,感恩。
但如此一來,豈不一台 Server 要安裝兩套 SQL ?
如果資料量大時,效能行嗎?
比如 select name,轉成全形(address) from comcustomer;
一次來個一千筆,如何運作?

我要留言

立即登入留言