运行在nodejs上的mysql联接查询获得空结果，尽管给定的查询没有问题

Question

我现在正试图通过在服务器(Nodejs)上执行一个“JOIN”查询来获取一些数据，但是它总是给出一个空值结果。

以下是查询：

    const selectNameSql=`SELECT 
    data_db.assets.name_assets, data_db.assets.id_assets, 
    data_db.threats.name_threats, data_db.threats.id_threats, 
    data_db.vulns.name_vulns, data_db.vulns.id_vulns 
    FROM 
    data_db.threats 
    RIGHT JOIN data_db.assets 
    ON data_db.assets.id_assets=data_db.threats.id_assets 
    RIGHT JOIN data_db.vulns 
    ON data_db.vulns.id_assets=data_db.assets.id_assets 
    WHERE 
    data_db.assets.id_assets=? AND data_db.vulns.id_vulns=? AND data_db.threats.id_threats=?`

下面是执行查询的代码：

    exports.test=async function(req,res,next){
    const selectNameSql=`SELECT 
    data_db.assets.name_assets, data_db.assets.id_assets, 
    data_db.threats.name_threats, data_db.threats.id_threats, 
    data_db.vulns.name_vulns, data_db.vulns.id_vulns 
    FROM 
    data_db.threats 
    RIGHT JOIN data_db.assets 
    ON data_db.assets.id_assets=data_db.threats.id_assets 
    RIGHT JOIN data_db.vulns 
    ON data_db.vulns.id_assets=data_db.assets.id_assets 
    WHERE 
    data_db.assets.id_assets=? AND data_db.vulns.id_vulns=? AND data_db.threats.id_threats=?`;
    var a = await db.query(selectNameSql, [2, 3, 4], function(err, rows, fields){
        if(err){ console.log(err);}
        else{console.log('SO SAD');}
    });
    console.log(a);}

尽管直接在数据库上执行查询很好，但在nodejs服务器上执行上面的代码会显示如下结果：

<ref *1> Query {
  _events: [Object: null prototype] {
    error: [Function (anonymous)],
    packet: [Function (anonymous)],
    timeout: [Function (anonymous)],
    end: [Function (anonymous)]
  },
  _eventsCount: 4,
  _maxListeners: undefined,
  _callback: [Function (anonymous)],
  _callSite: Error
      at Protocol._enqueue (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/mysql/lib/protocol/Protocol.js:144:48)
      at Connection.query (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/mysql/lib/Connection.js:198:25)
      at exports.test (/home/tv/Desktop/CapTeen_repo/CapTeen/routes/analysis/analysis.controller.js:334:22)
      at Layer.handle [as handle_request] (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/layer.js:95:5)
      at next (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/route.js:144:13)
      at Route.dispatch (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/route.js:114:3)
      at Layer.handle [as handle_request] (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/layer.js:95:5)
      at /home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/index.js:284:15
      at Function.process_params (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/index.js:346:12)
      at next (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/index.js:280:10),
  _ended: false,
  _timeout: undefined,
  _timer: Timer { _object: [Circular *1], _timeout: null },
  sql: 'SELECT \n' +
    '    data_db.assets.name_assets, data_db.assets.id_assets, \n' +
    '    data_db.threats.name_threats, data_db.threats.id_threats, \n' +
    '    data_db.vulns.name_vulns, data_db.vulns.id_vulns \n' +
    '    FROM \n' +
    '    data_db.threats \n' +
    '    RIGHT JOIN data_db.assets \n' +
    '    ON data_db.assets.id_assets=data_db.threats.id_assets \n' +
    '    RIGHT JOIN data_db.vulns \n' +
    '    ON data_db.vulns.id_assets=data_db.assets.id_assets \n' +
    '    WHERE \n' +
    '    data_db.assets.id_assets=2 AND data_db.vulns.id_vulns=3 AND data_db.threats.id_threats=4',
  values: [ 2, 3, 4 ],
  typeCast: true,
  nestTables: false,
  _resultSet: null,
  _results: [],
  _fields: [],
  _index: 0,
  _loadError: null,
  _connection: <ref *2> Connection {
    _events: [Object: null prototype] {},
    _eventsCount: 0,
    _maxListeners: undefined,
    config: ConnectionConfig {
      host: '-----(I erased it)-----',
      port: 3306,
      localAddress: undefined,
      socketPath: undefined,
      user: '----(I erased it)----',
      password: '-----(I erased it)----',
      database: 'data_db',
      connectTimeout: 10000,
      insecureAuth: false,
      supportBigNumbers: false,
      bigNumberStrings: false,
      dateStrings: false,
      debug: undefined,
      trace: true,
      stringifyObjects: false,
      timezone: 'local',
      flags: '',
      queryFormat: undefined,
      pool: undefined,
      ssl: false,
      localInfile: true,
      multipleStatements: false,
      typeCast: true,
      maxPacketSize: 0,
      charsetNumber: 33,
      clientFlags: 455631,
      protocol41: true
    },
    _socket: Socket {
      connecting: false,
      _hadError: false,
      _parent: null,
      _host: null,
      _readableState: [ReadableState],
      _events: [Object: null prototype],
      _eventsCount: 4,
      _maxListeners: undefined,
      _writableState: [WritableState],
      allowHalfOpen: false,
      _sockname: null,
      _pendingData: null,
      _pendingEncoding: '',
      server: null,
      _server: null,
      timeout: 0,
      [Symbol(async_id_symbol)]: 8,
      [Symbol(kHandle)]: [TCP],
      [Symbol(lastWriteQueueSize)]: 0,
      [Symbol(timeout)]: Timeout {
        _idleTimeout: -1,
        _idlePrev: null,
        _idleNext: null,
        _idleStart: 150,
        _onTimeout: null,
        _timerArgs: undefined,
        _repeat: null,
        _destroyed: true,
        [Symbol(refed)]: false,
        [Symbol(kHasPrimitive)]: false,
        [Symbol(asyncId)]: 11,
        [Symbol(triggerId)]: 1
      },
      [Symbol(kBuffer)]: null,
      [Symbol(kBufferCb)]: null,
      [Symbol(kBufferGen)]: null,
      [Symbol(kCapture)]: false,
      [Symbol(kSetNoDelay)]: false,
      [Symbol(kSetKeepAlive)]: false,
      [Symbol(kSetKeepAliveInitialDelay)]: 0,
      [Symbol(kBytesRead)]: 0,
      [Symbol(kBytesWritten)]: 0
    },
    _protocol: Protocol {
      _events: [Object: null prototype],
      _eventsCount: 7,
      _maxListeners: undefined,
      readable: true,
      writable: true,
      _config: [ConnectionConfig],
      _connection: [Circular *2],
      _callback: null,
      _fatalError: null,
      _quitSequence: null,
      _handshake: true,
      _handshaked: true,
      _ended: false,
      _destroyed: false,
      _queue: [Array],
      _handshakeInitializationPacket: [HandshakeInitializationPacket],
      _parser: [Parser],
      [Symbol(kCapture)]: false
    },
    _connectCalled: true,
    state: 'authenticated',
    threadId: 57,
    [Symbol(kCapture)]: false
  },
  [Symbol(kCapture)]: false
}
SO SAD

我不知道如何解决这个问题，所以如果有人知道，请告诉我如何排除它。

Answer 1

mysql包不支持async await，您可以尝试像这样修改代码并使用回调：

exports.test = function (req, res, next) {
  const selectNameSql = `SELECT 
    data_db.assets.name_assets, data_db.assets.id_assets, 
    data_db.threats.name_threats, data_db.threats.id_threats, 
    data_db.vulns.name_vulns, data_db.vulns.id_vulns 
    FROM 
    data_db.threats 
    RIGHT JOIN data_db.assets 
    ON data_db.assets.id_assets=data_db.threats.id_assets 
    RIGHT JOIN data_db.vulns 
    ON data_db.vulns.id_assets=data_db.assets.id_assets 
    WHERE 
    data_db.assets.id_assets=? AND data_db.vulns.id_vulns=? AND data_db.threats.id_threats=?`;

  db.query(
    selectNameSql,
    [2, 3, 4],
    function (err, rows, fields) {
      if (err) {
        console.log(err);
      } else {
        console.log(rows);
      }
    }
  );
};

或者将mysql2包与async await一起使用

exports.test = async function (req, res, next) {
    const selectNameSql = `SELECT 
        data_db.assets.name_assets, data_db.assets.id_assets, 
        data_db.threats.name_threats, data_db.threats.id_threats, 
        data_db.vulns.name_vulns, data_db.vulns.id_vulns 
        FROM 
        data_db.threats 
        RIGHT JOIN data_db.assets 
        ON data_db.assets.id_assets=data_db.threats.id_assets 
        RIGHT JOIN data_db.vulns 
        ON data_db.vulns.id_assets=data_db.assets.id_assets 
        WHERE 
        data_db.assets.id_assets=? AND data_db.vulns.id_vulns=? AND data_db.threats.id_threats=?`;
  
    try {
      const mysql = require('mysql2/promise');
      const connection = await mysql.createConnection({
        host: '<your-host>',
        user: '<your-user>',
        database: '<your-database>',
      });
  
      const a = await connection.execute(selectNameSql, [2, 3, 4]);
      console.log(a);
    } catch (err) {
      console.log(err);
    }
  };

有关mysql2的更多信息，请访问他们的官方文档

Answer 2

这个问题可能是由于把回调和承诺混合在一起造成的(“所以SAD”消息是从回调内部记录的，但是您没有对结果做任何处理)。

要么坚持基于承诺的解决方案，要么坚持基于回调的解决方案，但两者都不能(尽管我在mysql包文档中找不到任何东西表明它实际上支持承诺？)