mysql - sql查询不适用于node.js mysql，但适用于终端

Question

我有一个sql查询如下

select profileName from user where (id) in ( select id FROM( SELECT user_id1 as id from user_connection where user_id2=(select id from user where profileName='deva') UNION ALL SELECT user_id2 as id from user_connection where user_id1=(select id from user where profileName='deva')) t GROUP BY id) ;

在终端上就像魔术一样，但在node.js上它有sql查询错误

exports.findFriends = function(userName,callback){
console.log('searching friends.... '+userName);
var findFriendsQuery = 'select profileName from user where (id) in ( select id FROM ( SELECT user_id1 as id from user_connection where user_id2=(select id from user where profileName= ? ) UNION ALL SELECT user_id2 as id from user_connection where user_id1=(select id from user where profileName=?)) t GROUP BY id)' ;
  db.query(findFriendsQuery,[userName],function(err,rows,fields){
    if(err){
     console.log(err);
     callback(1,-1,-1);
     }
    else{
        callback(0,rows,1);
        }
    });
};

sqlMessage: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'?)) t GROUP BY id)\' at line 1', sqlState: '42000', index: 0,

Answer 1

您已经使用了两次占位符( ? )，而只传递了一次，您需要传递两次( [userName, userName] )

exports.findFriends = function(userName,callback){
console.log('searching friends.... '+userName);
var findFriendsQuery = 'select profileName from user where (id) in ( select id FROM ( SELECT user_id1 as id from user_connection where user_id2=(select id from user where profileName= ? ) UNION ALL SELECT user_id2 as id from user_connection where user_id1=(select id from user where profileName=?)) t GROUP BY id)' ;
  db.query(findFriendsQuery,[userName, userName],function(err,rows,fields){
    if(err){
     console.log(err);
     callback(1,-1,-1);
     }
    else{
        callback(0,rows,1);
        }
    });
};