詳解mysql跨庫查詢解決方案

2022-08-05 18:02:46

1.第一種跨庫查詢，是在同一個mysql伺服器下兩個不同的資料庫之間的聯查，關係如下圖

在同一個mysql伺服器下，不同的兩個資料直接加上庫名就可以實現跨庫查詢了

select * from t_test1 t1, test2.t_test2 t2 where t1.id = t2.id

執行sql查詢到一下結果

2.第二種跨庫查詢，是在兩臺不同伺服器(物理伺服器)上分別安裝的mysql伺服器，實現跨庫查詢，其實現原理類似一個虛擬對映,需要用到mysql的另一個儲存引擎Federated，FEDERATED儲存引擎存取在遠端資料庫的表中的資料，而不是原生的表。

這個特性給某些開發應用帶來了便利，你可以直接在本地構建一個federated表來連線遠端資料表，設定好了之後本地表的資料可以直接跟遠端資料表同步。實際上這個引擎裡面是不真實存放資料的，所需要的資料都是連線到其他MySQL伺服器上獲取。

mysql預設沒有開啟federated儲存引擎,需要在組態檔裡面開啟;

#在[mysqld] 下 新增 federated 然後重啟mysql服務就可以了
[mysqld]
federated

如下圖:

#輸入命令檢視引擎開啟狀態
show engines;

可以看到,已經開啟了

然後就可在test1資料庫上建立虛擬表了,將test2庫上t_test2表對映到test1庫上面

CREATE TABLE `t_test2` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb3 COMMENT='遠端測試表t_test2' CONNECTION='mysql://root:123456@127.0.0.1:3306/test2/t_test2' ;
/**

CONNECTION='mysql://root:123456$@127.0.0.1:3306/test2/t_test2' 這個設定是關鍵
root:代表遠端資料庫的使用者
123456:代表遠端資料的密碼
127.0.0.1:代表遠端資料庫的ip地址,域名也可以
test2:代表遠端資料庫的資料庫名稱
t_test2:代表遠端資料庫中的哪一張表
**/

執行sql語句

可以看到建立了一張t_test2的表，這張表其實不存在只是一個遠端的對映而已

可以看到使用FEDERATED引擎

表建立完成後就可以寫sql了

select * from t_test1 t1, t_test2 t2 where t1.id = t2.id

可以看到執行結果

federated使用注意事項:

1.本地建立的表名必須在遠端伺服器存在，建立的欄位也必須是遠端表中的欄位，可以比遠端表的欄位少，但是不能多，本地儲存引擎選擇

2.對本地虛擬表的結構修改，並不會修改遠端表的結構

3.truncate 命令，會清除遠端表資料

4.drop命令只會刪除虛擬表，並不會刪除遠端表

5.select count(*), select * from limit M, N 等語句執行效率非常低，資料量較大時存在很嚴重的問題，但是按主鍵或索引列查詢，則很快，如以下查詢就非常慢（假設 id 為主索引）

select id from db.tablea where id >100 limit 10 ;

到此這篇關於詳解mysql跨庫查詢解決方案的文章就介紹到這了,更多相關mysql跨庫查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

詳解mysql跨庫查詢解決方案

熱門文章