7

Erlang Mnesia 数据库实现SQL查询

 9 months ago
source link: https://www.ttalk.im/2023/11/erlang-mnesia-sql-query.html?amp%3Butm_medium=Atom
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Erlang Mnesia 数据库实现SQL查询

由 David Gao 发布于： 2023-11-12

编程, Erlang, 数据库

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

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

我们先定义两张表

%% 用户表
-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操作

%% 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 操作

查询所有的字段

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

查询所需的字段

%% 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，我们可以使用非常快捷的查询方法

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

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

%% 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 查询

%% 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 查询

%% 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 操作

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

%% 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应用时会更加得心应手。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK