In the MySQL team we are currently discussing if we should deprecate the integer display width in numeric types. For example:
CREATE TABLE my_table ( id INT(11) NOT NULL PRIMARY KEY auto_increment );
The (11) does not affect the storage size of the data type, which for an INT will always be 4 bytes. It affects the display width.
Our rationale for proposing this should be deprecated is that it is a common source of confusion amongst users.
We are also discussing deprecating the non-standard ZEROFILL type attribute, which is the only modern consumer of this display width meta data. For example:
CREATE TABLE my_table ( id INT(11) ZEROFILL NOT NULL PRIMARY KEY auto_increment ); INSERT INTO my_table VALUES (1); mysql> SELECT * FROM my_table; +-------------+ | id | +-------------+ | 00000000001 | +-------------+ 1 row in set (0.00 sec)
StackOverflow has a good example of how ZEROFILL is useful:
[..] In Germany, we have 5 digit zipcodes. However, those Codes may start with a Zero, so 80337 is a valid zipcode for munic, 01067 is a zipcode of Berlin.
As you see, any German citizen expects the zipcodes to be displayed as a 5 digit code, so 1067 looks strange.
[..]
This usage is true for any numeric values that require leading zeros, such as some phone numbers.
Upgrade Paths
There are two possible upgrade paths to migrate away from ZEROFILL.
Option #1 – Move to CHAR/VARCHAR
This option is the most transparent for applications, and changes the data type to be a string instead of numeric. For example:
CREATE TABLE my_zip_codes (
id INT NOT NULL PRIMARY KEY auto_increment,
zip_code INT(5) ZEROFILL
);
INSERT INTO my_zip_codes (zip_code) VALUES ('01234'), ('54321'), ('00123'), ('98765');
mysql> select * from my_zip_codes;
+----+----------+
| id | zip_code |
+----+----------+
| 1 | 01234 |
| 2 | 54321 |
| 3 | 00123 |
| 4 | 98765 |
+----+----------+
4 rows in set (0.00 sec)
ALTER TABLE my_zip_codes CHANGE zip_code zip_code CHAR(5);
In the case of a CHAR(5) the storage requirements will only be one byte higher than that of an integer. In the case of other data types (phone numbers requiring leading zeros) it might be slightly more efficient to store as an integer.
Option #2 – Format integers at a different layer
This option retains the storage efficiency of an integer, but moves the presentation into the application. For example:
CREATE TABLE my_zip_codes (
id INT NOT NULL PRIMARY KEY auto_increment,
zip_code INT(5) ZEROFILL
);
INSERT INTO my_zip_codes (zip_code) VALUES ('01234'), ('54321'), ('00123'), ('98765');
ALTER TABLE my_zip_codes CHANGE zip_code zip_code INT;
mysql> select * from my_zip_codes;
+----+----------+
| id | zip_code |
+----+----------+
| 1 | 1234 |
| 2 | 54321 |
| 3 | 123 |
| 4 | 98765 |
+----+----------+
4 rows in set (0.00 sec)
It will also technically be possible to retrofit this into legacy applications that require ZEROFILL presentation returning from MySQL. This can be done with a query rewrite plugin to modify SELECT statements to add padding:
mysql> SELECT id, LPAD(zip_code, 5, '0') as zip_code FROM my_zip_codes; +----+----------+ | id | zip_code | +----+----------+ | 1 | 01234 | | 2 | 54321 | | 3 | 00123 | | 4 | 98765 | +----+----------+ 4 rows in set (0.01 sec)
Conclusion
We are seeking feedback from the community in response to this proposal. If you have found the existing behavior confusing, or will be affected by the removal of zero fill, please leave a comment or get in touch! We would love to hear from you.
