MySQL JavaScript query replaces last digits of a number with 0

Question

I have this code (shown below) that connects to the database, and then runs a SQL query that selects all the values (bigint snowflake IDs) in the "id" column that are also IN the array written inside the query. Once that's done, it logs the result to the console.

For some reason though, it seems to round off the numbers, or at least replace some of the last digits with 0. I'm trying to find out why and how to fix this, so help would be greatly appreciated!

const mysql = require('mysql');
const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root",
  database: "botproject"
});

con.connect(function(err) {
    if (err) throw err;

    con.query("SELECT id FROM xplevel WHERE id IN (718141588043202731, 294126883694444544, 685325127373553689, 307409641262940160, 478536470081175562);", function (err, result, fields) {
      if (err) throw err;
      
    console.log(result);
 
    });

});

The expected output would be this (the exact same values that were put in the query's array):

[
  RowDataPacket { id: 294126883694444544 },
  RowDataPacket { id: 307409641262940160 },
  RowDataPacket { id: 478536470081175562 },
  RowDataPacket { id: 685325127373553689 },
  RowDataPacket { id: 718141588043202731 }
]

But the actual output that I'm getting is this:

[
  RowDataPacket { id: 294126883694444540 },
  RowDataPacket { id: 307409641262940160 },
  RowDataPacket { id: 478536470081175550 },
  RowDataPacket { id: 685325127373553700 },
  RowDataPacket { id: 718141588043202700 }
]

EDIT: Note that it doesn't do this when running the query directly in the mysql terminal as such:

mysql> SELECT id FROM xplevel WHERE id IN (718141588043202731, 294126883694444544, 685325127373553689, 307409641262940160, 478536470081175562);
+--------------------+
| id                 |
+--------------------+
| 294126883694444544 |
| 307409641262940160 |
| 478536470081175562 |
| 685325127373553689 |
| 718141588043202731 |
+--------------------+
5 rows in set (0.00 sec)

Try casting the number to BigInt: `SELECT id FROM CAST(xplevel AS UNSIGNED) WHERE...` — ZephDavies, Nov 09 '20 at 10:16
@ZephDavies Using `SELECT CAST(id AS CHAR) AS id FROM . . .` works! — King TMK, Nov 09 '20 at 10:41

score 1 · Accepted Answer · answered Nov 09 '20 at 10:22

1

This is a limitation of JavaScript IEEE754 numbers.

You can do simple test in browser console:

let a = 685325127373553689
a
685325127373553700 //prints rounded value

To deal with such data in JS you need to disable automatic conversion into numbers and make it return you the data as strings. Or convert it into new BigInt type, or use some 3rd-party libraries for large-numbers processing (Extremely large numbers in javascript).

answered Nov 09 '20 at 10:22

Stalinko

3,319
28
31

Thank you! Converting it to a string by using `SELECT CAST(id AS CHAR) AS id FROM . . .` works like a charm. – King TMK Nov 09 '20 at 10:40
That's quite dirty to make MySQL cast datatypes to fool JS :) I'd better look for options how to solve it on JS-side. But anyway if it works then why not. – Stalinko Nov 09 '20 at 10:53
1

I realize this question is almost two years old, but I'd like to clarify that I did look for better options, and I found out that adding `supportBigNumbers: true` in the object passed in the mysql.createConnection method will convert those big numbers into strings, fixing the inaccuracy issue. – King TMK Sep 06 '22 at 15:35
@KingTMK that sounds like a cool alternative solution. Why don't you submit it as an answer? – Stalinko Sep 07 '22 at 05:24

MySQL JavaScript query replaces last digits of a number with 0

1 Answers1