Database numeric field starting with 0

I am using a field named as area_code that has int data-type in MySQL. But it doesn't allow me to store exact values like 0781, 0727, 0788 Bcoz they are starting with 0. It stores the values as 781, 727 and 788 i.e. it truncates 0 from the v开发者_高级运维alues and save it.

Please help me..

Area codes are not numbers, but text (which contains digits only). Declare area_code as a varchar field of appropriate size.

drop table if exists foo;
create table foo
(
area_code smallint(4) unsigned zerofill not null default 0
)
engine=innodb;

insert into foo (area_code) values (781),(727);

select * from foo;
+-----------+
| area_code |
+-----------+
|      0781 |
|      0727 |
+-----------+

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Use a character based data-type rather than a numeric data-type.

Use VARCHAR, because you don't need to make any arithmetic actions with area codes

if you want your result as 4 digits you can maybe do it in this way:

select lpad(area_code, 4, '0') from zipcode_list;

now you can store the 3 numbers in your database and returning the zero before.