The difference between MySQL and MongoDB database design

MySQL is the star among relational databases, and MongoDB is the leader among document databases. Let’s compare the two through a design example: Suppose we are maintaining a mobile phone product library, which contains not only basic information such as the name and brand of the mobile phone, but also parameter information such as standby time and appearance design. How should we access the data?

If you use MySQL, the basic information of the mobile phone is a separate table. In addition, because the parameter information of different mobile phones is very different, a parameter table is needed to save it 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 ‘, ‘straight’), (3, 2, ‘standby time’, ‘500’), (4, 2, ‘appearance design’, ‘slider’);

Note: For the convenience of demonstration, the paradigm design of relational database is not strictly followed.

If you want to inquire about mobile phones with a standby time greater than 100 hours and a candy bar design, you need to inquire as follows:

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 saves numeric values ​​and strings as strings. In actual use, MySQL allows numeric type queries on string type fields, but type conversion is required, which will affect performance somewhat.

Take the intersection of the two SQL results to get the desired MOBILE_ID, and then go to the mobiles table to query:

SELECT * FROM `mobiles` WHERE mobile_id IN (MOBILE_ID)

If you use MongoDB, although you can use the same design scheme as MySQL in theory, it will be boring in that case, and it will not give full play to the advantages of MongoDB as a document database. In fact, if you use MongoDB, compared with MySQL, it will be more vivid. Say, the two can be combined into one:

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”: “Candy bar”} ] }); db.getCollection(“mobiles”).insert({ “_id”: 2, “name”: “E7”, “brand”: “Nokia”, “params”: [ {“name “: “Standby Time”, “value”: 500}, {“name”: “Appearance Design”, “value”: “Sliding Cover”} ] });

If you want to inquire about mobile phones with a standby time greater than 100 hours and a candy bar design, you need to inquire as follows:

db.getCollection(“mobiles”).find({ “params”: { $all: [ {$elemMatch: {“name”: “Standby Time”, “value”: {$gt: 100}}}, {$ elemMatch: {“name”: “Design”, “value”: “Straight”}} ] } });

Note: For the detailed introduction of advanced usages such as $all and $elemMatch used in the query, please refer to the relevant instructions in the official documentation.

MySQL needs multiple tables and multiple queries can solve the problem. MongoDB only needs one table and can be solved with one query. The comparison is complete. Compared with MySQL, MongoDB is even better, at least in this case.