mysql int fields does not truncate

as all you know, when you describe varchar or integer fields you should set the length of them...

something like int(5) or varchar(5)...

but when you try add 123456 to both fields.. while varchar field truncates the value, integer field does not tr开发者_如何学Cuncate it...

so what's the aim of describing int length?

int(5) does not do what you think it does: it specifies an integer field with a display width of 5 digits, i.e. numbers shorter than 5 digits will be padded with space characters.

In MySQL, int values are always 4 bytes wide and can go from -2,147,483,648 to 2,147,483,647.

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

The N in INT(N), indicating application display length; is was very misleading, due to the syntax similarity to VARCHAR(N), and understandably, often misunderstood. It's effectively meaningless for all applications I've seen.

This goes for all TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT.

It appears this "length" is used for display only: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

The size of the INT type is neither bits nor bytes. It's just the display width that is used when the field has ZEROFILL specified.

See this blog article for an in depth explanation.

FRom 10.2. Numeric Types

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly.