Mnesia是一個分布式
數據庫管理系統,適合于電信和其它需要持續運行和具備軟實時特性的Erlang應用,越來越受關注和使用,但是目前Mnesia資料卻不多,很多都只有官方的用戶指南。下面的內容將著重說明 如何做 Mnesia 數據庫查詢。
示例中表結構的定義:
%% 賬號表結構
-record( y_account,{ id, account, password }).
%% 資料表結構
-record( y_info, { id, nickname, birthday, sex }).
1、Select 查詢
查詢全部記錄
%%=============================================== %% select * from y_account %%=============================================== %% 使用 mnesia:select F = fun() -> MatchHead = #y_account{ _ = '_' }, Guard = [], Result = ['$_'], mnesia:select(y_account, [{MatchHead, Guard, Result}]) end, mnesia:transaction(F). %% 使用 qlc F = fun() -> Q = qlc:q([E || E <- mnesia:table(y_account)]), qlc:e(Q) end, mnesia:transaction(F). |
查詢部分字段的記錄
%%=============================================== %% select id,account from y_account %%=============================================== %% 使用 mnesia:select F = fun() -> MatchHead = #y_account{id = '$1', account = '$2', _ = '_' }, Guard = [], Result = ['$$'], mnesia:select(y_account, [{MatchHead, Guard, Result}]) end, mnesia:transaction(F). %% 使用 qlc F = fun() -> Q = qlc:q([[E#y_account.id, E#y_account.account] || E <- mnesia:table(y_account)]), qlc:e(Q) end, mnesia:transaction(F). |
2、Insert / Update 操作
mnesia是根據主鍵去更新記錄的,如果主鍵不存在則插入
%%=============================================== %% insert into y_account (id,account,password) values(5,"xiaohong","123") %% on duplicate key update account="xiaohong",password="123"; %%=============================================== %% 使用 mnesia:write F = fun() -> Acc = #y_account{id = 5, account="xiaohong", password="123"}, mnesia:write(Acc) end, mnesia:transaction(F). |
3、Where 查詢
%%=============================================== %% select account from y_account where id>5 %%=============================================== %% 使用 mnesia:select F = fun() -> MatchHead = #y_account{id = '$1', account = '$2', _ = '_' }, Guard = [{'>', '$1', 5}], Result = ['$2'], mnesia:select(y_account, [{MatchHead, Guard, Result}]) end, mnesia:transaction(F). %% 使用 qlc F = fun() -> Q = qlc:q([E#y_account.account || E <- mnesia:table(y_account), E#y_account.id>5]), qlc:e(Q) end, mnesia:transaction(F). |
如果查找主鍵 key=X 的記錄,還可以這樣子查詢:
%%=============================================== %% select * from y_account where id=5 %%=============================================== F = fun() -> mnesia:read({y_account,5}) end, mnesia:transaction(F). |
如果查找非主鍵 field=X 的記錄,可以如下查詢:
%%=============================================== %% select * from y_account where account='xiaomin' %%=============================================== F = fun() -> MatchHead = #y_account{ id = '_', account = "xiaomin", password = '_' }, Guard = [], Result = ['$_'], mnesia:select(y_account, [{MatchHead, Guard, Result}]) end, mnesia:transaction(F). |
4、Order By 查詢
%%=============================================== %% select * from y_account order by id asc %%=============================================== %% 使用 qlc F = fun() -> Q = qlc:q([E || E <- mnesia:table(y_account)]), qlc:e(qlc:keysort(2, Q, [{order, ascending}])) end, mnesia:transaction(F). %% 使用 qlc 的第二種寫法 F = fun() -> Q = qlc:q([E || E <- mnesia:table(y_account)]), Order = fun(A, B) -> B#y_account.id > A#y_account.id end, qlc:e(qlc:sort(Q, [{order, Order}])) end, mnesia:transaction(F). |
5、Join 關聯表查詢
%%=============================================== %% select y_info.* from y_account join y_info on (y_account.id = y_info.id) %% where y_account.account = 'xiaomin' %%=============================================== %% 使用 qlc F = fun() -> Q = qlc:q([Y || X <- mnesia:table(y_account), X#y_account.account =:= "xiaomin", Y <- mnesia:table(y_info), X#y_account.id =:= Y#y_info.id ]), qlc:e(Q) end, mnesia:transaction(F). |
6、Limit 查詢
%%=============================================== %% select * from y_account limit 2 %%=============================================== %% 使用 qlc F = fun() -> Q = qlc:q([E || E <- mnesia:table(y_account)]), QC = qlc:cursor(Q), qlc:next_answers(QC, 2) end, mnesia:transaction(F). |
注:使用qlc模塊查詢,需要在文件頂部聲明“-include_lib("stdlib/include/qlc.hrl").”,否則編譯時會產生“Warning: qlc:q/1 called, but "qlc.hrl" not included”的警告。