MySQL insert into table with 00001 automatically converts to 1

Question

I have a table with a column id INT(11). When I do:

insert into table (id) VALUES ('00001');

it converts it to 1. I don't mind this behavior but I am wondering if this will change in future versions of mysql. Is there documentation or a reason on why this works?

you can set `UNSINGNED ZEROFILL` of fixed length of INT datatype — xkeshav, Oct 14 '14 at 13:47
if you want to store the values as it is make the data type for id as varchar2 it must be Integer or Decimal — dev, Oct 14 '14 at 13:47
Look at this one, i think it will help : http://stackoverflow.com/questions/5256469/what-is-the-benefit-of-zerofill-in-mysql — Kac Per, Oct 14 '14 at 13:50

score 3 · Answer 1 · edited May 23 '17 at 11:54

3

This is because of the ZEROFILL flag on the column. If the flag is set, it adds zeros as padding to your numbers so that they meet the total "width" of the number. So if you have an INT(3) and you INSERT 1, you get 001.

In your case, you probably have an INT(1) so it is just truncating your leading zeros because they do not affect the value of the number—only the display of the number.

What is the benefit of zerofill in MySQL?

edited May 23 '17 at 11:54

Community

1
1

answered Oct 14 '14 at 13:47

Alex W

37,233
13
109
109

Ah ok. My integer is NOT zero filled but just wondered why it actually worked. It seems because of the zero fill is why it works (even though I am not using it) – Chris Muench Oct 14 '14 at 14:01

MySQL insert into table with 00001 automatically converts to 1

1 Answers1