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

What is 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, 21:40 +0000 (UTC) by Alexander Kirk

I was all wondering what's the size of 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 have see it when you use Zerofill.

Usually you see something like Int (one) in the CREATE TABLE statements, but can also change it to int (4).

So why does this size mean? Can you store higher values in a int (one) than in an int (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 about the M. The entry about BOOL suggests that the size was not there for fun as it was a synonym forTINYINT (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 is synonyms for TINYINT (1). A value of zero is considered false. Non-zero values is considered true: [...]

So TINYINT (1) must are different in some the From TINYINT (4) which are assumed by default if you leave T He size out1. Still, you can store for example to a TINYINT (1).

Finally, let's come to the place of the manual where there are 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 are 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 about the display width. The weird thing is, THOUGH2, which, for example, if you had a value of 5 digits in a field with a display width of 4 digit S, the display width is 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 has any effect in real life.

Now2 Zerofill comes into play. It is a neat feature this pads values that was (here it comes) less than the specified display width with zeros, So, you'll always receive a value of the specified length. Example useful for invoice IDs.

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

If you see any more uses in the size of value, 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 A;
+-------+------------+------+-----+---------+-------+
| 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;
+-----+
| A |
+-----+
| 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 values (10000);
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from B;
+-------+
| B |
+-------+
| 10000 |
+-------+
1 row in Set (0.00 sec)

mysql> ALTER TABLE B change b b int (11);
Query OK, 1 row Affected (0.00 sec)
Records:1 duplicates:0 warnings:0

Mysql> SELECT * from B;
+-------+
| B |
+-------+
| 10000 |
+-------+
1 row in Set (0.00 sec)

mysql> ALTER TABLE B change b b int (one) Zerofill;
Query OK, 1 row Affected (0.00 sec)
Records:1 duplicates:0 warnings:0

Mysql> SELECT * from B;
+-------------+
| B |
+-------------+
| 00000010000 |
+-------------+
1 row in Set (0.00 sec)

mysql> ALTER TABLE B change b b int (4) Zerofill;
Query OK, 1 row affected (0.08 sec)
Records:1 duplicates:0 warnings:0

Mysql> SELECT * from B;
+-------+
| B |
+-------+
| 10000 |
+-------+
1 row in Set (0.00 sec)

mysql> ALTER TABLE B change b b int (6) Zerofill;
Query OK, 1 row affected (0.01 sec)
Records:1 duplicates:0 warnings:0

Mysql> SELECT * from B;
+--------+
| B |
+--------+
| 010000 |
+--------+
1 row in Set (0.00 sec)

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