mardi 5 janvier 2021

how to select a random id from table in periods in MySQL?

I want to select a random id from table nodes from last 15 seconds.

I have tried this but it gives a long output: the code:

    const mysql = require('mysql');
    
    const connection = mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'DAGtest3'
    });
    
    connection.connect((err) => {
      if (err) throw err;
      console.log('Database Connected!');
    });
    
    var b = connection.query("SELECT t.`id` FROM `nodes` AS t 
           INNER JOIN (SELECT ROUND( RAND() * (SELECT MAX(id) FROM `nodes` )) AS id ) AS x 
            WHERE t.id >= x.id AND created_at < DATE_SUB(NOW(), INTERVAL 15 SECOND) LIMIT 1");
    console.log(b);

output

Query {
  _events: [Object: null prototype] {
    error: [Function],
    packet: [Function],
    timeout: [Function],
    end: [Function]
  },
  _eventsCount: 4,
  _maxListeners: undefined,
  _callback: undefined,
  _callSite: Error
      at Protocol._enqueue (/home/istabraq/DAGsim/node_modules/mysql/lib/protocol/Protocol.js:144:48)
      at Connection.query (/home/istabraq/DAGsim/node_modules/mysql/lib/Connection.js:198:25)
      at Object.<anonymous> (/home/istabraq/DAGsim/test30.js:15:20)
      at Module._compile (internal/modules/cjs/loader.js:999:30)
      at Object.Module._extensions..js (internal/modules/cjs/loader.js:1027:10)
      at Module.load (internal/modules/cjs/loader.js:863:32)
      at Function.Module._load (internal/modules/cjs/loader.js:708:14)
      at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:60:12)
      at internal/main/run_main_module.js:17:47,
  _ended: false,
  _timeout: undefined,
  _timer: Timer { _object: [Circular], _timeout: null },
  sql: 'SELECT t.`id` FROM `nodes` AS t INNER JOIN (SELECT ROUND( RAND() * (SELECT MAX(id) FROM `nodes` )) AS id ) AS x WHERE t.id >= x.id AND created_at < DATE_SUB(NOW(), INTERVAL 15 SECOND) LIMIT 1',
  values: undefined,
  typeCast: true,
  nestTables: false,
  _resultSet: null,
  _results: [],
  _fields: [],
  _index: 0,
  _loadError: null,
  _connection: Connection {
    _events: [Object: null prototype] {},
    _eventsCount: 0,
    _maxListeners: undefined,
    config: ConnectionConfig {
      host: 'localhost',
      port: 3306,
      localAddress: undefined,
      socketPath: undefined,
      user: 'root',
      password: 'password',
      database: 'DAGtest3',
      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
    },
    _socket: Socket {
      connecting: true,
      _hadError: false,
      _parent: null,
      _host: 'localhost',
      _readableState: [ReadableState],
      readable: false,
      _events: [Object: null prototype],
      _eventsCount: 5,
      _maxListeners: undefined,
      _writableState: [WritableState],
      writable: true,
      allowHalfOpen: false,
      _sockname: null,
      _pendingData: null,
      _pendingEncoding: '',
      server: null,
      _server: null,
      timeout: 10000,
      [Symbol(asyncId)]: 2,
      [Symbol(kHandle)]: [TCP],
      [Symbol(kSetNoDelay)]: false,
      [Symbol(lastWriteQueueSize)]: 0,
      [Symbol(timeout)]: Timeout {
        _idleTimeout: 10000,
        _idlePrev: [TimersList],
        _idleNext: [TimersList],
        _idleStart: 99,
        _onTimeout: [Function: bound ],
        _timerArgs: undefined,
        _repeat: null,
        _destroyed: false,
        [Symbol(refed)]: false,
        [Symbol(kHasPrimitive)]: false,
        [Symbol(asyncId)]: 6,
        [Symbol(triggerId)]: 1
      },
      [Symbol(kBuffer)]: null,
      [Symbol(kBufferCb)]: null,
      [Symbol(kBufferGen)]: null,
      [Symbol(kCapture)]: false,
      [Symbol(kBytesRead)]: 0,
      [Symbol(kBytesWritten)]: 0
    },
    _protocol: Protocol {
      _events: [Object: null prototype],
      _eventsCount: 7,
      _maxListeners: undefined,
      readable: true,
      writable: true,
      _config: [ConnectionConfig],
      _connection: [Circular],
      _callback: null,
      _fatalError: null,
      _quitSequence: null,
      _handshake: true,
      _handshaked: false,
      _ended: false,
      _destroyed: false,
      _queue: [Array],
      _handshakeInitializationPacket: null,
      _parser: [Parser],
      [Symbol(kCapture)]: false
    },
    _connectCalled: true,
    state: 'disconnected',
    threadId: null,
    [Symbol(kCapture)]: false
  },
  [Symbol(kCapture)]: false
}

How to solve it, please?




Aucun commentaire:

Enregistrer un commentaire