CODEWITHSUNDEEP
javascriptmysqlnode.js
GuiDrn
GuiDrn

Reputation: 333

how to list column in mysql on nodejs with module mysql

I want to list columns in a table using module mysql on nodejs

When I run the query : 

SHOW COLUMNS FROM tableName WHERE FIELD = columnName

It's work fine, I can know if the column exist or not.

But I want to list the columns and I get a list of object and I don't what to do with that and if I get the good result.

I tried :

SHOW COLUMNS FROM tableName
DESCRIBE tableName

with both queries I get a list of object


{ catalog: 'def',
  db: 'information_schema',
  table: 'COLUMNS',
  orgTable: 'COLUMNS',
  name: 'Field',
  orgName: 'COLUMN_NAME',
  filler1: ,
  charsetNr: 33,
  length: 192,
  type: 253,
  flags: 1,
  decimals: 0,
  filler2: ,
  default: undefined,
  zeroFill: false,
  protocol41: true }
{ catalog: 'def',
  db: 'information_schema',
  table: 'COLUMNS',
  orgTable: 'COLUMNS',
  name: 'Type',
  orgName: 'COLUMN_TYPE',
  filler1: ,
  charsetNr: 33,
  length: 589815,
  type: 252,
  flags: 17,
  decimals: 0,
  filler2: ,
  default: undefined,
  zeroFill: false,
  protocol41: true }
{ catalog: 'def',
  db: 'information_schema',
  table: 'COLUMNS',
  orgTable: 'COLUMNS',
  name: 'Null',
  orgName: 'IS_NULLABLE',
  filler1: ,
  charsetNr: 33,
  length: 9,
  type: 253,
  flags: 1,
  decimals: 0,
  filler2: ,
  default: undefined,
  zeroFill: false,
  protocol41: true }
{ catalog: 'def',
  db: 'information_schema',
  table: 'COLUMNS',
  orgTable: 'COLUMNS',
  name: 'Key',
  orgName: 'COLUMN_KEY',
  filler1: ,
  charsetNr: 33,
  length: 9,
  type: 253,
  flags: 1,
  decimals: 0,
  filler2: ,
  default: undefined,
  zeroFill: false,
  protocol41: true }
{ catalog: 'def',
  db: 'information_schema',
  table: 'COLUMNS',
  orgTable: 'COLUMNS',
  name: 'Default',
  orgName: 'COLUMN_DEFAULT',
  filler1: ,
  charsetNr: 33,
  length: 589815,
  type: 252,
  flags: 16,
  decimals: 0,
  filler2: ,
  default: undefined,
  zeroFill: false,
  protocol41: true }
{ catalog: 'def',
  db: 'information_schema',
  table: 'COLUMNS',
  orgTable: 'COLUMNS',
  name: 'Extra',
  orgName: 'EXTRA',
  filler1: ,
  charsetNr: 33,
  length: 90,
  type: 253,
  flags: 1,
  decimals: 0,
  filler2: ,
  default: undefined,
  zeroFill: false,
  protocol41: true }

The function I use is the following : 

var mysql = require('mysql');
var connection = mysql.createConnection({
    host     : "host",
    user     : "user",
    password : "pass",
    database : "db"
});
connection.query(myQuery, function(err, rows, fields){ 
    if(err) console.log(err);
    if(fields) console.log(fields);
    if(rows) console.log(rows);
});

If someone have a solution for me I tried also to look in information_schema and get the same result. Thanks you in advance.

At the same time if someone can tell how to use show table I get a similar result.

Upvotes: 2

Views: 15273

Answers (1)

Andrey Sidorov
Andrey Sidorov

Reputation: 25466

You are printing 'fields' first, which is description of fields in the SHOW COLUMNS response. Response rows itself looks like this:

 [
  {
    Field: 'id',
    Type: 'int(11) unsigned',
    Null: 'NO',
    Key: 'PRI',
    Default: null,
    Extra: 'auto_increment'
  }
  /* , ... */
 ]

So first column name, for example is:

connection.query('SHOW COLUMNS FROM test', function(err, rows, fields){ 
    console.log(rows[0].Field);
});

Upvotes: 7

Related Questions