Shulou(Shulou.com)05/31 Report--

Editor to share with you MySQL and MongoDB should be how to obtain data, I believe that most people do not know much, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

MySQL is the star of relational databases, and MongoDB is the leader of document databases. Two kinds of storage tools, MySQL and MongoDB, design the database structure respectively. In the design of MongoDB, we make use of the schema-free characteristics of MongoDB. The following editor will explain how MySQL and MongoDB should obtain data.

How should MySQL and MongoDB get data

MySQL is the star of relational databases, and MongoDB is the leader of 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 phone, but also parameter information such as standby time and design. How should I access the data?

If you use MySQL, how should you access the data?

If you use MySQL, the basic information of the phone is a separate table, and because the parameter information of different phones varies greatly, you also need a parameter table to save separately.

CREATETABLEIFNOTEXISTS`mobiles` (

`id`int (10) unsignedNOTNULLAUTO_INCREMENT

`name`VARCHAR (100) NOTNULL

`brand`VARCHAR (100) NOTNULL

PRIMARYKEY (`id`)

)

CREATETABLEIFNOTEXISTS`mobile _ params` (

`id`int (10) unsignedNOTNULLAUTO_INCREMENT

`mobile_ id`int (10) unsignedNOTNULL

`name`varchar (100) NOTNULL

`value`varchar (100) NOTNULL

PRIMARYKEY (`id`)

)

INSERTINTO`mobiles` (`id`, `name`, `brand`) VALUES

(1, "ME525", "Motorola")

(2, recording E7, recording 'Nokia')

INSERTINTO`mobile _ params` (`id`, `mobile_ id`, `name`, `value`) VALUES

('standby time', '200')

(2dy1) 'exterior design', 'straight board')

(3pd2 'standby time,' 500')

(4pd2 'exterior design', 'slide cover')

How should MySQL and MongoDB get data

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

If you want to query a phone with a standby time of more than 100 hours and a straight design, you need to query it as follows:

SELECT* From`mobile _ params`WherEname = 'standby time' ANDvalue > 100

SELECT* From`mobile _ params`WEREname = 'exterior design' ANDvalue=' straight board'

Note: parameter table for convenience, the values and strings are saved as strings. In practice, MySQL allows query of numeric types on fields of string type, but type conversion is needed, which will affect performance a little bit.

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

SELECT* From`mobiles`WhEREmobile _ idIN (MOBILE_IDS)

If you use MongoDB, how should you access the data?

If you use MongoDB, although you can theoretically adopt the same design scheme as MySQL, it will be boring, 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, you can combine the two 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": "Design", "value": "straight"}]}) Db.getCollection ("mobiles") .insert ({"_ id": 2, "name": "E7", "brand": "Nokia", "params": [{"name": "standby time", "value": 500}, {"name": "Design", "value": "slider"}]}) If you want to query a phone with a standby time of more than 100 hours and a straight design You need to query db.getCollection ("mobiles") .find ({"params": {$all: [{$elemMatch: {"name": "standby time", "value": {$gt:100}, {$elemMatch: {"name": "Design", "value": "straight"}]}})

Note: for a detailed description of the advanced usage such as $all,$elemMatch used in the query, please refer to the relevant instructions in the official documentation.

MySQL requires multiple tables, multiple queries to solve the problem, MongoDB only needs one table, a query can be completed, compared to MySQL, MongoDB appears to be better than MySQL, at least in this case.

The above is all the contents of the article "how to get data from MySQL and MongoDB". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.