column type support #1546
Description
import mysql from "mysql";
const dbConfig = {
connectionLimit: 10,
host: "db",
user: "db-read",
password: "Secr3t",
database: "test",
};
const pool = mysql.createPool(dbConfig);
const cbk = (err, rows, fields) => {
if (err) console.log(err);
if (rows.length) {
var data = {};
data.meta = fields.map((f)=>{
return {"text": f.name,"type": mysql.Types[f.type]};
});
data.rows = rows.map((row)=>{
return data.meta.map((col)=>{
return row[col.text];
});
});
console.log( data);
}
else console.log("No data");
process.exit(0);
};
const sql = \`select 'ABCDEF', 123, 123.45, cast('2022-01/01 11:59' as datetime) x, cast('{"name": "Steve"}' as json) y\`;
console.log('running query...');
pool.query(sql, cbk);When I run the program above, I get the following output:
{
meta: [
{ text: 'ABCDEF', type: 'VAR_STRING' },
{ text: '123', type: 'LONGLONG' },
{ text: '123.45', type: 'NEWDECIMAL' },
{ text: 'x', type: 'DATETIME' },
{ text: 'y', type: 'JSON' }
],
rows: [
[
'ABCDEF',
123,
123.45,
2022-01-01T16:59:00.000Z,
'{"name": "Steve"}'
]
]
}If I change the import to "mysql2" and run the program, I get this output:
{
meta: [
{ text: 'ABCDEF', type: undefined },
{ text: '123', type: undefined },
{ text: '123.45', type: undefined },
{ text: 'x', type: undefined },
{ text: 'y', type: undefined }
],
rows: [ [ 'ABCDEF', 123, '123.45', 2022-01-01T16:59:00.000Z, [Object] ] ]
}The mysql.Types is not supported as it was in mysql, and the field.type also seems to be missing, or replaced by a columnType attribute. For backward compatibility, could you add a field.type alias and implement a Types dictionary similar to this:
const Types = {
0x00: 'DECIMAL', // aka DECIMAL
0x01: 'TINY', // aka TINYINT, 1 byte
0x02: 'SHORT', // aka SMALLINT, 2 bytes
0x03: 'LONG', // aka INT, 4 bytes
0x04: 'FLOAT', // aka FLOAT, 4-8 bytes
0x05: 'DOUBLE', // aka DOUBLE, 8 bytes
0x06: 'NULL', // NULL (used for prepared statements, I think)
0x07: 'TIMESTAMP', // aka TIMESTAMP
0x08: 'LONGLONG', // aka BIGINT, 8 bytes
0x09: 'INT24', // aka MEDIUMINT, 3 bytes
0x0a: 'DATE', // aka DATE
0x0b: 'TIME', // aka TIME
0x0c: 'DATETIME', // aka DATETIME
0x0d: 'YEAR', // aka YEAR, 1 byte (don't ask)
0x0e: 'NEWDATE', // aka ?
0x0f: 'VARCHAR', // aka VARCHAR (?)
0x10: 'BIT', // aka BIT, 1-8 byte
0xf5: 'JSON',
0xf6: 'NEWDECIMAL', // aka DECIMAL
0xf7: 'ENUM', // aka ENUM
0xf8: 'SET', // aka SET
0xf9: 'TINY_BLOB', // aka TINYBLOB, TINYTEXT
0xfa: 'MEDIUM_BLOB', // aka MEDIUMBLOB, MEDIUMTEXT
0xfb: 'LONG_BLOB', // aka LONGBLOG, LONGTEXT
0xfc: 'BLOB', // aka BLOB, TEXT
0xfd: 'VAR_STRING', // aka VARCHAR, VARBINARY
0xfe: 'STRING', // aka CHAR, BINARY
0xff: 'GEOMETRY' // aka GEOMETRY
};