1

I've just upgraded from MySQL 5.7 to MariaDB 10.3 and one functionality difference has me stumped. I have an index field that is of the following type:

UserID | int(9) unsigned zerofill

If I execute the following query:

SELECT UserID FROM User WHERE UserID='000000003';

I get:

+-----------+
| UserID    |
+-----------+
| 000000003 |
+-----------+

If I use the MariaDB callback connector from Node.js I get the following:

[
        {
            "UserID": 3
        }
]

How do I prevent the MariaDB Connector/Node.js Callback API from stripping off the leading zeros from an index? Right now I understand that I can pad every instance of a index reference within my API after all my queries, but this just seems... inefficient.

Is there a better way? Thanks.

src
  • 117
  • 1
  • 4

1 Answers1

4

node.js driver automatically evaluate value type. Since that's an int, 3 is the expected value.

If you explicitly want a string you can tell by sql, like query select cast (UserID AS CHAR) as UserID from User WHERE UserID='000000003';

This will return

[
        {
            "UserID": '000000003'
        }
]
Diego Dupin
  • 1,106
  • 8
  • 9
  • Nit: that’s not a valid JSON response. – user2864740 Apr 26 '21 at 08:59
  • 1
    Thank you very much for this -- this does work and it is more elegant to update the queries than it is to modify the results. That said, while I appreciate that the field is an INT in the DB, if it is a zero padded INT the driver should return a zero padded result. If that means returning it as a string, then fine. This is a change in functionality between this and the older MySQL driver. In any case, hope this helps someone else @user2864740, my JSON response was just a clip of part of a larger response and Diego helpfully used the same clip. You are correct that it is not valid JSON. – src Apr 26 '21 at 17:13