erlang mnesia 数据库查询

Mnesia是一个分布式数据库管理系统，适合于电信和其它需要持续运行和具备软实时特性的Erlang应用，越来越受关注和使用，但是目前Mnesia资料却不多，很多都只有官方的用户指南。下面的内容将着重说明 如何做 Mnesia 数据库查询。

示例中表结构的定义：

 

%% 账号表结构 
-record( y_account,{ id, account, password }).

%% 资料表结构  
-record( y_info, { id, nickname, birthday, sex }). 

1、查询全部记录

 

%%===============================================
%%  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).

2、查询部分字段的记录

 

%%===============================================
%%  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).

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”的警告。