MySQL Returns Wrong Value Only When Number Is Larger

Question

I am writing a bot that has stored values. One of which is a number of 18 places. I am able to store the values properly into my database, MySQL, as a BIGINT[20]. The values being stored are correct.

The problem comes when trying to fetch that number. The value stored inside of the database is 502033097630416897, however, the actual value returned by mysql is this 502033097630416900. For comparison, here they are again:

502033097630416900
502033097630416897

It seems as if the return value is either being rounded up or changed. I wanted to test this theory, so I changed the value stored in MySQL to a smaller number, 123, 1234567, and so on. The values being returned are actually correct. For some reason, the values returned are incorrect if the numbers stored in MySQL are too large. I would post my code for storing, but I don't think that is relevant since the actual value being stored is correct.

As for calling, I have a function that can get multiple return new

Promise((success, fail) => {
    con.query(`SELECT * FROM serversettings WHERE serverId = ${serverID}`, (err, rows) => {
        switch(type){
            case "onjoin":
                success(rows[0].onjoin);

Where type is the data I want to pull. Any insight would be helpful, thank you!

Answer 1

Javascript does arithmetic using 64-bit IEEE 754 floating point numbers. In that kind of arithmetic, the two numbers in your example are equal to each other; IEEE 754 only offers about 16 decimal digits of precision.

To handle these numbers in Javascript, you must either work with them as if they were text strings or use a package for long numbers.

If you change your query to

SELECT CAST(bigint_column AS CHAR) bigint_column, other, else, etc
  FROM serversettings 
  ...

You'll get the character strings.