Zerofill

The number shown by integer column definitions is the display width. Given INT(y) ZEROFILL, MySQL will pad the number with zeros up to y digits. The display width does not affect the column range; both a tinyint(1) and a tinyint(300) can store values between -128 and 127.

For example:

 mysql> CREATE TABLE zerofill_test (
     ->   h INT(4) ZEROFILL,
     ->   m INT(4)
     -> );
 Query OK, 0 rows affected (0.02 sec)
 
 mysql> INSERT INTO zerofill_test VALUES ( 1, 1 ), (10000, 10000);
 Query OK, 1 row affected (0.00 sec)
 
 mysql> SELECT * FROM zerofill_test;
 +-------+-------+
 | h     | m     |
 +-------+-------+
 |  0001 |     1 |
 | 10000 | 10000 |
 +-------+-------+
 1 row in set (0.00 sec)
 
 mysql>

To quote the manual:

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 is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.

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.

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width.

Note: The ZEROFILL attribute is stripped when a column is involved in expressions or UNION queries.

Problem: displaywidth without ZEROFILL

CREATE TABLE zerofill_test1 (
    h INT(40) ZEROFILL, 
    m INT(40)
);

insert into zerofill_test1 values (2,2);

select * from zerofill_test1;
+------------------------------------------+------+
| h                                        | m    |
+------------------------------------------+------+
| 0000000000000000000000000000000000000002 |    2 | 
+------------------------------------------+------+

In the column named m the value is not prefixed with 39 blanks? Following the MySQL manual both columns had to be 40 columns wide...

Bug, feature or only mistake in the documentation?