Comparative analysis of MySQL and MongoDB design examples

The following is a design example to compare the two: suppose we are maintaining a mobile phone product library, which contains not only the phone's name, brand and other basic information, but also the standby time, appearance design and other parameter information, how to access the data?
How do you access data if you use MySQL?
If MySQL is used, the basic information of the mobile phone is a separate table. In addition, since the parameter information of different mobile phones varies greatly, another parameter table is needed to be saved separately.

 
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 ', '200'), 
(2, 1, ' Appearance design ', ' A straightforward '), 
(3, 2, ' Standby time ', '500'), 
(4, 2, ' Appearance design ', ' slide '); 

Note: for demonstration purposes, the paradigm design of relational databases is not strictly adhered to.
If you want to check the standby time more than 100 hours, and the design of the phone is straight board, you need to follow the following way:
SELECT * FROM 'mobile_params' WHERE name = 'standby time' AND value > 100;
SELECT * FROM 'mobile_params' WHERE name = 'design' AND value = 'straight board ';
Note: for convenience, the parameter table keeps the numeric value and the string as a string. In practice, MySQL allows the query of numeric type on the field of string type.
The two SQL results take the intersection to get the desired MOBILE_ID, then go to the mobiles table query:
SELECT * FROM `mobiles` WHERE mobile_id IN (MOBILE_ID)
How do you access data if you use MongoDB?
If you use MongoDB, although in theory you can use the same design as MySQL, but that would be boring, not to play the advantages of MongoDB as a document database, in fact, MongoDB and MySQL, compared to the image, can be combined:

 
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": " A straightforward "} 
] 
}); 
db.getCollection("mobiles").insert({ 
"_id": 2, 
"name": "E7", 
"brand": " nokia ", 
"params": [ 
{"name": " Standby time ", "value": 500}, 
{"name": " Appearance design ", "value": " slide "} 
] 
}); 

If you want to check the standby time more than 100 hours, and the design of the phone is straight board, you need to follow the following way:

 
db.getCollection("mobiles").find({ 
"params": { 
$all: [ 
{$elemMatch: {"name": " Standby time ", "value": {$gt: 100}}}, 
{$elemMatch: {"name": " Appearance design ", "value": " A straightforward "}} 
] 
} 
}); 

Note: please refer to the official documentation for a detailed description of $all, $elemMatch and other advanced usage used in the query.

MySQL needs multiple tables and can be solved by multiple queries, while MongoDB only needs one table and can be solved by one query. Compared with MySQL, MongoDB is better than MySQL, at least for example.