What is the difference (when being applied to my code) between INT(10) and INT(12)?

Question

If I use INT(12) vs INT(10) or INT(8) what will this actually do in terms of me using in code?

(This is a spin off of a previous question) I read through the manuals and I think I understand what they're saying, but I don't actually know how it would apply to my php/mysql coding.

Can someone provide an example of where this would actually matter?

Answer 1

The argument to integer types in MySQL has no effect on the storage of data or the range of values supported by each data type.

The argument only applies to display width, which may be used by applications as Jonathan Fingland mentions. It also comes up when used in combination with the ZEROFILL option:

CREATE TABLE foo (
  i INT(3) ZEROFILL, 
  j INT(6) ZEROFILL, 
  k INT(11) ZEROFILL
);
INSERT INTO foo (i, j, k) VALUES (123, 456, 789);
SELECT * FROM foo;

+------+--------+-------------+
| i    | j      | k           |
+------+--------+-------------+
|  123 | 000456 | 00000000789 |
+------+--------+-------------+

See how ZEROFILL makes sure the data is zero-padded to at least the number of digits equal to the integer type argument.

Without ZEROFILL, the data is space-padded, but since spaces are often trimmed anyway, it's harder to see that difference.

What affect does it have on your PHP code? None. If you need to output columnar data, or space-pad or zero-pad values, it's more flexible to use sprintf(),

Answer 2

Short answer: there's no difference.

The display width is passed back in the "meta data". It's up to the application to make use of it. Normally it's just ignored. I don't think you can get it using the mysql functions, but you might be able to with mysqli using mysqli_fetch_field_direct.

Answer 3

You shouldn't have to change anything as it has no impact on the data returned. The information can be used by applications that want to use it.

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

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). 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 the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

(Emphasis mine)