erlang-mnesia数据库操作
2018-06-20 本文已影响0人
zhang_yongfeng
Mnesia是一个分布式数据库管理系统，适合于电信和其它需要持续运行和具备软实时特性的Erlang应用，越来越受关注和使用，但是目前Mnesia资料却不多，很多都只有官方的用户指南。下面的内容将着重说明 Mnesia 数据库如何实现SQL查询，实现select / insert / update / where / order by / join / limit / delete等SQL操作。
示例文件如下：
-module(mnesia_text).
-export([init/0,
create_m_y_account/0,
create_m_y_info/0,
delete_m/0,
select/0,
select_qlc/0,
quary/0,
quary_qlc/0,
write/0,
write_y_info/0,
where_qlc/0,
where/0,
where_qlc_key/0,
where_qlc_no_key/0,
order_by/0,
order_by_two/0,
join/0
]).
-compile([export_all]).
-include_lib("stdlib/include/qlc.hrl").
%% 账号表结构
-record( y_account,{ id, account, password }).
%% 资料表结构
-record( y_info, { id, nickname, birthday, sex }).
init() ->
%% 在本机节点上初始化数据库
mnesia:create_schema([node()]),
%% 启动数据库
mnesia:start().
%1、Create Table / Delete Table 操作
%%===============================================
%% create table y_account ( id int, account varchar(50),
%% password varchar(50), primary key(id)) ;
%%===============================================
create_m_y_account() ->
mnesia:create_table( y_account,[{attributes, record_info(fields, y_account)} , {type,set}, {disc_copies, [node()]} ]).
create_m_y_info() ->
mnesia:create_table( y_info,[{attributes, record_info(fields, y_info)} , {type,set}, {disc_copies, [node()]} ]).
%%===============================================
%% drop table y_account;
%%===============================================
delete_m() ->
mnesia:delete_table(y_account) .
%2、Select 查询
select() ->
%% 2.1使用 mnesia:select
F = fun() ->
MatchHead = #y_account{ _ = '_' },
Guard = [],
Result = ['$_'],
mnesia:select(y_account, [{MatchHead, Guard, Result}])
end,
mnesia:transaction(F).
%% 2.2使用 qlc
%%===============================================
%% select * from y_account
%%===============================================
select_qlc() ->
F = fun() ->
Q = qlc:q([E || E <- mnesia:table(y_account)]),
qlc:e(Q)
end,
mnesia:transaction(F).
% 2.3查询部分字段的记录
%%===============================================
%% select id,account from y_account
%%===============================================
quary() ->
F = fun() ->
MatchHead = #y_account{id = '$1', account = '$2', _ = '_' },
Guard = [],
Result = ['$$'],
mnesia:select(y_account, [{MatchHead, Guard, Result}])
end,
mnesia:transaction(F).
quary_qlc() ->
%% 使用 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、Insert / Update 操作
%%===============================================
%% insert into y_account (id,account,password) values(5,"xiaohong","123")
%% on duplicate key update account="xiaohong",password="123";
%%===============================================
%mnesia是根据主键去更新记录的，如果主键不存在则插入
write()->
%% 使用 mnesia:write
F = fun() ->
Acc = #y_account{id = 5, account="xiaohong", password="123"},
mnesia:write(Acc)
end,
mnesia:transaction(F).
write_y_info()->
%% 使用 mnesia:write id, nickname, birthday, sex
F = fun() ->
Acc = #y_info{id = 5, nickname="aaa", birthday="20180101"},
mnesia:write(Acc)
end,
mnesia:transaction(F).
%4、Where 查询
%%===============================================
%% select account from y_account where id>5
%%===============================================
where() ->
%% 使用 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).
where_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).
%%===============================================
%% select * from y_account where account='xiaomin'
%%===============================================
where_qlc_key() ->
%如果查找主键 key=X 的记录，还可以这样子查询：
F = fun() ->
mnesia:read({y_account,5})
end,
mnesia:transaction(F).
%%===============================================
%% select * from y_account where account='xiaomin'
%%===============================================
where_qlc_no_key() ->
%如果查找非主键 field=X 的记录，可以如下查询：
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
%%===============================================
order_by() ->
%% 使用 qlc
F = fun() ->
Q = qlc:q([E || E <- mnesia:table(y_account)]),
qlc:e(qlc:keysort(2, Q, [{order, ascending}]))
end,
mnesia:transaction(F).
order_by_two()->
%% 使用 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).
%6、Join 关联表查询
%%===============================================
%% select y_info.* from y_account join y_info on (y_account.id = y_info.id)
%% where y_account.account = 'xiaomin'
%%===============================================
join() ->
%% 使用 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).
%7、limit查询
%%===============================================
%% select * from y_account limit 2
%%===============================================
limit() ->
F = fun() ->
MatchHead = #y_account{ _ = '_' },
mnesia:select(y_account, [{MatchHead, [], ['$_']}], 2, none)
end,
mnesia:transaction(F).
limit_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).
%8、Select count(*) 查询
%%===============================================
%% select count(*) from y_account
%%===============================================
select_count() ->
%% 使用 mnesia:table_info
F = fun() ->
mnesia:table_info(y_account, size)
end,
mnesia:transaction(F).
%9、Delete 查询
%%===============================================
%% delete from y_account where id=5
%%===============================================
delete_data()->
%% 使用 mnesia:delete
F = fun() ->
mnesia:delete({y_account, 5})
end,
mnesia:transaction(F).
➜ erlang erl
Erlang R15B03 (erts-5.9.3.1) [source] [64-bit] [smp:8:8] [async-threads:0] [hipe] [kernel-poll:false]
Eshell V5.9.3.1 (abort with ^G)
1> c(mnesia_text).
{ok,mnesia_text}
2> mnesia_text:init().
ok
3> mnesia_text:create_m_y_account().
{aborted,{already_exists,y_account}}
4> mnesia_text:create_m_y_info(().
* 1: syntax error before: ')'
4> mnesia_text:create_m_y_info().
{aborted,{already_exists,y_info}}
5> mnesia_text:delete_m().
{atomic,ok}
6> mnesia_text:create_m_y_account().
{atomic,ok}
7> mnesia_text:create_m_y_account().
{aborted,{already_exists,y_account}}
8> mnesia_text:select().
{atomic,[]}
9> mnesia_text:quary().
{atomic,[]}
10> mnesia_text:write().
{atomic,ok}
11> c(mnesia_text).
{ok,mnesia_text}
12> mnesia_text:write().
{aborted,{{badmatch,{y_account,2,"xiaohong2","13"}},
[{mnesia_text,'-write/0-fun-0-',0,
[{file,"mnesia_text.erl"},{line,107}]},
{mnesia_tm,apply_fun,3,[{file,"mnesia_tm.erl"},{line,829}]},
{mnesia_tm,execute_transaction,5,
[{file,"mnesia_tm.erl"},{line,809}]},
{erl_eval,do_apply,6,[{file,"erl_eval.erl"},{line,576}]},
{shell,exprs,7,[{file,"shell.erl"},{line,668}]},
{shell,eval_exprs,7,[{file,"shell.erl"},{line,623}]},
{shell,eval_loop,3,[{file,"shell.erl"},{line,608}]}]}}
13> c(mnesia_text).
{ok,mnesia_text}
14> mnesia_text:write().
{atomic,ok}
15> mnesia_text:quary().
{atomic,[[6,"xiaohong6"],
[1,"xiaohong1"],
[2,"xiaohong2"],
[5,"xiaohong"]]}
16> mnesia_text:select().
{atomic,[{y_account,6,"xiaohong6","2"},
{y_account,1,"xiaohong1","11"},
{y_account,2,"xiaohong2","13"},
{y_account,5,"xiaohong","3"}]}
17> mnesia_text:select_qlc().
{atomic,[{y_account,6,"xiaohong6","2"},
{y_account,1,"xiaohong1","11"},
{y_account,2,"xiaohong2","13"},
{y_account,5,"xiaohong","3"}]}
18> mnesia_text:quary_qlc().
{atomic,[[6,"xiaohong6"],
[1,"xiaohong1"],
[2,"xiaohong2"],
[5,"xiaohong"]]}
19> c(mnesia_text).
{ok,mnesia_text}
20> mnesia_text:write_y_info().
{atomic,ok}
21> mnesia_text:where().
{atomic,["xiaohong6"]}
22> mnesia_text:where_qlc().
{atomic,["xiaohong6"]}
23> mnesia_text:where_qlc_key().
{atomic,[{y_account,5,"xiaohong","3"}]}
24> mnesia_text:where_qlc_no_key().
{atomic,[]}
25> mnesia_text:order_by().
{atomic,[{y_account,1,"xiaohong1","11"},
{y_account,2,"xiaohong2","13"},
{y_account,5,"xiaohong","3"},
{y_account,6,"xiaohong6","2"}]}
26> c(mnesia_text).
{ok,mnesia_text}
27> mnesia_text:order_by(3).
{atomic,[{y_account,5,"xiaohong","3"},
{y_account,1,"xiaohong1","11"},
{y_account,2,"xiaohong2","13"},
{y_account,6,"xiaohong6","2"}]}
28> mnesia_text:order_by_two().
{atomic,[{y_account,1,"xiaohong1","11"},
{y_account,2,"xiaohong2","13"},
{y_account,5,"xiaohong","3"},
{y_account,6,"xiaohong6","2"}]}
29> c(mnesia_text).
{ok,mnesia_text}
30> mnesia_text:join().
{atomic,[{y_info,5,"bbb","20181001",undefined}]}
31> mnesia_text:limit().
{atomic,{[{y_account,2,"xiaohong2","13"},
{y_account,5,"xiaohong","3"}],
{mnesia_select,y_account,
{tid,68,<0.31.0>},
nonode@nohost,disc_copies,
{y_account,196,2,<<>>,[],0},
[],undefined,undefined,
[{{y_account,'_','_','_'},[],['$_']}]}}}
32> mnesia_text:limit_qlc().
{atomic,[{y_account,6,"xiaohong6","2"},
{y_account,1,"xiaohong1","11"}]}
33> mnesia_text:limit_qlc().
{atomic,[{y_account,6,"xiaohong6","2"},
{y_account,1,"xiaohong1","11"}]}
34>
34>
34>
34> mnesia_text:select_count().
{atomic,4}
35> mnesia_text:delete_data().
{atomic,ok}
36> mnesia_text:quary().
{atomic,[[6,"xiaohong6"],[1,"xiaohong1"],[2,"xiaohong2"]]}
37> mnesia_text:select().
{atomic,[{y_account,6,"xiaohong6","2"},
{y_account,1,"xiaohong1","11"},
{y_account,2,"xiaohong2","13"}]}
38>
