Numeric data types and zerofill

One error I see people make in their data assumptions is most likely a result of GUIs adding unnecessary information when you create tables with their editors.

Many times I've seen people use INT(11) or SMALLINT(7) for instance. Then they make the erroneous assumption that the number in brackets determines how many digits which can be held in that data type.

This is incorrect. The various limits are included in the table below. The number in brackets determines how many zeros are used when padding out a zerofill column type.

If you create a column INT(11) and insert values they will show up as entered. 100 for instance would show up just like it is entered.

If you make the column INT(11) ZEROFILL, on the other hand, 100 is displayed as follows: 00000000100

The 11 says to pad the number out with zeros until 11 characters have been reached, it has nothing to do with the maximum value held by INT.

A small but important fact, that you now know.