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 sh开发者_Go百科ould I use to prevent this?

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.

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.

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.

You might look into altering the field to use UNSIGNED ZEROFILL. This should allow you to store the number with leading zeros.

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.