The following is a design example to compare the two: Suppose we are maintaining a cell phone product library, in addition to include the name of the phone, brand and other basic information, but also includes standby time, design parameters such as information, how should access to data?
If you use MySQL, how do you access the data?
If you use MySQL, the basic information of the mobile phone is a single table, in addition, because of the different parameters of the mobile phone information vary greatly, so also need a parameter table to save separately.
Copy Code code as follows:
CREATE TABLE IF not EXISTS ' mobiles ' (
' ID ' int (a) unsigned not NULL auto_increment,
' Name ' VARCHAR not NULL,
' Brand ' VARCHAR not NULL,
PRIMARY KEY (' id ')
);
CREATE TABLE IF not EXISTS ' Mobile_params ' (
' ID ' int (a) unsigned not NULL auto_increment,
' mobile_id ' int (a) unsigned not NULL,
' Name ' varchar not NULL,
' Value ' varchar 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 ', ' 200 '),
(2, 1, ' Design ', ' candy bar '),
(3, 2, ' Standby time ', ' 500 '),
(4, 2, ' exterior design ', ' slider ');
Note: In order to demonstrate convenience, there is no strict adherence to the relational database paradigm design.
If you want to inquire about the standby time greater than 100 hours, and the design is a straight mobile phone, you need to follow the following way to query:
SELECT * from ' mobile_params ' WHERE name = ' standby time ' and value > 100;
SELECT * from ' mobile_params ' WHERE name = ' appearance design ' and value = ' straight ';
Note: Parameter table to facilitate, the value and string to be unified to save the string, in practice, MySQL allows the string type of the field on the numeric type of query, just need to do type conversion, how much will affect a little performance.
Two SQL results to get the intersection of the desired mobile_id, and then to the Mobiles table query can:
SELECT * from ' mobiles ' WHERE mobile_id in (mobile_id)
If you use MongoDB, how do you access the data?
If the use of MongoDB, although the theory can be used in the same way as MySQL design, but that would be boring, did not play the MongoDB as a document database advantages, in fact, using MongoDB words, and MySQL compared to the image point, Can be combined:
Copy Code code 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 ': ' Appearance design ', ' value ': ' Straight '}
]
});
Db.getcollection ("mobiles"). Insert ({
"_id": 2,
"Name": "E7",
"Brand": "Nokia",
"Params": [
{' name ': ' Standby time ', ' Value ': 500},
{' name ': ' Design ', ' value ': ' Slide '}
]
});
If you want to inquire about the standby time greater than 100 hours, and the design is a straight mobile phone, you need to follow the following way to query:
Copy Code code as follows:
Db.getcollection ("mobiles"). Find ({
"Params": {
$all: [
{$elemMatch: {"name": "Standby Time", "value": {$gt: 100}}},
{$elemMatch: {"name": "Design", "value": "Straight"}}
]
}
});
Note: Please refer to the relevant instructions in the official documentation for a detailed description of the $allused in the query,$elemMatch and other advanced usage.
MySQL needs more than one table, multiple inquiries to fix the problem, MongoDB only need a table, a query can be done, compared to complete, relative to MySQL, MongoDB appears to be better, at least in this case.
