erlang-mnesia数据库操作

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>

转：https://blog.csdn.net/mycwq/article/details/12506085

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,686评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,668评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,160评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,736评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,847评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,043评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,129评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,872评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,318评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,645评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,777评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,470评论 4赞 333
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,126评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,861评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,095评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,589评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,687评论 2赞 351

推荐阅读更多精彩内容

  • 关于生气的一些事儿
    1. 跟朋友约好见面时,朋友在约定的时间没有及时到,本来的好心情就这样没了一大半~ 朋友来了便会抱怨都是因为你迟到...
    冰蓝007阅读 491评论 6赞 2
  • 赞庐阳第一怪
    脑门锃亮泛金光，青丝闲卧露锋芒。 阔脸白皙胜鲜肉，桃眼清澈蕴诗行。 铁骨铮铮透傲气，文风洒脱传书香。 简书文武个个...
    风筝_5c59阅读 382评论 45赞 23
  • 2018-03-26
    木木无纹阅读 133评论 0赞 3