MySQL 5.1.41 leading zero is deleted

Question

I have a MySQL database where I want to store phone numbers among other things.

The fieldtype is INT(10)

When I try to insert a number starting with a 0, like 0504042858 it's stored like 504042858. This only happens with phone numbers with leading zeros. When the number start with any other number, it's stored correctly.

What am I doing wrong?

Answer 1

You should probably store phone numbers as a varchar. Phone numbers are only numeric by accident.

You may also be interested in checking out the following Stack Overflow posts:

• What datatype should be used for storing phone numbers in SQL Server 2005?
• Common MySQL fields and their appropriate data types

Answer 2

You can give length INT(11) with attribute value UNSIGNED_ZEROFILL. it will fill all 11 digits and if any digit length is less than 11, it will add zero itself before the value. This might solve your problem.

Answer 3

it is removing the leading zero because mathematically they are the same and removing the leading zero is a quick storage optimization. In addition it also makes the numbers easier to read imagine a number padded with several leading zeros in a column of several hundred numbers.

I agree with Daniel change your column to a varchar.