MySQL is a star in relational databases, and MongoDB is a leader in document-based databases.
MySQL is a star in relational databases, and MongoDB is a leader in document-based databases.
The following is a design example to compare the two: assume that we are maintaining a mobile phone product library, which includes not only the phone name, brand, and other basic information, but also the standby time, how do I access data for parameter information such as design?
How can I access data using MySQL?
If MySQL is used, the basic information of the mobile phone is a separate table. In addition, because the parameter information of different mobile phones varies greatly, a parameter table is also required for separate storage.
The Code is as follows:
Create table if not exists 'mobiles '(
'Id' int (10) unsigned not null AUTO_INCREMENT,
'Name' VARCHAR (100) not null,
'Brand' VARCHAR (100) not null,
Primary key ('id ')
);
Create table if not exists 'mobile _ params '(
'Id' int (10) unsigned not null AUTO_INCREMENT,
'Mobile _ id' int (10) unsigned not null,
'Name' varchar (100) not null,
'Value' varchar (100) not null,
Primary key ('id ')
);
Insert into 'mobiles' ('id', 'name', 'brand') VALUES
(1, 'me525', 'motorola '),
(2, 'e7 ', 'nokia ');
Insert into 'mobile _ params '('id', 'mobile _ id', 'name', 'value') VALUES
(1, 1, 'standby time', '123 '),
(2, 1, 'Design output', 'dashboard '),
(3, 2, 'standby time', '123 '),
(4, 2, 'Design output', 'slide ');
Note: For ease of demonstration, we did not strictly abide by the relational database paradigm design.
If you want to query mobile phones with the standby time greater than 100 hours and the design is straight-forward, you need to query them as follows:
SELECT * FROM 'mobile _ params 'WHERE name = 'standby time' AND value> 100;
SELECT * FROM 'mobile _ params 'WHERE name = 'designs' AND value = 'dashboard ';
NOTE: For the convenience of the parameter table, the values and strings are saved as strings. In actual use, MySQL allows query of the value type on the string type fields, but only requires type conversion, performance may be affected.
Obtain the expected MOBILE_ID from the intersection of the two SQL statements, and then query the mobiles table:
SELECT * FROM 'mobiles' WHERE mobile_id IN (MOBILE_ID)
How can I access data if I use MongoDB?
If MongoDB is used, although the same design scheme as MySQL can be used in theory, it seems boring and does not take full advantage of MongoDB as a document-type database. In fact, if MongoDB is used, compared with MySQL, the image can be combined into one:
The Code is as follows:
Db. getCollection ("mobiles"). ensureIndex ({
"Params. name": 1,
"Params. value": 1
});
Db. getCollection ("mobiles"). insert ({
"_ Id": 1,
"Name": "ME525 ",
"Brand": "Motorola ",
"Params ":[
{"Name": "standby time", "value": 200 },
{"Name": "design", "value": "straight panel "}
]
});
Db. getCollection ("mobiles"). insert ({
"_ Id": 2,
"Name": "E7 ",
"Brand": "Nokia ",
"Params ":[
{"Name": "standby time", "value": 500 },
{"Name": "design", "value": "slide "}
]
});
If you want to query mobile phones with the standby time greater than 100 hours and the design is straight-forward, you need to query them as follows:
The Code is as follows:
Db. getCollection ("mobiles"). find ({
"Params ":{
$ All :[
{$ ElemMatch: {"name": "standby time", "value": {$ gt: 100 }}},
{$ ElemMatch: {"name": "design", "value": "straight panel "}}
]
}
});
Note: For more information about advanced usage such as used in the query, see the relevant instructions in the official documentation.
MySQL requires multiple tables for multiple queries to solve the problem. MongoDB only needs one table for one query. Compared with MySQL, MongoDB is superior, at least this example is used.
