Mnesia: Erlang数据库（一）（学习笔记）

夲撻鲎龇

于 2024-01-03 00:05:04 发布

阅读量444

点赞数 7

文章标签：数据库 erlang 开发语言

本文链接：https://blog.csdn.net/m0_62961827/article/details/135351488

版权

本文详细介绍了在Erlang环境中使用Mnesia数据库管理系统，包括如何创建表、定义记录结构、执行SQL等操作，以及事务处理和数据增删示例。

摘要由CSDN通过智能技术生成

在下载和安装Erlang后,在磁盘上的数千个文件之中,已经内置了一个相当完备的数据库管理系统，它的名字叫做Mnesia。它非常快，而且，更妙的是可以直接存储任意的Erlang数据结构
Mnesia可以根据需要灵活配置。比如说，既可以把数据表存储在内存中(数据的访问速度)，也可以存储在磁盘上(数据的持久性)，甚至还可以在不同的机器上建立同一份数据的多个副

test mnesia.erl

-modu1e(test_mnesia).
-import(lists, [foreach/2]).
-compile(export_all).

-include_lib("stdlib/include/qlc.hrl").
-record(shop, {item, quantity, cost}).
-record(cost, {name, price}).
-record(design, {id, plan}).
do_this_once() ->
  mnesia:create_schema([node()]),
  mnesia:start(),
  mnesia:create_table(shop, [{attributes, record_info(fields, shop)}]),
  mnesia:create_table(cost, [{attributes, record_info(fields, cost)}]),
  mnesia:create_table(design, [{attributes, record_info(fields, design)}]),
  mnesia:stop().

start() ->
  mnesia:start(),
  mnesia:wait_for_tables([shop, cost, design], 20000).

demo(select_shop) -> do(qlc:q([X || X <- mnesia:table(shop)]));

demo(select_some) -> do(qlc:q([{X#shop.item, X#shop.quantity} || X <- mnesia:table(shop)]));

demo(reorder) -> do(qlc:q([X#shop.item || X <- mnesia:table(shop), X#shop.quantity < 250]));


demo(join) -> do(qlc:q([X#shop.item || X <- mnesia:table(shop),
  X#shop.quantity < 250,
  Y <- mnesia:table(cost),
  X#shop.item =:= Y#cost.name,
  Y#cost.price < 2])).
do(Q) ->
  F = fun() -> qlc:e(Q) end,
  {atomic, Val} = mnesia:transaction(F),
  Val.

example_tables() ->
  [{shop, apple, 20, 2.3},
    {shop, orange, 100, 3.8},
    {shop, pear, 200, 3.6},
    {shop, banana, 420, 4.5},
    {shop, potato, 2456, 1.2},

    {cost, apple, 1.5},
    {cost, orange, 2.4},
    {cost, pear, 2.2},
    {cost, banana, 1.5},
    {cost, potato, 0.6}].

add_shop_item(Name, Quantity, Cost) ->
  Row = #shop{item = Name, quantity = Quantity, cost = Cost},
  F = fun() ->
    mnesia:write(Row)
      end,
  mnesia:transaction(F).

remove_shop_item(Item) ->
  Oid = {shop, Item},
  F = fun() ->
    mnesia:delete(Oid)
      end,
  mnesia:transaction(F).

farmer(Nwant) ->
  F = fun() ->
    [Apple] = mnesia:read({shop, apple}),
    Napples = Apple#shop.quantity,
    Apple1 = Apple#shop{quantity = Napples + 2 * Nwant},
    mnesia:write(Apple1),
    [Orange] = mnesia:read({shop, orange}),
    NOranges = Orange#shop.quantity,
    if
      NOranges >= Nwant ->
        N1 = NOranges - Nwant,
        Orange1 = Orange#shop{quantity = N1},
        mnesia:write(Orange1);
      true ->
        mnesia:abort(oranges)
    end
      end,
  mnesia:transaction(F).

reset_tables() ->
  mnesia:clear_table(shop),
  mnesia:clear_table(cost),
  F = fun() ->
    foreach(fun mnesia:write/1, example_tables())
      end,
  mnesia:transaction(F).

add_plans() ->
  D1 = #design{id = {joe, 1},
    plan = {circle, 10}},
  D2 = #design{id = fred,
    plan = {rectangle, 10, 5}},
  D3 = #design{id = {jane, {house, 23}},
    plan = {house,
      [{floor, 1, [{doors, 3},
        {windows, 12},
        {rooms, 5}]},
        {floor, 2,
          [{doors, 2},
            {rooms, 4},
            {windows, 15}]}]}},
  F = fun() ->
    mnesia:write(D1),
    mnesia:write(D2),
    mnesia:write(D3)
      end,
  mnesia:transaction(F).

get_plan(PlanId) ->
  F = fun() -> mnesia:read({design, PlanId}) end,
  mnesia:transaction(F).

数据库查询

在Mnesia中表示这样的表,定义表示每列记录的结构。记录的定义是这样的：

-record(shop,{item,quantity,cost}).
-record(cost,{name,price}).

创建并填充数据库

1> c(test_mnesia).
{ok, test_mnesia}
2 > test_mnesia:do_this_once().
=INFO REPORT== == 2-Jan-2024 :: 23:35:52 == =
  application: mnesia
  exited: stoppedtype: temporary
stopped

选取表中所有的数据

erlang语句

demo(select_shop) -> do(qlc:q([X ||X <- mnesia:table(shop)]));

等价的sql 语句：

SQL equivalent SELECT * FROM shop;

这里的重点在于对qlc:q的调用，它负责把参数中的查询语句编译为用来执行数据库查询的内部形式。我们把得到的编译结果交给一个名为do(的函数，这个函数也是在test_mnesia中定义的。它的职责是运行这个查询，并返回结果。为了便于在er1中执行，我们将整个过程都包含在demo(select_shop)函数中。

1> test mnesia:start().
ok
2>test_mnesia:reset_ tab1es().
{atomic,ok}
3>test_mnesia:demo(select_shop).
[{shop , orange , 100,3.80000},
{shop , pear,200,3.60000},
{shop, banana,420,4.50000},
{shop ,potato,2456,1.20000},
{shop , apple,20,2.30000}]

选取表中的数据

erlang语句

demo(select_some) -> do(qlc:q([{X#shop.item, X#shop.quantity} || X <- mnesia:table(shop)]));

等价的sql 语句：

SQL equivalent SELECT item,quantity FROM shop;

这个查询从shop表中选取品名和数量这两列的数据。

4>test_mnesia:demo(select some).
[{orange,100}, {pear,200},{banana,420} ,{potato,2456},{apple,20}]

按条件选取表中的数据

下面的查询从shop表中选取所有库存量小于250的条目:

erlang语句

demo(reorder) -> do(qlc:q([X#shop.item || X <- mnesia:table(shop), X#shop.quantity < 250]));

等价的sql 语句：

SQL equivalent SELECT shop.item FROM shop WHERE shop.quantity < 250;

5>test_mnesia:demo(reorder).
[orange,pear,apple]

从两个表选取数据（关联查询)

限制条件是库存少于250而且单价低于2.0个货币单位

erlang语句

demo(join) -> do(qlc:q([X#shop.item || X <- mnesia:table(shop),
  X#shop.quantity < 250,
  Y <- mnesia:table(cost),
  X#shop.item =:= Y#cost.name,
  Y#cost.price < 2])).

等价的sql 语句：

SQL equivalent SELECT shop.item,shop.quantity,cost.name,cost.price FROM shop,cost WHERE shop.item = cost.nameAND cost.price < 2 AND shop.quantity < 250

6>test_mnesia:demo(join).
[apple]

增删表中的数据

向shop表插入数据

add_shop_item(Name, Quantity, Cost) ->
  Row = #shop{item = Name, quantity = Quantity, cost = Cost},
  F = fun() ->
    mnesia:write(Row)
      end,
  mnesia:transaction(F).

1> test_mnesia:start().
ok
2>test__mnesia: reset_tables().
{atomic,ok}
3>test_mnesia:demo(select_shop).
[{shop,orange,100,3.80000},
{shop ,pear,200,3.60000},
{shop, banana,420,4.50000},
{shop , potato,2456,1.20000},
{shop, apple,20,2.30000}]
4>test_mnesia:add_shopitem(orange,236,2.8).
{atomic,ok}
5>test_mnesia:demo(select_shop).
[{shop,orange,236,2.80000},
{shop , pear,200,3.60000},
{shop,banana,420,4.50000} ,
{shop, potato,2456,1.20000},
{shop , apple,20,2.30000}]

删除一行

删除一行数据，

remove_shop_item(Item) ->
  Oid = {shop, Item},
  F = fun() ->
    mnesia:delete(Oid)
      end,
  mnesia:transaction(F).

6>test_mnesia:remove_shop_item(pear).
{atomic,ok}
7>test_mnesia:demo(select_shop).
[{shop,orange,236,2.80000},
{shop,banana,420,4.50000},
{shop,potato,2456,1.20000},
{shop,apple,20,2.30000}]
8>mnesia:stop().
ok

取消一个事务

farmer(Nwant) ->
  F = fun() ->
    [Apple] = mnesia:read({shop, apple}),
    Napples = Apple#shop.quantity,
    Apple1 = Apple#shop{quantity = Napples + 2 * Nwant},
    mnesia:write(Apple1),
    [Orange] = mnesia:read({shop, orange}),
    NOranges = Orange#shop.quantity,
    if
      NOranges >= Nwant ->
        N1 = NOranges - Nwant,
        Orange1 = Orange#shop{quantity = N1},
        mnesia:write(Orange1);
      true ->
        mnesia:abort(oranges)
    end
      end,
  mnesia:transaction(F).

reset_tables() ->
  mnesia:clear_table(shop),
  mnesia:clear_table(cost),
  F = fun() ->
    foreach(fun mnesia:write/1, example_tables())
      end,
  mnesia:transaction(F).

1>test_mnesia:start().
ok
2>test mnesia:reset_tables().
{atomic,ok}
3> test mnesia:demo(select shop).
[{shop,orange, 100,3.80000},
{shop , pear,200,3.60000} ,
{shop, banana,420,4.50000} ,
{shop ,potato,2456,1.20000},
{shop ,apple,20,2.30000}]
4>test_mnesia: farmer(50).
{atomic,ok}
5> test_mnesia:demo(select_shop).
[{shop, orange, 50,3.80000},
{shop,pear,200,3.60000} ,
{shop, banana,420,4.50000},
{shop,potato,2456,1.20000} ,
{shop , app1e,120,2.30000}]
6> test mnesia: farmer(100).
{aborted, oranges}
7 > test_ mnesia:demo(select shop).
[{shop,orange, 50, 3.80000},
{shop,pear, 200, 3.60000},
ishop,banana, 420, 4.50000},
{shop,potato, 2456, 1.20000},
{shop,apple, 120, 2.30000}]

夲撻鲎龇

关注

7
点赞
踩
9

收藏

觉得还不错? 一键收藏
1
评论
Mnesia: Erlang数据库（一）（学习笔记）

在下载和安装Erlang后,在磁盘上的数千个文件之中,已经内置了一个相当完备的数据库管理系统，它的名字叫做Mnesia。它非常快，而且，更妙的是可以直接存储任意的Erlang数据结构Mnesia可以根据需要灵活配置。比如说，既可以把数据表存储在内存中(数据的访问速度)，也可以存储在磁盘上(数据的持久性)，甚至还可以在不同的机器上建立同一份数据的多个副。
复制链接

扫一扫