Comparison and analysis of design examples of MySQL and MongoDB _mongodb

Source: Internet
Author: User
Tags mongodb one table
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.