CODEWITHSUNDEEP
mysql
Latox
Latox

Reputation: 4695

MySQL stripping off leading zero from integer column

I have a bigint field which when entering a number such as '05555555555' for example, the 0 is being stripped off and only inserting '5555555555'.

What data type should I use to prevent this?

Upvotes: 3

Views: 3118

Answers (4)

Mitch Wheat
Mitch Wheat

Reputation: 300529

You can't. Integer columns (bigint's) do not store leading zeros (ie. in a visual representation)

Rather than attempt to store a leading zero (by using a varchar field), have a view (or whatever) format the integer into a string in the format you require.

If you need to store something that is actually a string in the Domain model (e.g. a phone number), use a string rather than an integer type field.

Upvotes: 4

user1392021
user1392021

Reputation: 99

Problem is if you got a big database with hundredthousands of rows, a bigint, is much faster then a VARHAR field. I got a similar issue with a product database full with European Article Numbers(EAN).Some of those codes start with a leading 0 When i change it to VARCHAR it takes 8 seconds to load certain pages that search for EAN codes, when I change it to BIGINT it turns into 2 seconds. Big difference in speed indeed.

Upvotes: -3

Andrew Moore
Andrew Moore

Reputation: 95334

BIGINT and other Integer columns do not store the visual representation of a number, only the number itself in binary form (BIGINT is 8 bytes). 5555555555 is stored as:

00000000 00000000 00000000 00000001 01001011 00100011 00001100 11100011

If the preceding zeros are significant to the integrity of your data, you should be using a VARCHAR or CHAR instead of an integer type. Numerical datatypes should only be used for numerical data. US ZIP Codes and phone numbers are NOT numerical data.

Upvotes: 3

nitro2k01
nitro2k01

Reputation: 7745

bigint stores the data as a number, and 05555555555 and 5555555555 are the same number. You'll need a string type to preserve the leading zero, e.g. varchar with a suitable maximum length.

Upvotes: 2

Related Questions