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

Question

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

And Maximum value that can be stored in this columns?

Possible duplicate of [Whats the size of an SQL Int(N)?](http://stackoverflow.com/questions/4151259/whats-the-size-of-an-sql-intn) — cellepo, Oct 03 '16 at 19:47
@cellepo one is generic sql the other is specific for mysql (plus this one has way more views). If you want to go on a dupe hunt and have me quickly close them in one vote, visit [SOBotics](http://chat.stackoverflow.com/rooms/111347) chat and ping me. But the one to close has to be tagged `mysql` prior to my arrival. Thx — Drew, Oct 03 '16 at 22:39
Hey, Guys. It is possibly duplicate, but it is more search friendly. Most people do search like this using int(11). and it is solving the questions of other people. you can check its number of view comparative to other one. and user will find more detailed answer here. — Gaurav, Oct 04 '16 at 06:43

score 884 · Accepted Answer · edited Feb 11 '20 at 17:43

884

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 pad when selecting data with the mysql command line client. 12345 stored as int(3) will still show as 12345, but if it was stored as int(10) it would still display as 12345, but you would have the option to pad the first five digits. For example, if you added ZEROFILL it would display as 0000012345.

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

edited Feb 11 '20 at 17:43

Chuck Le Butt

47,570
62
203
289

answered Apr 12 '11 at 11:02

Matt MacLean

19,410
7
50
53

165

then what is meaning of 11 in int(11) here. – Gaurav Apr 12 '11 at 11:04
4

@Gaurav perhaps `10` ints with `-` before it. – S L Apr 12 '11 at 11:05
1

@experimentX : then max value can be 99999999999 or anything else. please give me any example. – Gaurav Apr 12 '11 at 11:07
7

the (11) defines how many spaces to use when selecting from the mysql command line. Example. INT(4) would display as |0001| where as INT (11) would display as |00000000001|. But the actual number is still stored using 4 bytes and the maximum value will be 2147483647 (Signed) or 4294967295 (Unsigned) – Matt MacLean Apr 12 '11 at 11:08
86

Column INT(4), integer 1 would be displayed as 0001 ONLY if the column was also specified as zerofill. Otherwise, it will just be displayed as number 1, without extra digits. @Gaurav - don't mix how mysql displays data and how it stores it, those are two different things. What you see is not what it really might be. – Michael J.V. Apr 12 '11 at 11:15
@Michael +1 for zerofill. i'm still amazed that people dont get this – Jon Black Apr 12 '11 at 11:49
14

@Michael Wow, really interesting. So if you create a column INT(3) and store a value 5001, it will store 5001 but only display 1. I did not know that. – andrewtweber Jan 13 '12 at 21:37
1

Do I understand correctly then that specifying a width of 11 for an *unsigned* INT is pointless? It seems to me the maximum width in that case is 10. – Madbreaks Aug 12 '15 at 16:47
edited cuz it is collecting more answers. Oh do we need more answers on this? :P – Drew Dec 20 '15 at 18:54
and yes @Madbreaks width is display width. Irrelevant or pointless as you say – Drew Dec 20 '15 at 18:55
25

@andrewtweber: `5001` will display as `5001` even if field is defined as `INT(3)`. See @priyabagus [answer](http://stackoverflow.com/a/27519793/3366962) below and [here](https://blogs.oracle.com/jsmyth/entry/what_does_the_11_mean). – go2null Apr 22 '16 at 16:26
18

It you don't provide a length for integer fields, MySQL will set a default value (tinyint 4, smallint 6, mediumint 9, int 11, bigint 20) It is worthy to note that these default lengths are enough to display any number that can be stored in this field (ex : smallint max value is 2^16 = 65536, 5 digits) Unless you have a very good reason to do otherwise, I'd recommand to leave the default sizes to avoid bad surprises. – Thibault Witzig Jan 02 '17 at 10:04
This answer claims that the mysql CLI client does something special with displaying INTs, which implicitly means that other clients don't. That does not appear to be true in my tests. The MySQL CLI client does *nothing* special. The length indeed **only** has effect in **any** way when using zerofill – aross Dec 06 '17 at 10:51
FWIW: @Drew - Madbreaks point was that the maximum value of an unsigned integer *only uses 10 decimal digits*, so its pointless that the default specification is "(11)", as it *can't possibly need 11 digits to display its value*. Logically, the default spec for unsigned int should be "(10)". Not that it matters... – ToolmakerSteve Oct 06 '19 at 18:53
@MichaelJ.V. This is a bit late, but what you say is false. An `int(11)` with more than 11 digits will still display more than 11 digits. The number is used for padding only, and only in very specific situations. – Chuck Le Butt Feb 11 '20 at 17:40
I seriously question the limitations built into MySQL regarding integers: What is the point of BIGINT field type if it still restricted to MAX_INT(32-bit-signed) for actual storage? Who develops schema that uses negative IDs (to get the other half of possible values of said ID field)? What if my application requires storage/retrieval of 64-bit unsigned integers? This technology is flimsy and will not age well. – Daniel Randall Apr 02 '21 at 21:10
@gaurav Here in this int (11) stands for 4bytes which is 32 bit and now the number we can store within this int(11) is 2^31 = 2,147,483,648 Signed values will store 2,147,483,648 (from -... 0 to +...) Unsigned values stores 4294967296 (here there will be no negative values) Hope you got the proper understanding of it. – Atmiya Kolsawala Jan 24 '22 at 09:24

score 244 · Answer 2 · edited Feb 09 '21 at 03:11

244

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

So, for any number of x in INT(x)

if the stored value has less digits than x, 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 stored value has more digits than x, it will be shown as it is.

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 actual value stored in database is not affected, the size is still the same, and any calculation will behave normally.

This also applies to BIGINT, MEDIUMINT, SMALLINT, and TINYINT.

edited Feb 09 '21 at 03:11

Community

1
1

answered Dec 17 '14 at 06:48

priyabagus

2,718
1
14
10

74

So the length does absolutely nothing unless you use zerofill? – brettwhiteman May 17 '16 at 08:35
48

@developerbmw - Correct. The length does absolutely nothing unless you use `ZEROFILL`. – Rick James Sep 16 '17 at 18:22
5

ZEROFILL is essentially mainframe compatibility mode. – Henk Poley Jan 22 '20 at 09:36
1

Another side effect of `ZEROFILL` is it will implicitly make your column `UNSIGNED`. So you can't use negative values on that _zerofill_-ed column. – priyabagus Jan 29 '21 at 00:19

S L · Answer 3 · 2013-11-05T03:53:24.657

128

According to here, int(11) will take 4 bytes of space that is 32 bits of space with 2^(31) = 2147483648 max value and -2147483648min value. One bit is for sign.

edited Nov 05 '13 at 03:53

answered Apr 12 '11 at 11:02

S L

14,262
17
77
116

1

Your example has 9 nines. Can you please confirm that your example is correct? If so, why does it say 10 when there are 9 nines? Is it the number of digits plus one plus the sign character? – Homer6 Jul 20 '12 at 19:12
7

No, the maximum number is 4294967295 if unsigned int, i.e. 2^32. The number in brackets does not affect the storage type. If you need more than 2^32, you need to go to bigint. – Kieran Tully Jun 27 '13 at 09:58
2

+Kieran is right. The max number is different to that in the answer. See [here](http://dev.mysql.com/doc/refman/5.0/en/integer-types.html) – daviewales Jul 13 '13 at 06:34

Programster · Answer 4 · 2019-10-07T14:40:14.943

52

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,
    `zf1` int(10) ZEROFILL NOT NULL,
    `zf2` int(3) ZEROFILL NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mytable` 
(`int1`, `int2`, `zf1`, `zf2`) 
VALUES
(10000, 10000, 10000, 10000),
(100, 100, 100, 100);

select * from mytable;

which will output:

+----+-------+-------+------------+-------+
| id | int1  | int2  | zf1        | zf2   |
+----+-------+-------+------------+-------+
|  1 | 10000 | 10000 | 0000010000 | 10000 |
|  2 |   100 |   100 | 0000000100 |   100 |
+----+-------+-------+------------+-------+

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

edited Oct 07 '19 at 14:40

answered Jun 12 '16 at 08:38

Programster

12,242
9
49
55

5

"Notice how int1 column has a much smaller display width than zerofill2 even though the length is larger" ... Ehm, that's just because the column name 'zerofill2' is 9 chars long vs 'int1' being 4. – designosis Sep 14 '16 at 16:27
2

... yes, so clearly length has no effect, even on display width when zerofill is not enabled. Otherwise int1 would be much wider with a width of 10 characters instead. – Programster Sep 15 '16 at 12:20
2

A good explanation with a solid example. Way better answer than the accepted one. Thank you @Programster. – Corin Oct 18 '17 at 16:34
Minor nit: As neokio points out, can't observe from this query the exact consequences of "int(3)" with zero fill. Shorten the column name to "zf2", to make the behavior clearer. Specifically, whether true or false [on given DB version] that "(with zerofill) 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," – ToolmakerSteve Jan 16 '19 at 21:27
The point ultimately will remain: a number will always be stored depending on its `type`; a number will always be displayed as stored without `zerofill`; a number will get a `left '0' padding` to match the display width if number of digits in number is less than display width; a number will be displayed as stored if number of digits in number is more than display width. – Fr0zenFyr May 03 '19 at 12:27
1

To clarify neokio's comment and my minor nit comment: The sentence *"Notice how int1 column has a much smaller display width than zerofill2 even though the length is larger."* should be removed from the answer. That is a consequence of the number of characters in the header word "zerofill2" - it has nothing to do with testing display widths. Better to give that column a shorter name, e.g. "zf2". Then we would have a table where "int1, int2, and zf2 are all exactly the same width, even though int2 and zf2 specify a small width (3)". – ToolmakerSteve Oct 06 '19 at 18:49
@ToolmakerSteve I tested against new names zf1 and zf2 and updated the answer accordingly, removing that note as you said. Good spot! – Programster Oct 07 '19 at 14:41
When I do "show columns" on a mysql server, the display width setting is not shown. When I do "show columns" on a mariadb server, the width setting *is* shown. How can I see a column's defined display width in mysql? – chrisinmtown Sep 03 '20 at 13:46

score 36 · Answer 5 · edited Nov 29 '18 at 03:13

36

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.

edited Nov 29 '18 at 03:13

StephenA

61
6

answered Mar 07 '13 at 08:46

ks1322

33,961
14
109
164

Also note the width property has been deprecated as of 8.0.17. https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html – Scott C Wilson Jun 14 '22 at 10:51

score 22 · Answer 6 · edited Nov 29 '18 at 03:13

A good explanation for this can be found here

To summarize : The number N in int(N) is often confused by the maximum size allowed for the column, as it does in the case of varchar(N).

But this is not the case with Integer data types- the number N in the parentheses is not the maximum size for the column, but simply a parameter to tell MySQL what width to display the column at when the table's data is being viewed via the MySQL console (when you're using the ZEROFILL attribute).

The number in brackets will tell MySQL how many zeros to pad incoming integers with. For example: If you're using ZEROFILL on a column that is set to INT(5) and the number 78 is inserted, MySQL will pad that value with zeros until the number satisfies the number in brackets. i.e. 78 will become 00078 and 127 will become 00127. To sum it up: The number in brackets is used for display purposes.
In a way, the number in brackets is kind of usless unless you're using the ZEROFILL attribute.

So the size for the int would remain same i.e., -2147483648 to 2147483648 for signed and 0 to 4294967295 for unsigned (~ 2.15 billions and 4.2 billions, which is one of the reasons why developers remain unaware of the story behind the Number N in parentheses, as it hardly affects the database unless it contains over 2 billions of rows), and in terms of bytes it would be 4 bytes.

For more information on Integer Types size/range, refer to MySQL Manual

@OldGeezer - agree, "number of rows" is irrelevant for most columns. Only relevant for auto-incrementing primary key (id) column. — ToolmakerSteve, Jan 16 '19 at 21:36

Sachin Agrawal · Answer 7 · 2015-12-20T18:22:57.747

13

In MySQL integer int(11) has size is 4 bytes which equals 32 bit.

Signed value is : -2^(32-1) to 0 to 2^(32-1)-1 = -2147483648 to 0 to 2147483647

Unsigned values is : 0 to 2^32-1 = 0 to 4294967295

edited Dec 20 '15 at 18:22

answered Dec 20 '15 at 18:13

Sachin Agrawal

131
1
3

score 11 · Answer 8 · answered Jan 16 '16 at 21:34

Though this answer is unlikely to be seen, I think the following clarification is worth making:

the (n) behind an integer data type in MySQL is specifying the display width
the display width does NOT limit the length of the number returned from a query
the display width DOES limit the number of zeroes filled for a zero filled column so the total number matches the display width (so long as the actual number does not exceed the display width, in which case the number is shown as is)
the display width is also meant as a useful tool for developers to know what length the value should be padded to

A BIT OF DETAIL
the display width is, apparently, intended to provide some metadata about how many zeros to display in a zero filled number.
It does NOT actually limit the length of a number returned from a query if that number goes above the display width specified.
To know what length/width is actually allowed for an integer data type in MySQL see the list & link: (types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT);
So having said the above, you can expect the display width to have no affect on the results from a standard query, unless the columns are specified as ZEROFILL columns
OR
in the case the data is being pulled into an application & that application is collecting the display width to use for some other sort of padding.

Primary Reference: https://blogs.oracle.com/jsmyth/entry/what_does_the_11_mean

Thank you for providing the proper answer. I was trying from the MySQL command-line, and I couldn't get it to lop any digits off with small `n`, so...yeah, that's bullcrap. It only does anything with zerofill. — mpen, Feb 25 '16 at 17:33
@mpen glad I could help, this was related to something I was trying and figured I could put more details about it here that might be useful to someone else. — MER, Feb 26 '16 at 20:56

score 0 · Answer 9 · answered Oct 14 '18 at 19:41

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).

score -6 · Answer 10 · answered Jan 15 '15 at 03:58

-6

I think max value of int(11) is 4294967295

answered Jan 15 '15 at 03:58

Samuel

49
1
6

2

It is 2147483647 because it has sign. – d3bit Mar 06 '15 at 13:28

score -12 · Answer 11 · answered Mar 05 '15 at 03:06

-12

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

answered Mar 05 '15 at 03:06

Dudee

1

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

11 Answers11

Linked

Related