Node.js+Express操作MariaDB时单id参数如何匹配SQL两处?占位符

Node.js 生态下 MariaDB/MySQL 驱动默认按顺序匹配?占位符：SQL中占位符的数量必须和传入参数数组的元素数量完全一致，按位置一一对应赋值。你当前的UNION查询写了2个?占位符，但参数数组仅传入1个值，第二个占位符无法获取参数，就会导致查询逻辑错误。

方案1：参数数组重复传入id（通用无兼容问题） #

这是最稳妥的实现方式，不依赖驱动特性，只需要在参数数组里把同一个id值传两次，按位置匹配两个占位符即可。建议对路由参数做类型转换，避免字符串和数值类型隐式转换导致的索引失效：

routes.get('/ajustarOTCP/:id', (req, res)=>{
  req.getConnection((err, connection)=>{
    if(err) return res.send(err);
    // 数据库id为数值类型时，先把路由参数转成数值
    const targetUserId = Number(req.params.id);
    connection.query(`
    SELECT * FROM developmentrequest
    INNER JOIN potentialclients ON potentialclients.id_potentialclients = developmentrequest.id_potentialclients
    INNER JOIN users ON potentialclients.id_user = users.id
    INNER JOIN products ON products.id_productdevelopment = developmentrequest.id_developmentrequest
    WHERE users.id = ?
    UNION
    SELECT * FROM developmentrequest
    INNER JOIN clients ON clients.id_clients = developmentrequest.id_clients
    INNER JOIN users ON clients.id_user = users.id
    INNER JOIN products ON products.id_productdevelopment = developmentrequest.id_developmentrequest
    WHERE users.id = ?`, [targetUserId, targetUserId], (err, rows)=>{
        if(err) return res.send(err);
        res.json(rows);
    });
  })
})

方案2：使用命名占位符（适用于mysql2驱动） #

如果你的项目使用目前主流的mysql2驱动，可以用命名占位符替代?，同一个命名参数可以在SQL中多次引用，只需要传一次值，后续如果SQL里还要用到该id，也不需要额外补参数：

routes.get('/ajustarOTCP/:id', (req, res)=>{
  req.getConnection((err, connection)=>{
    if(err) return res.send(err);
    const targetUserId = Number(req.params.id);
    connection.query(`
    SELECT * FROM developmentrequest
    INNER JOIN potentialclients ON potentialclients.id_potentialclients = developmentrequest.id_potentialclients
    INNER JOIN users ON potentialclients.id_user = users.id
    INNER JOIN products ON products.id_productdevelopment = developmentrequest.id_developmentrequest
    WHERE users.id = :targetUserId
    UNION
    SELECT * FROM developmentrequest
    INNER JOIN clients ON clients.id_clients = developmentrequest.id_clients
    INNER JOIN users ON clients.id_user = users.id
    INNER JOIN products ON products.id_productdevelopment = developmentrequest.id_developmentrequest
    WHERE users.id = :targetUserId`, {targetUserId}, (err, rows)=>{
        if(err) return res.send(err);
        res.json(rows);
    });
  })
})

提示：如果使用老版本mysql驱动，不支持命名占位符语法，直接用方案1即可。

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