Tinyint (size), varchar (size): explanation "size"

Question

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

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

And it is mentioned that the maximum size is tinyint (255). There are others as well, such as the varchar (500) type. If the essay is not stored, under what circumstances would you need such a large screen width for text and integers? How is password encryption or something else?

Correct me if any of the above information written in the question is incorrect.

Add to the question: Display width only works for char, varchar, etc., but not for integer types.

However, why bother putting “M”, since for integers this will not affect saving it other than zerofill?

Answer 1

Your solution to the confusion is

In Mysql, int (6) does not mean that they can store up to 6 digits. no more than 999999.

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

houses

If I tried to keep the word “special” in this column, MySQL would slice the value “specia” because the original word has 7 characters.

In any case, whole columns have a given range of valid values. The number in parentheses indicates only the width of the display.

This is probably still confusing, so let me explain in more detail ...

The width of the display is a number from 1 to 255. You can set the width of the screen if you want all your integer values ​​to "display" in the same way:

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

M indicates the maximum display width for integer types. The maximum width of the screen is 255. The width of the display is not related to the range of values ​​that the type may 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 the answer to your question

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, we understand that in Mysql the display width only works with zerofill.

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

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