database - MySQL: Why specify display width without using zerofill

Question

I have recently delved into the exciting world of SQL. I am still trying to wrap my head around the concepts. I have been following tutorials online. Many of these tutorials contain SQL for making a table like this.

CREATE TABLE `users` (
     `id` tinyint(4) NOT NULL AUTO_INCREMENT,
     `username` varchar(10) NOT NULL,
     `password` varchar(100) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

After seeing lines like id tinyint(4)I wondered what the parameter passed to the data type. I thought "does that mean id can be any integer between -128 and 127 (no more than 4 characters)?"

So I consulted the docs. This is what MySQL docs have to say about number type attributes.

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. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits

When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.

So if I'm reading this right, declaring things like INT(255) are useless unless you are using zerofill. Ok makes sense you declare a datatype the database allocates enough space for that database.

So why do people write code like this? Does it serve a purpose? Am I completely misunderstanding?

Answer 1

This additional "feature" display width is quite confusing, because in other column types like CHAR is specifies the length.

Here is a short breakdown:

• Most important: It is not specifying the "storage space" or "number of digits". It is just saying how the data in this column is formatted before it is returned. INT(5) can store the same values like INT(16) or INT(255) - all three can store all (and only) values that are valid for INT. INT(255) can not store a number with 255 digits. The storage space for all of them is the space an INT occupies.
• If you use ZEROFILL on a column with display width, and the string representation of the stored number is shorter than the display width, it will be left-padded with zeros. If it is longer, nothing happens. If you choose INT(5) and the value is 13, it will be returned as 00013. If the value is 123456 it will be returned as 123456.
• If you do not use ZEROFILL, there will be no padding at all (no spaces and such)
• If you use ZEROFILL, you must be aware that the column also will be UNSIGNED
• In any case, the display width is returned when the table meta data is queried. So an application could know how the data should be formatted.

I dislike the display width, because the storage layer is "knowing" about visual presentation of the stored data. Besides this, there is no use that I am aware of.