What is the size of column of int(11) in mysql in

Question

What is the size of column of int(11) in mysql in bytes?

And Maximum value that can be stored in this columns?

Answer 1

4294967295 is the answer, because int(11) shows maximum of 11 digits IMO

Answer 2

As others have said, the minumum/maximum values the column can store and how much storage it takes in bytes is only defined by the type, not the length.

A lot of these answers are saying that the (11) part only affects the display width which isn't exactly true, but mostly.

A definition of int(2) with no zerofill specified will:

• still accept a value of 100
• still display a value of 100 when output (not 0 or 00)
• the display width will be the width of the largest value being output from the select query.

The only thing the (2) will do is if zerofill is also specified:

• a value of 1 will be shown 01.
• When displaying values, the column will always have a width of the maximum possible value the column could take which is 10 digits for an integer, instead of the miniumum width required to display the largest value that column needs to show for in that specific select query, which could be much smaller.
• The column can still take, and show a value exceeding the length, but these values will not be prefixed with 0s.

The best way to see all the nuances is to run:

CREATE TABLE `mytable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `int1` int(10) NOT NULL,
    `int2` int(3) NOT NULL,
    `zerofill1` int(10) ZEROFILL NOT NULL,
    `zerofill2` int(3) ZEROFILL NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mytable` 
(`int1`, `int2`, `zerofill1`, `zerofill2`) 
VALUES
(10000, 10000, 10000, 10000),
(100, 100, 100, 100);

select * from mytable;

which will output:

+----+-------+-------+------------+-----------+
| id | int1  | int2  | zerofill1  | zerofill2 |
+----+-------+-------+------------+-----------+
|  1 | 10000 | 10000 | 0000010000 |     10000 |
|  2 |   100 |   100 | 0000000100 |       100 |
+----+-------+-------+------------+-----------+

Notice how int1 column has a much smaller display width than zerofill2 even though the length is larger.

This answer is tested against MySQL 5.7.12 for Linux and may or may not vary for other implementations.

Answer 3

according to this book:

MySQL lets you specify a “width” for integer types, such as INT(11). This is meaningless for most applications: it does not restrict the legal range of values, but simply specifies the number of characters MySQL’s interactive tools will reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(20).

Answer 4

What is the size of column of int(11) in mysql in bytes?

(11) - this attribute of int data type has nothing to do with size of column. It is just the display width of the integer data type. From 11.1.4.5. Numeric Type Attributes:

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits.

Answer 5

INT(somenumber) will make difference only in term of display, that is to show in the number in 'somenumber' digits, and not restricted only to 11. You pair it using ZEROFILL, which will prepend the zeros until it matches your length

Note that the value stored in database is not affected, any calculation will still behave as it is.

Remarks :

• if the value has less digit than 'somenumber', ZEROFILL will prepend zeros.

  INT(5) ZEROFILL with the stored value of 32 will show 00032
  INT(5) with the stored value of 32 will show 32
  INT with the stored value of 32 will show 32

• if the value has more digit than 'somenumber', the stored value will be shown.

  INT(3) ZEROFILL with the stored value of 250000 will show 250000
  INT(3) with the stored value of 250000 will show 250000
  INT with the stored value of 250000 will show 250000

The similar applies to BIGINT, MEDIUMINT, SMALLINT, and TINYINT as well.

Answer 6

An INT will always be 4 bytes no matter what length is specified.

• TINYINT = 1 byte (8 bit)
• SMALLINT = 2 bytes (16 bit)
• MEDIUMINT = 3 bytes (24 bit)
• INT = 4 bytes (32 bit)
• BIGINT = 8 bytes (64 bit).

The length just specifies how many characters to display when selecting data with the mysql command line client.

... and the maximum value will be 2147483647 (Signed) or 4294967295 (Unsigned)