Mnesia是一个分布式数据库管理系统,适合于电信和其它需要持续运行和具备软实时特性的Erlang应用,越来越受关注和使用,但是目前Mnesia资料却不多,很多都只有官方的用户指南。下面的内容将着重说明 Mnesia 数据库如何实现SQL查询,实现select / insert / up
Mnesia是一个分布式数据库管理系统,适合于电信和其它需要持续运行和具备软实时特性的Erlang应用,越来越受关注和使用,但是目前Mnesia资料却不多,很多都只有官方的用户指南。下面的内容将着重说明 Mnesia 数据库如何实现SQL查询,实现select / insert / update / where / order by / join / limit / delete等SQL操作。
示例中表结构的定义:
%% 账号表结构 -record( y_account,{ id, account, password }). %% 资料表结构 -record( y_info, { id, nickname, birthday, sex }).
1、Create Table / Delete Table 操作
%%=============================================== %% create table y_account ( id int, account varchar(50), %% password varchar(50), primary key(id)) ; %%=============================================== %% 使用 mnesia:create_table mnesia:create_table( y_account,[{attributes, record_info(fields, y_account)} , {type,set}, {disc_copies, [node()]} ]). %%=============================================== %% drop table y_account; %%=============================================== %% 使用 mnesia:delete_table mnesia:delete_table(y_account) .
注:参数意义可以看文档,{type,set} 表示id作为主键,不允许id重复,如果改为 {type,bag},id可以重复,但整条记录不能重复
2、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 <p>查询部分字段的记录</p> <pre class="brush:php;toolbar:false">%%=============================================== %% 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 <p><strong>3、Insert / Update 操作</strong></p> <p>mnesia是根据主键去更新记录的,如果主键不存在则插入</p> <pre class="brush:php;toolbar:false">%%=============================================== %% 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).
4、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 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).
5、Order By 查询
%%=============================================== %% select * from y_account order by id asc %%=============================================== %% 使用 qlc F = fun() -> Q = qlc:q([E || E Q = qlc:q([E || E B#y_account.id > A#y_account.id end, qlc:e(qlc:sort(Q, [{order, Order}])) end, mnesia:transaction(F).
6、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 <p><strong>7、Limit 查询</strong></p> <pre class="brush:php;toolbar:false">%%=============================================== %% select * from y_account limit 2 %%=============================================== %% 使用 mnesia:select F = fun() -> MatchHead = #y_account{ _ = '_' }, mnesia:select(y_account, [{MatchHead, [], ['$_']}], 2, none) end, mnesia:transaction(F). %% 使用 qlc F = fun() -> Q = qlc:q([E || E <p><strong>8、Select count(*) 查询</strong></p> <pre class="brush:php;toolbar:false">%%=============================================== %% select count(*) from y_account %%=============================================== %% 使用 mnesia:table_info F = fun() -> mnesia:table_info(y_account, size) end, mnesia:transaction(F).
9、Delete 查询
%%=============================================== %% delete from y_account where id=5 %%=============================================== %% 使用 mnesia:delete F = fun() -> mnesia:delete({y_account, 5}) end, mnesia:transaction(F).
注:使用qlc模块查询,需要在文件顶部声明“-include_lib("stdlib/include/qlc.hrl").”,否则编译时会产生“Warning: qlc:q/1 called, but "qlc.hrl" not included”的警告。
更新说明:
2013/11/20 补充了 mnesia:select 方式的 limit 查询