How to solve the query problem of database Number1 by Mysql
This article mainly introduces Mysql how to solve the database Niss1 query problem, the article is very detailed, has a certain reference value, interested friends must read it!
Brief introduction
In orm frameworks, such as hibernate and mybatis, you can set associated objects, such as user objects associated with dept
If you query n user, then you need to query dept n times. Query user is a select, and query the associated user.
Dept, it's n times, so it's a nimble 1 problem. In fact, it's more reasonable to call it 1% n.
Mybatis configuration
UserMapper.xml
The data table is as follows:
Department table
| | id | name |
User table
| | id | name | department_id | |
The requirement is to get the data of the following structures:
[{"id": 1, "name": "test", "department_id": 1, "department": {"id": 1, "name": "testing department"}}]
Method 1: circular query
Query user list
Loop user list to query the corresponding department information
$users = $db- > query ('SELECT * FROM `user`'); foreach ($users as & $user) {$users ['department'] = $db- > query (' SELECT * FROM `department`WHERE `id` ='. $user ['department_id']);}
The query times of this method are as follows: 1 times N (1 query list, N query departments), which has the lowest performance and is not desirable.
Method 2: join tables
Query user and department data through linked tables
Processing returned data
$users = $db- > query ('SELECT * FROM `user`INNER JOIN `department` ON `department`.`id` = `user`.`department _ id`'); / / the result returned by manual processing is the requirement structure
In fact, this method also has limitations, if user and department are not on the same server, you can't join tables.
Method 3: 1 query
This method first queries the user list once.
Take out the department ID in the list to form an array
Query the departments in step 2
Merge final data
The code is roughly as follows:
$users = $db- > query ('SELECT * FROM `user`); $departmentIds = []; foreach ($users as $user) {if (! in_array ($user [' department_id'], $departmentIds)) {$departmentIds [] = $user ['department_id'];}} $departments = $db- > query (' SELECT * FROM `department`WHERE id in ('.join (',', $department_id).'); $map = [] / [departmental ID = > departmental item] foreach ($departments as $department) {$map [$department ['id']] = $department;} foreach ($users as $user) {$user [' department'] = $map [$user ['department_id']]? Null;} these are all the contents of the article "how to solve the database Niss1 query problem by Mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
