Node.js中实现MySQL多对多表关联，同时返回项目与技术数据

现有表结构 #

Projects_Project表 #

MariaDB [portfolioDB]> describe Projects_Project;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| projectId   | int(4)       | NO   | PRI | NULL    | auto_increment |
| name        | varchar(150) | NO   |     | NULL    |                |
| description | varchar(500) | NO   |     | NULL    |                |
| view        | varchar(200) | YES  |     | NULL    |                |
| code        | varchar(200) | YES  |     | NULL    |                |
| date        | date         | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)

Projects_Technology表 #

MariaDB [portfolioDB]> describe Projects_Technology;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| technologyID | int(4)       | NO   | PRI | NULL    | auto_increment |
| name         | varchar(150) | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
2 rows in set (0.001 sec)

Projects_rel_Project_Technology关联表 #

MariaDB [portfolioDB]> describe Projects_rel_Project_Technology;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| Project    | int(11) | NO   | PRI | NULL    |       |
| Technology | int(11) | NO   | PRI | NULL    |       |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.001 sec)

关联表创建语句：

CREATE TABLE Projects_rel_Project_Technology (
    Project INT NOT NULL, 
    Technology INT NOT NULL, 
    PRIMARY KEY (Project, Technology), 
    CONSTRAINT Constr_Projects_rel_Project_Technology_Project_fk FOREIGN KEY Project_fk (Project) REFERENCES Projects_Project(projectId) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT Constr_Projects_rel_Project_Technology_Technology_fk FOREIGN KEY Technology_fk (Technology) REFERENCES Projects_Technology(technologyID) ON DELETE CASCADE ON UPDATE CASCADE
);

单项目技术查询语句：

SELECT Projects_Technology.* FROM Projects_Technology JOIN Projects_rel_Project_Technology ON Projects_Technology.technologyID = Projects_rel_Project_Technology.Technology WHERE Projects_rel_Project_Technology.Project = 1;

现有Node.js接口代码 #

app.get("/api/showProjects", function (request, response){
    mysqlConnectionStart();

    var myquery = `select * from Projects_Project`;

    connection.query(myquery, function(err, results, fields) {
        if (err) {
            console.log('----> Error with MySQL query in /showProjects: ' + err.message);
        }
        else{
            console.log('Query successful, results for "' + myquery + '" are being displayed.');
            
            var mylist = [];

            Object.keys(results).forEach(function(key) {
                var row = results[key];
                mylist.push({ 
                    'projectId' : row.projectId,
                    'name' : row.name,
                    'description' : row.description,
                    'view' : row.view,
                    'code' : row.code,
                    'date' : row.date
                });
            });

            response.send({"My projects" : mylist});
        }
    });


    mysqlConnectionEnd();
});

需求说明 #

当前接口仅返回项目基础数据，需要将每个项目关联的技术列表嵌入到对应项目对象中，最终返回结构示例：

mylist.push({ 
    'projectId' : row.projectId,
    'name' : row.name,
    'description' : row.description,
    'view' : row.view,
    'code' : row.code,
    'date' : row.date,
    'technologies' : [{'name': '技术1'}, {'name': '技术2'}]
});

修改方案 #

方案一：SQL层面聚合查询（性能更优） #

通过LEFT JOIN关联三张表，使用GROUP_CONCAT将同一项目的技术名称合并为字符串，再在Node.js中拆分为数组。

修改后的SQL查询语句

SELECT 
    p.projectId,
    p.name,
    p.description,
    p.view,
    p.code,
    p.date,
    GROUP_CONCAT(t.name SEPARATOR ',') AS tech_names
FROM Projects_Project p
LEFT JOIN Projects_rel_Project_Technology rel ON p.projectId = rel.Project
LEFT JOIN Projects_Technology t ON rel.Technology = t.technologyID
GROUP BY p.projectId;

修改后的Node.js代码

app.get("/api/showProjects", function (request, response){
    mysqlConnectionStart();

    var myquery = `
        SELECT 
            p.projectId,
            p.name,
            p.description,
            p.view,
            p.code,
            p.date,
            GROUP_CONCAT(t.name SEPARATOR ',') AS tech_names
        FROM Projects_Project p
        LEFT JOIN Projects_rel_Project_Technology rel ON p.projectId = rel.Project
        LEFT JOIN Projects_Technology t ON rel.Technology = t.technologyID
        GROUP BY p.projectId;
    `;

    connection.query(myquery, function(err, results, fields) {
        if (err) {
            console.log('----> Error with MySQL query in /showProjects: ' + err.message);
            response.status(500).send({error: err.message});
        }
        else{
            console.log('Query successful, results for "' + myquery + '" are being displayed.');
            
            var mylist = [];

            Object.keys(results).forEach(function(key) {
                var row = results[key];
                // 处理技术列表：拆分字符串并转换为对象数组
                let technologies = [];
                if (row.tech_names) {
                    technologies = row.tech_names.split(',').map(name => ({name: name.trim()}));
                }

                mylist.push({ 
                    'projectId' : row.projectId,
                    'name' : row.name,
                    'description' : row.description,
                    'view' : row.view,
                    'code' : row.code,
                    'date' : row.date,
                    'technologies' : technologies
                });
            });

            response.send({"My projects" : mylist});
        }
    });

    mysqlConnectionEnd();
});

方案二：Node.js嵌套异步查询（更灵活） #

先查询所有项目，再对每个项目单独查询关联技术，适合需要获取技术更多字段的场景。使用async/await处理异步逻辑，避免回调地狱。

修改后的Node.js代码

app.get("/api/showProjects", async function (request, response) {
    try {
        mysqlConnectionStart();

        // 查询所有项目
        const projectsQuery = `select * from Projects_Project`;
        const projectsResults = await new Promise((resolve, reject) => {
            connection.query(projectsQuery, (err, results) => {
                if (err) reject(err);
                else resolve(results);
            });
        });

        // 遍历项目，查询每个项目的技术
        const mylist = await Promise.all(projectsResults.map(async (project) => {
            const techQuery = `
                SELECT t.name 
                FROM Projects_Technology t 
                JOIN Projects_rel_Project_Technology rel 
                ON t.technologyID = rel.Technology 
                WHERE rel.Project = ?
            `;
            const techResults = await new Promise((resolve, reject) => {
                connection.query(techQuery, [project.projectId], (err, results) => {
                    if (err) reject(err);
                    else resolve(results);
                });
            });

            // 转换技术数据格式
            const technologies = techResults.map(tech => ({name: tech.name}));

            return {
                projectId: project.projectId,
                name: project.name,
                description: project.description,
                view: project.view,
                code: project.code,
                date: project.date,
                technologies: technologies
            };
        }));

        response.send({"My projects": mylist});
    } catch (err) {
        console.log('----> Error in /showProjects: ' + err.message);
        response.status(500).send({error: err.message});
    } finally {
        mysqlConnectionEnd();
    }
});

方案对比 #

• 方案一：仅需1次数据库请求，性能更高；但仅适合获取技术的单一字段（如名称），若需要技术ID等更多数据，需调整GROUP_CONCAT逻辑。
• 方案二：每个项目对应1次技术查询，请求次数随项目数量增加；但可以灵活获取技术的所有字段，扩展性更强。

内容的提问来源于stack exchange，提问作者LautaroColella