Why is the length of the value 11 instead of 10 in mysql?

Question:

I started optimizing the database, I calculated that there are 10 digits in the time function. When it is 11, the year 2400 will come.

Question: why do people write int (11) instead of int (10)?

Answer:

For int, the value in parentheses does not indicate the length of the value, not the size of the value, but only shows the possible length of the output. And only if the column has the zerofill attribute

Let's consider some examples:

>> CREATE TABLE test (
    id INT,
    id1 INT(10),
    id2 INT(11),
    id3 INT(2),
    id4 INT(10) ZEROFILL,
    id5 INT(11) ZEROFILL,
    id6 INT(2) ZEROFILL
);

>> SHOW CREATE TABLE test;
CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `id1` int(10) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  `id3` int(2) DEFAULT NULL,
  `id4` int(10) unsigned zerofill DEFAULT NULL,
  `id5` int(11) unsigned zerofill DEFAULT NULL,
  `id6` int(2) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

>> INSERT INTO test VALUES
    (1, 1, 1, 1, 1, 1, 1),
    (111, 111, 111, 111, 111, 111, 111);

>> SELECT * FROM test;
+------+------+------+------+------------+-------------+------+
| id   | id1  | id2  | id3  | id4        | id5         | id6  |
+------+------+------+------+------------+-------------+------+
|    1 |    1 |    1 |    1 | 0000000001 | 00000000001 |   01 |
|  111 |  111 |  111 |  111 | 0000000111 | 00000000111 |  111 |
+------+------+------+------+------------+-------------+------+

For columns id , id1 , id2 , id3 we did not specify the ZEROFILL attribute ZEROFILL and there is no difference in the output of these columns, even though the output size for these columns is different.

For columns id4 , id5 , id6 we specified the ZEROFILL attribute and for each column its own output size, so if the length of the string representation of a number is less than the size of the output (the size indicated in parentheses), then the string representation of the number will be padded with zeros from the left to the required length.

If the length of the string representation of the number is greater than the size of the output (column id6 and its value 111 ), then no padding with zeros occurs.

---

It is worth paying attention to the id field – we did not specify the size of the output for it, but at the same time in SHOW CREATE TABLE size is still set and is equal to 11 .

11 – since the id column is INT , i.e. signed integer, according to the documentation, there may be a value equal to -2147483648 , which is exactly 11 digits (10 digits + minus sign)

If we describe the column as INT UNSIGNED , then there can be no negative values ​​(minus sign), and the maximum number in length is 4294967295 , which is 10 digits. Therefore, for INT UNSIGNED , the SHOW CREATE TABLE output will indicate INT(10)