MySQL Integer data types with length

We have come across integer data types like INT, TINYINT etc. While creating/modifying a column we generally give a length to it like INT(10). Today I really found its usefulness.

Often it is documented that the length just specifies how many characters to display when selecting data. But the length only works only when the column is created as UNSIGNED ZEROFILL and its for display purpose only.

For example a column created as INT(5) UNSIGNED having a value of 100 will always display 100 when we select the column. But if the column is created with INT(5) UNSIGNED ZEROFILL then it will display 00100 i.e  with 2 leading zeroes.

Thus integers created with length and ZEROFILL option is same as padding 0’s in the left which can be achieved with LPAD.

I wanted to left pad a number with leading zeroes and thought this one is better than updating the column with LPAD because the column still remains numeric and I can still perform numeric operations. Also in case I want to change the number of leading zeroes then I only need to alter the length of the column instead of updating. Hope this helps anyone who need to add leading zeroes to a number.