Click a thumbs-up if you like! Source <– click here to view

The introduction of

When I see some comments like the one below. I’m curious how this feature is made. This is actually a recursive operation of MySQL. Let’s see how to implement a recursive query in MySQL.

Design database

If you look at this database design, you will see that it has a parent node all the way up to the root node, so when we design the database, we should set a parenTID field. So, we can get the following database.

The SQL script is as follows

CREATE TABLE digui(
	id INT(11) NOT null auto_increment,
	msg VARCHAR(255) not NULL COMMENT 'Content of comments',
	parentid int(11) not null COMMENT 'Last one',
	PRIMARY KEY(id))ENGINE=INNODB auto_increment = 100 DEFAULT CHARSET=utf8mb4;

INSERT into `digui`(msg, parentid) VALUES ('A'.0);
INSERT into `digui`(msg, parentid) VALUES('B'.1);
INSERT into `digui`(msg, parentid) VALUES('D'.3);
INSERT into `digui`(msg, parentid) VALUES('C'.2);
Copy the code

In fact, there are many recursive query methods to implement MySQL

  1. Use MySQL stored procedures
  2. Application layer code recursion
  3. MyBatis collection tag

Scenario 1 application layer code recursion

// Application layer recursive query
@Override
public List<Digui> getAll(int parent) {
    List<Digui> deptVosList=new ArrayList<>();
    QueryWrapper queryWrapper = new QueryWrapper();
    queryWrapper.eq("parentid", parent);
    List<Digui> list1 = list(queryWrapper);
    for (Digui digui: list1) {
        Digui digui1 = new Digui();
        digui1.setId(digui.getId());
        digui1.setMsg(digui.getMsg());
        digui1.setParentid(digui.getParentid());
        // Assignment is called recursively here
        digui1.setDiguiList(getAll(digui.getId()));
        deptVosList.add(digui1);
    }
    return deptVosList;

}
Copy the code

Option 2 Collection tag of MyBatis

 <resultMap id="RecursiveMap" type="com.example.lsbdigui.entity.Digui">
        <result property="id" column="id"/>
        <result property="msg" column="msg"/>
        <result property="parentid" column="parentid"/>
        <collection property="diguiList" ofType="com.example.lsbdigui.entity.Digui"
                    select="com.example.lsbdigui.mapper.DiguiMapper.getAllBySQL"
                    column="id"/>
</resultMap>
<select id="getAllBySQL" resultMap="RecursiveMap">
    select *
    from digui
    where parentid = #{parent}
</select>
Copy the code

SQL recursive query using
, < SELECT > tags.

The results of

{
    "code": 200."msg": "Correct return"."date": [{"id": 100."msg": "A"."parentid": 0."diguiList": [{"id": 101."msg": "B"."parentid": 100."diguiList": [{"id": 103."msg": "C"."parentid": 101."diguiList": [{"id": 102."msg": "D"."parentid": 103."diguiList": []}]}]}]}Copy the code

contrast

advice

The application layer can query all the data at once and then recursively find the data needed, which can reduce database queries and improve performance.

reference

  • blog.rxliuli.com/p/5830226b/
  • Juejin. Cn/post / 684490…
  • Blog.csdn.net/u014079773/…

Pay attention to wechat public number, mobile terminal at any time to read