Erlang Mnesia database for SQL queries

Mnesia is a distributed database management system, suitable for telecommunications and other applications requiring continuous operation and with the soft real-time characteristics of the Erlang, more and more attention and use, but the current Mnesia data is not much, many only the official user guide. The following will focus on how the Mnesia database implements SQL queries to implement SQL operations such as Select/insert/update/where/order By/join/limit/delete.

The definition of the table structure in the example:

[Plain]View Plaincopy

1. Percent of Account table structure
2. -record (y_account,{ID, account, password}).
4. Data table structure
5. -record (Y_info, {ID, nickname, birthday, sex}).

1. Create Table/delete Table operation

[Plain]View Plaincopy

2. %%===============================================
4. Percent CREATE TABLE y_account (ID int, account varchar (50),
6. Percent Password varchar (+), primary key (ID));
8. %%===============================================
12. Percent use Mnesia:create_table
14. Mnesia:create_table (Y_account,[{attributes, Record_info (Fields, Y_account)},
16. {Type,set}, {disc_copies, [node ()]}]).
20. %%===============================================
22. Percent drop table y_account;
24. %%===============================================
28. Percent use Mnesia:delete_table
30. Mnesia:delete_table (Y_account).

Note: The parameter meaning can be seen in the document, {Type,set} represents the ID as the primary key, the ID is not allowed to repeat, if {Type,bag},id can be repeated, but the entire record cannot be repeated

2. Select Query

Query all records

[Plain]View Plaincopy

2. %%===============================================
4. Percent SELECT * from Y_account
6. %%===============================================
10. Percent use Mnesia:select
12. F = Fun ()
14. Matchhead = #y_account {_ = ' _ '},
16. Guard = [],
18. Result = [' $_ '],
20. Mnesia:select (Y_account, [{matchhead, Guard, Result}])
22. End
24. Mnesia:transaction (F).
28. Percent use QLC
30. F = Fun ()
32. Q = Qlc:q ([E | | E <-mnesia:table (Y_account)]),
34. Qlc:e (Q)
36. End
38. Mnesia:transaction (F).

Querying records for part of a field

[Plain]View Plaincopy

2. %%===============================================
4. Percent of select Id,account from Y_account
6. %%===============================================
10. Percent use Mnesia:select
12. F = Fun ()
14. Matchhead = #y_account {id = ' $ ', account = ' $ $ ', _ = ' _ '},
16. Guard = [],
18. Result = [' $$ '],
20. Mnesia:select (Y_account, [{matchhead, Guard, Result}])
22. End
24. Mnesia:transaction (F).
28. Percent use QLC
30. F = Fun ()
32. Q = Qlc:q ([[E#y_account.id, E#y_account.account] | | E <-mnesia:table (Y_account)]),
34. Qlc:e (Q)
36. End
38. Mnesia:transaction (F).

3. Insert/update operation

Mnesia is based on the primary key to update the record, if the primary key does not exist insert

[Plain]View Plaincopy

1. %%===============================================
2. Percent INSERT into y_account (Id,account,password) VALUES (5, "Xiaohong", "123")
3. Percent on duplicate key update account= "Xiaohong", password= "123";
4. %%===============================================
6. Percent use Mnesia:write
7. F = Fun ()
8. ACC = #y_account {id = 5, account= "Xiaohong", password= "123"},
9. Mnesia:write (ACC)
10. End
11. Mnesia:transaction (F).

4. Where Query

[Plain]View Plaincopy

2. %%===============================================
4. Percent of select account from Y_account where id>5
6. %%===============================================
10. Percent use Mnesia:select
12. F = Fun ()
14. Matchhead = #y_account {id = ' $ ', account = ' $ $ ', _ = ' _ '},
16. Guard = [{' > ', ' $ ', 5}],
18. Result = [' $ $ '],
20. Mnesia:select (Y_account, [{matchhead, Guard, Result}])
22. End
24. Mnesia:transaction (F).
28. Percent use QLC
30. F = Fun ()
32. Q = Qlc:q ([E#y_account.account | | E <-mnesia:table (Y_account), e#y_account.id>5]),
34. Qlc:e (Q)
36. End
38. Mnesia:transaction (F).

If you look for a record of the primary key key=x, you can also do this subquery:

[Plain]View Plaincopy

1. %%===============================================
2. Percent SELECT * from Y_account where id=5
3. %%===============================================
5. F = Fun ()
6. Mnesia:read ({y_account,5})
7. End
8. Mnesia:transaction (F).

If you are looking for records for non-primary key field=x, you can query as follows:

[Plain]View Plaincopy

1. %%===============================================
2. Percent SELECT * from Y_account where account= ' Xiaomin '
3. %%===============================================
5. F = Fun ()
6. Matchhead = #y_account {id = ' _ ', account = "Xiaomin", Password = ' _ '},
7. Guard = [],
8. Result = [' $_ '],
9. Mnesia:select (Y_account, [{matchhead, Guard, Result}])
10. End
11. Mnesia:transaction (F).

5. Order by Query

[Plain]View Plaincopy

2. %%===============================================
4. Percent SELECT * from Y_account ORDER by ID ASC
6. %%===============================================
10. Percent use QLC
12. F = Fun ()
14. Q = Qlc:q ([E | | E <-mnesia:table (Y_account)]),
16. Qlc:e (Qlc:keysort (2, Q, [{Order, Ascending}]))
18. End
20. Mnesia:transaction (F).
24. The second way to use QLC
26. F = Fun ()
28. Q = Qlc:q ([E | | E <-mnesia:table (Y_account)]),
30. Order = Fun (A, B)
32. B#y_account.id > A#y_account.id
34. End
36. Qlc:e (Qlc:sort (Q, [{Order, Order}]))
38. End
40. Mnesia:transaction (F).

6. Join Association Table Query

[Plain]View Plaincopy

2. %%===============================================
4. Percent select y_info.* from Y_account join Y_info on (y_account.id = y_info.id)
6. Percent of where y_account.account = ' xiaomin '
8. %%===============================================
12. Percent use QLC
14. F = Fun ()
16. Q = Qlc:q ([Y | | X <-mnesia:table (Y_account),
18. X#y_account.account =:= "Xiaomin",
20. Y <-mnesia:table (y_info),
22. X#y_account.id =:= Y#y_info.id
24. ]),
26. Qlc:e (Q)
28. End
30. Mnesia:transaction (F).

7. Limit Query

[Plain]View Plaincopy

2. %%===============================================
4. Percent SELECT * from Y_account limit 2
6. %%===============================================
10. Percent use Mnesia:select
12. F = Fun ()
14. Matchhead = #y_account {_ = ' _ '},
16. Mnesia:select (Y_account, [{matchhead, [], [' $_ ']}], 2, none)
18. End
20. Mnesia:transaction (F).
24. Percent use QLC
26. F = Fun ()
28. Q = Qlc:q ([E | | E <-mnesia:table (Y_account)]),
30. QC = Qlc:cursor (Q),
32. Qlc:next_answers (QC, 2)
34. End
36. Mnesia:transaction (F).

8. Select count (*) query

[Plain]View Plaincopy

1. %%===============================================
2. Percent of SELECT count (*) from Y_account
3. %%===============================================
5. Percent use Mnesia:table_info
6. F = Fun ()
7. Mnesia:table_info (y_account, size)
8. End
9. Mnesia:transaction (F).

9. Delete Query

[Plain]View Plaincopy

1. %%===============================================
2. Percent Delete from Y_account where id=5
3. %%===============================================
5. Percent use Mnesia:delete
6. F = Fun ()
7. Mnesia:delete ({y_account, 5})
8. End
9. Mnesia:transaction (F).

Note: Using the QLC module query, you need to declare "-include_lib (" Stdlib/include/qlc.hrl ") at the top of the file.", otherwise the compilation will produce "WARNING:QLC:Q/1 called, but" QLC.HRL "not Included "warning.

Update Description:

2013/11/20 added limit query for Mnesia:select mode

Erlang Mnesia database for SQL queries