tinyint(size), varchar(size): "size" explaination

Question

I understand that tinyint(1) and tinyint(2) have the same range of storage space.

The only difference would be that the display width is differentiated. Does this mean that tinyint(1) will store all types of integers but only display the range from 0 to 9 correctly? And tinyint(2) displays only 0 to 99 correctly?

And it is mentioned that max size is tinyint(255). Also there are others like varchar(500) type of width. Unless an essay is being stored, under what circumstances would you need such a large display width for both text and integers? Like password encryption or something?

Do correct me if any of the above information written in the question is wrong.

Add on question: The display width only works for char, varchar etc but not for integer types.

However, why bother putting "M" since for integers it will not make a difference in storing it other than zerofill?

Answer 1

Your Solution to confusion is-

In Mysql , int(6) doesn't mean they can store up to 6 digit. not more than 999999.

However CHAR(6) This means a character column, with a maximum length of 6 characters, such as these words:

houses

If I tried storing the word “special” in that column, MySQL would chop the value to be “specia”, since the original word has 7 characters.

Anyway, integer columns all have a preset range of values allowed. The number in parenthesis only indicates the display width.

This is probably still confusing, so let me explain further…

The display width is a number from 1 to 255. You can set the display width if you want all of your integer values to “appear” similarly:

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

M indicates the maximum display width for integer types. The maximum display width is 255. Display width is unrelated to the range of values a type can contain, . For floating-point and fixed-point types, M is the total number of digits that can be stored.

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

So Answer of your Question is

create table foo
(
col2 tinyint(2) unsigned zerofill, col4 tinyint(4) unsigned zerofill, col5notzerofill        tinyint(5)  
)engine=innodb;

insert into foo values (1,2,3),(11,12,14),(123,123,156),(1234,1234,12756);
select * from foo;

OUTPUT :-
+------+------+-----------------+
| col2 | col4 | col5notzerofill |
+------+------+-----------------+
|   01 | 0002 |               3 |
|   11 | 0012 |              14 |
|  123 | 0123 |             127 |
|  255 | 0255 |             127 |
+------+------+-----------------+

*So Understand, In Mysql display width only work with zerofill.

*if you use do not use zerofill, it is not effective.

And if you use zerofill then mysql will check display widht , and if you do not assign then mysql will automatically assign display-width to that column.*