erlang mnesia 数据库实现SQL查询

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

3、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).

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

5、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).

6、关联表查询

Join关联表查询

Mnesia 在处理三个或更多表的关联查询时确实无法高效处理。Mnesia 的设计初衷是用于处理较小规模的表之间的关联查询，对于大规模的多表关联查询，性能可能会受到限制。在一个qlc下只允许两张表的关联查询。

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

三张以上复杂表的关联查询

如果需要进行复杂的多表关联查询，可能需要考虑以下方法：

1. 重新设计数据模型：通过重新设计数据模型来减少对多表关联查询的需求。尽量将相关数据组织在同一个表中，以减少关联查询的次数。
2. 手动进行多次查询和关联：将多个查询拆分为多个步骤，逐步进行关联。首先从一个表中获取数据，然后使用获取的结果进行下一个查询，以此类推。然后将这些结果手动进行关联和合并。
3. 在应用层进行关联：将数据从多个表中获取到应用层，然后在应用层进行关联。这样可以利用应用层的更强大的处理能力和灵活性来处理复杂的关联逻辑。

下面实例是采用第2种方法，将多个查询拆分为多个步骤，逐步进行关联。

%% 根据用户ID查询菜单权限
%% ==============================================================================
%% <select id="selectMenuPermsByUserId" parameterType="Long" resultType="String">
%%    select distinct m.perms
%%    from sys_menu m
%%             left join sys_role_menu rm on m.menu_id = rm.menu_id
%%             left join sys_user_role sur on rm.role_id = sur.role_id
%%             left join sys_role r on r.role_id = sur.role_id
%%    where m.status = '0'
%%      and r.status = '0'
%%      and sur.user_id = #{userId}
%%</select>
%% ==============================================================================
-spec select_menu_perms_by_user_id(integer()) -> list().
select_menu_perms_by_user_id(UserId) ->
    F = fun() ->
        %% 获取所有启用状态的角色Id
        R_Q = qlc:q([R#?SYS_ROLE.role_id || R <- mnesia:table(?SYS_ROLE), R#?SYS_ROLE.status =:= <<"0">>]),
        R_RoleIdList = qlc:e(R_Q),
        %% 根据用户Id获取用户拥有的所有角色Id（已启用的角色）
        SUR_Q = qlc:q([SUR#?SYS_USER_ROLE.role_id || SUR <- mnesia:table(?SYS_USER_ROLE), SUR#?SYS_USER_ROLE.user_id =:= UserId,lists:member(SUR#?SYS_USER_ROLE.role_id,R_RoleIdList)]),
        SUR_RoleIdList = qlc:e(SUR_Q),
        %% 获取角色下所有的菜单Id
        RM_Q = qlc:q([RM#?SYS_ROLE_MENU.menu_id || RM <- mnesia:table(?SYS_ROLE_MENU), lists:member(RM#?SYS_ROLE_MENU.role_id, lists:usort(SUR_RoleIdList))]),
        RM_MenuIdList = qlc:e(RM_Q),
        %% 获取指定菜单Id的权限标识
        M_Q = qlc:q([M#?SYS_MENU.perms || M <- mnesia:table(?SYS_MENU), M#?SYS_MENU.status =:= <<"0">>, lists:member(M#?SYS_MENU.menu_id, lists:usort(RM_MenuIdList))]),
        M_PermList = qlc:e(M_Q),
        M_PermList
        end,
    {atomic, M_PermList} = mnesia:transaction(F),
    lists:umerge([string:tokens(string:strip(Perm), ",") || Perm <- M_PermList]).

7、Limit 查询

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

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

实践

方法3使用的是事务，也可以使用方法2的mnesia:dirty_select。

方法1：
F = fun () ->
MatchHead = #person{name='$1', sex=male, age='$2', _='_'},
Guard = [{'>', '$2', 30}],
Result = ['$1'],
L = mnesia:select(Tab,[{MatchHead, Guard, Result}])
end,
mnesia:transaction(F).

方法2：
MatchHead = #person{name='$1', sex=male, age='$2', _='_'},  % 也可包含等于条件   name=Name
Guard = [{'>', '$2', 30}], %条件 > == <
Result = ['$1'],  %结果  '$n'返回变量$n；'$$'返回所有  $变量；  '$_'返回整行；
mnesia:dirty_select(Tab,[{MatchHead, Guard, Result}]).

方法3：
F = fun() ->  
    MatchHead = #?ONLINE_TAB{type = '$1', user = '$2', _ = '_' },  
    Guard = [],  
    Result = ['$1'],
    Types = mnesia:select(?ONLINE_TAB, [{MatchHead, Guard, Result}]),
    io:format("topic list : ~p~n", [Types]),
    %lists:foreach(fun(Type) -> plugin:start(Type) end, Types)
    [plugin:start(Type) || Type <- Types]
    end,  
    mnesia:transaction(F).