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?
The display width is wrapped inside parentheses following the base keyword for the type. For example, INT (4) specifies an integer with the display width of four digits. It is important to note that this attribute does not control the range of value that can be stored in the column.
Zerofill pads the displayed value of the field with zeros up to the display width specified in the column definition. For example, if column is set int(8), therefore the width is 8.
The maximum display width is 255. Display width is unrelated to the range of values a type can store, as described in Section 11.1.
An unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED , the size of the column's range is the same but its endpoints shift up, from -2147483648 and 2147483647 to 0 and 4294967295 .
This additional "feature" display width is quite confusing, because in other column types like CHAR
is specifies the length.
Here is a short breakdown:
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.ZEROFILL
, there will be no padding at all (no spaces and such)ZEROFILL
, you must be aware that the column also will be UNSIGNED
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With