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

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!

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.