What does & quot; size & quot; in int (size) of MySQL mean ?, Mysqlmean

What does "size" in int (size) of MySQL mean ?, Mysqlmean

What does "size" in int (size) of MySQL mean?

Https://alexander.kirk.at/2007/08/24/what-does-size-in-intsize-of-mysql-mean/

 

Friday, August 24th, 2007 at + 0000 (UTC) by Alexander Kirk

I was always wondering whatSizeOf numeric columns in MySQL was. Forgive me if this is obvious to someone else. But for me the MySQL manual lacks a great deal in this field.

TL; DR: It's about the display width. You only see it when you use ZEROFILL.

Usually you see something likeInt (11)InCREATE TABLEStatements, but you can also change itInt (4).

So what does this size mean? Can you store higher values inInt (11)Than inInt (4)?

Let's see what the MySQL manual says:

INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

No word aboutM. The entry aboutBOOLSuggests that the size is not there for fun as it is a synonymTINYINT (1)(With the specific size of 1 ).

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true: […]

SoTINYINT (1)Must be different in some way fromTINYINT (4)Which is assumed by default when you leave the size out1. Still, you can store for example 100 intoTINYINT (1).

Finally, let's come to the place of the manual where there is the biggest hint to what the number means:

Several of the data type descriptions use these conventions:
M indicates the maximum display width for integer types. For floating-point and fixed-point types, M is the total number of digits that can be stored. For string types, M is the maximum length. The maximum allowable value of M depends on the data type.

It's aboutDisplay width. The weird thing is, though2, that, for example, if you have a value of 5 digits in a field with a display width of 4 digits, the display width will not cut a digits off.

If the value has less digits than the display width, nothing happens either. So it seems like the display doesn't have any effect in real life.

Now2ZEROFILLComes into play. It is a neat feature that pads values that are (here it comes) less than the specifiedDisplay widthWith zeros, so that you will always receive a value of the specified length. This is for example useful for invoice ids.

So, concluding:The size is neither bits nor bytes. It's just the display width, that is used when the field hasZEROFILLSpecified.

If you see any more uses inSizeValue, please tell me. I am curious to know.

1 See this example:
Mysql> create table a (a tinyint );
Query OK, 0 rows affected (0.29 sec)
Mysql> show columns from;
+ ------- + ------------ + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------ + ------ + ----- + --------- + ------- +
| A | tinyint (4) | YES | NULL |
+ ------- + ------------ + ------ + ----- + --------- + ------- +
1 row in set (0.26 sec)

Mysql> alter table a change a tinyint (1 );
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> insert into a values (100 );
Query OK, 1 row affected (0.00 sec)

Mysql> select * from;
+ ----- +
| A |
+ ----- +
| 1, 100 |
+ ----- +
1 row in set (0.00 sec)

 

2 Some code to better explain what I described so clumsily.
Mysql> create table B (B int (4 ));
Query OK, 0 rows affected (0.25 sec)

Mysql> insert into B valuees (10000 );
Query OK, 1 row affected (0.00 sec)

Mysql> select * from B;
+ ------- +
| B |
+ ------- +
| 1, 10000 |
+ ------- +
1 row in set (0.00 sec)

Mysql> alter table B change B int (11 );
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> select * from B;
+ ------- +
| B |
+ ------- +
| 1, 10000 |
+ ------- +
1 row in set (0.00 sec)

Mysql> alter table B change B int (11) zerofill;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> select * from B;
+ ------------- +
| B |
+ ------------- +
| 1, 00000010000 |
+ ------------- +
1 row in set (0.00 sec)

Mysql> alter table B change B int (4) zerofill;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> select * from B;
+ ------- +
| B |
+ ------- +
| 1, 10000 |
+ ------- +
1 row in set (0.00 sec)

Mysql> alter table B change B int (6) zerofill;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> select * from B;
+ -------- +
| B |
+ -------- +
| 1, 010000 |
+ -------- +
1 row in set (0.00 sec)