Erlang Mnesia 数据库实现SQL查询

由 David Gao 发布于：

编程, Erlang, 数据库

Erlang的Mnesia是一款Key Value数据库，但是我们很多时候习惯使用SQL进行查询，本文介绍如何在Mnesia上实现类似SQL的查询方法。

Mnesia是一个分布式数据库管理系统，它是一款Key Value数据库，适合于电信和软实时特性的Erlang应用。但是Mnesia并没有提供类似SQL的查询方案，我们下面的内容将着重说明Mnesia数据库如何实现SQL查询，实现常用的SQL操作。

我们先定义两张表

1
2
3
4
 
%% 用户表
-record(tb_account,{id,account,password}).
%% 资料表
-record(tb_info,{id,nickname,birthday,gender}).

在实现类SQL的查询时候，我们有两种方法，一种是Match Specifications，一种是QLC。一些查询，两种方法都可以实现，而有一些操作，我们只能使用其中的一种。为了能使用QLC，我们需要在文件顶部声明“-include_lib(“stdlib/include/qlc.hrl”).”，否则编译时会产生“Warning: qlc:q/1 called, but “qlc.hrl” not included”的警告。

Create/Delete Table操作

1
2
3
4
5
6
7
 
%% Create Table
mnesia:create_table( tb_account,
    [{attributes, record_info(fields, tb_account)},
     {type,set},
     {disc_copies, [node()]}]).
%% Delete Table
mnesia:delete_table(tb_account) .

此处我们需要注意mnesia:create_table的type参数是set，这表明我们将tb_account表的id设置为唯一的。更多关于mnesia:create_tablede创建时所使用的[create_option()]可以查看Mnesia文档

Select 操作

查询所有的字段

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
%% SQL: SELECT * FROM tb_account;

%% 使用 mnesia:select
F = fun() ->
    MatchHead = #y_account{ _ = '_' },
    Guard = [],
    Result = ['$_'],
    mnesia:select(tb_account, [{MatchHead, Guard, Result}])
end,
mnesia:transaction(F). %% 注意了，Mnesia的操作需要使用transaction确保数据安全
%% 使用QLC
F = fun() ->
    Q = qlc:q([E || E <- mnesia:table(tb_account)]),
    qlc:e(Q)
end,
mnesia:transaction(F).

查询所需的字段

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
%% SQL: SELECT id,account FROM tb_account;
%% 使用mnesia:select
F = fun() ->
    MatchHead = #tb_account{id = '$1', account = '$2', _ = '_' },
    Guard = [],
    Result = ['$$'],
    mnesia:select(tb_account, [{MatchHead, Guard, Result}])
end,
mnesia:transaction(F).

%% 使用QLC
F = fun() ->
    Q = qlc:q([[E#tb_account.id, E#tb_account.account] || E <- mnesia:table(tb_account)]),
    qlc:e(Q)
end,
mnesia:transaction(F).

使用WHERE条件

如果我们直接使用主键进行查找例如: id=1，我们可以使用非常快捷的查询方法

1
2
3
4
5
 
%% SQL: SELECT * FROM tb_account WHERE id=1;
F = fun() ->
  mnesia:read({tb_account,1})
end,
mnesia:transaction(F).

如果通过非主键或者不是精确匹配主键的条件查询记录，可以如下方法进行查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
%% SQL: SELECT account FROM tb_account WHERE id > 1;
%% 使用 mnesia:select  
F = fun() -> 
    MatchHead = #tb_account{id = '$1', account = '$2', _ = '_' },
    Guard = [{'>', '$1', 1}],
    Result = ['$2'],
    mnesia:select(tb_account, [{MatchHead, Guard, Result}])
end,
mnesia:transaction(F).

%% 使用 QLC
F = fun() ->
    Q = qlc:q([E#tb_account.account || E <- mnesia:table(tb_account), E#tb_account.id>1]),
    qlc:e(Q)
end,
mnesia:transaction(F).

%% SQL: SELECT * from tb_account WHERE account='David';
%% 使用mnesia:select

F = fun() ->
    MatchHead = #tb_account{ id = '_', account = "David", password = '_' },
    Guard = [],
    Result = ['$_'],
    mnesia:select(tb_account, [{MatchHead, Guard, Result}])
end,
mnesia:transaction(F).

%% 使用QLC
F = fun() ->
    Q = qlc:q([E || E <- mnesia:table(tb_account), E#tb_account.accout =:= "David"]),
    qlc:e(Q)
end,
mnesia:transaction(F).

ORDER BY 查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
%% SQL: SELECT * FROM tb_account ORDER BY id ASC

%% 使用QLC，方法1
F = fun() ->
    Q = qlc:q([E || E <- mnesia:table(tb_account)]),
    qlc:e(qlc:keysort(2, Q, [{order, ascending}]))
end,
mnesia:transaction(F).

%% 使用QLC，方法2
F = fun() ->
    Q = qlc:q([E || E <- mnesia:table(tb_account)]),
    SortFun = fun(A, B) ->
        B#tb_account.id > A#tb_account.id
    end,
    qlc:e(qlc:sort(Q, [{order, SortFun}]))
end,
mnesia:transaction(F).

JOIN 查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
%% SQL: SELECT tb_info.* FROM tb_account
%% JOIN tb_info ON  tb_info.id = tb_account.id
%% WHERE tb_account.account = 'David';

%% 使用mnesia:select
F = fun() ->
    M1 = #tb_account{id ='$1', account = "David",_ = '_' },
    G2 = [],
    R2 = ['$1'],
    [Account]  = mnesia:select(tb_account, [{M1,G2, R2}]),
    M2 = #tb_info{id=Account,_ = '_'},
    G2 = [],
    R2 = ['$_'],
    mnesia:select(tb_info,[{M2,G2,R2}])
end,
mnesia:transaction(F).

%% 使用QLC
F = fun() ->
    Q = qlc:q([Y || X <- mnesia:table(tb_account),
                    X#tb_account.account =:= "David",
                    Y <- mnesia:table(tb_info),
                    Y#tb_info.id  =:= X#tb_account.id]),
    qlc:e(Q)
end,
mnesia:transaction(F).

LIMIT 操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
%% SQL: SELECT * FROM tb_account LIMIT 0,2;
%% 使用 mnesia:select
F = fun() -> 
    MatchHead = #tb_account{ _ = '_' },
    mnesia:select(tb_account, [{MatchHead, [], ['$_']}], 2,read)
end,
mnesia:transaction(F).

%% 使用 QLC
F = fun() ->
    Q = qlc:q([E || E <- mnesia:table(tb_account)]),
    QC = qlc:cursor(Q),
    qlc:next_answers(QC, 2)
end,
mnesia:transaction(F).

Insert/Update 操作

tb_account中的id在建表时被设为了默认的主键。因为我们使用的数据表类型是set，所以当主键重复的时候，Mnesia会更新这个记录。但是如果我们是bag的时候，就会直接插入一个新的记录。

写入操作只能使用mnesia:write进行操作，无法使用QLC。

1
2
3
4
5
6
7
 
%% SQL: INSERT INTO tb_account (id,account,password) VALUES (1,"David","123456");
%% 使用 mnesia:write
F = fun() ->
    Record = #tb_account{id = 1, account="David", password="123456"},
    mnesia:write(Record) 
end,
mnesia:transaction(F).

总结

Mnesia是一款非常好用的分布式Key Value数据库，并且配合Match Spec和QLC完全可以模拟出我们常见的SQL操作，善用这些工具会让我们在开发Erlang应用时会更加得心应手。