mysql 中 int(11) 列的大小（以字节为单位）是多少？

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

提问by Gaurav

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

int(11)mysql 中列的大小（以字节为单位）是多少？

And Maximum value that can be stored in this columns?

以及可以存储在此列中的最大值？

回答by Matt MacLean

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

INT无论指定什么长度，An始终为 4 个字节。

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

• TINYINT= 1 字节（8 位）
• SMALLINT= 2 字节（16 位）
• MEDIUMINT= 3 字节（24 位）
• INT= 4 字节（32 位）
• BIGINT= 8 个字节（64 位）。

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 ZEROFILLit would display as 0000012345.

长度仅指定使用 mysql 命令行客户端选择数据时要填充的字符数。12345 存储为 12345int(3)仍将显示为 12345，但如果存储int(10)为 12345 仍将显示为 12345，但您可以选择填充前五位数字。例如，如果您添加ZEROFILL它，它将显示为 0000012345。

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

...并且最大值将为 2147483647（有符号）或 4294967295（无符号）

回答by priyabagus

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

INT( x)只会在显示方面有所不同，即以x位显示数字，而不限于 11。您使用 配对它ZEROFILL，它将在前面加上零，直到它与您的长度匹配。

So, for any number of xin INT(x)

因此，对于任何数量的X在INT(x)

• if the stored value has less digits than x, ZEROFILLwill prepend zeros.

  INT(5) ZEROFILLwith the stored value of 32 will show 00032
  INT(5)with the stored value of 32 will show 32
  INTwith 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) ZEROFILLwith the stored value of 250000 will show 250000
  INT(3)with the stored value of 250000 will show 250000
  INTwith the stored value of 250000 will show 250000

• 如果存储值的位数少于x，ZEROFILL则将在前面添加零。

  INT(5) ZEROFILL存储值为 32 将显示00032
  INT(5)存储值为 32 将显示32
  INT存储值为 32 将显示32

• 如果存储的值的位数多于x，它将按原样显示。

  INT(3) ZEROFILL存储值为 250000 将显示250000
  INT(3)存储值为 250000 将显示250000
  INT存储值为 250000 将显示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.

这也适用于 BIGINT、MEDIUMINT、SMALLINT 和 TINYINT。

回答by Santosh Linkha

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

根据here，int(11)将占用4个字节的空间，即32位空间的 2^(31) = 2147483648最大值和-2147483648最小值。一位用于符号。

回答by Programster

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.

很多这些答案都说该(11)部分仅影响显示宽度，这并不完全正确，但主要是。

A definition of int(2)with no zerofill specifiedwill:

的定义int(2)有没有指定ZEROFILL将：

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

• 仍然接受一个值 100
• 仍然显示100输出时的值（不0或00）
• 的显示宽度将从选择查询值被输出最大的宽度。

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

唯一要做的(2)就是如果还指定了 zerofill：

• a value of 1will be shown 01.
• When displaying values, the column will always have a width of the maximum possible value the column could takewhich 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.

• 1将显示的值01。
• 显示值时，该列的宽度将始终是该列可以采用的最大可能值的宽度，即整数的 10 位数字，而不是显示该列需要在该特定选择查询中显示的最大值所需的最小宽度，这可能要小得多。
• 该列仍然可以取，并显示超过长度的值，但这些值不会以 0 为前缀。

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.

此答案针对 Linux 的 MySQL 5.7.12 进行了测试，并且可能会或可能不会因其他实现而异。

回答by ks1322

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

mysql 中 int(11) 列的大小（以字节为单位）是多少？

(11)- this attribute of intdata 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:

(11)- 该int数据类型属性与列的大小无关。它只是整数数据类型的显示宽度。从11.1.4.5. 数字类型属性：

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.

MySQL 支持一个扩展，用于在类型的 base 关键字后面的括号中选择性地指定整数数据类型的显示宽度。例如，INT(4) 指定显示宽度为四位的 INT。

回答by shashi009

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

对此的一个很好的解释可以在这里找到

总结：int(N) 中的数字 N 经常被列允许的最大大小混淆，就像在 varchar(N) 的情况下一样。

但这不是整数数据类型的情况 -括号中的数字 N 不是列的最大大小，而只是一个参数，用于告诉 MySQL 在通过 MySQL 查看表数据时显示列的宽度控制台（当您使用 ZEROFILL 属性时）。

括号中的数字将告诉 MySQL 用多少个零来填充传入的整数。例如：如果您在设置为 INT(5) 的列上使用 ZEROFILL 并插入数字 78，MySQL 将用零填充该值，直到该数字满足括号中的数字。即78会变成00078，127会变成00127。总结一下：括号里的数字是用来显示的。
在某种程度上，除非您使用 ZEROFILL 属性，否则括号中的数字是无用的。

所以 int 的大小将保持不变，即-2147483648 到 2147483648 表示有符号， 0 到 4294967295 表示无符号（~ 21.5 亿和 42 亿，这是开发人员仍然不知道括号中数字 N 背后的故事的原因之一，因为它几乎不会影响数据库，除非它包含超过 20 亿行），并且就字节而言将是4 个字节。

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

有关整数类型大小/范围的更多信息，请参阅MySQL 手册

回答by MER

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

• MySQL 中整数数据类型后面的 (n) 是指定显示宽度
• 显示宽度不限制从查询返回的数字的长度
• 显示宽度确实限制了填充零的列的填充数量，因此总数与显示宽度匹配（只要实际数字不超过显示宽度，在这种情况下，数字按原样显示）
• 显示宽度也是开发人员知道值应该填充到什么长度的有用工具

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.

一点细节
显然，显示宽度旨在提供一些关于在零填充数字中显示多少个零的元数据。
如果该数字超过指定的显示宽度，它实际上不会限制从查询返回的数字的长度。
要了解 MySQL 中整数数据类型实际允许的长度/宽度，请参阅列表和链接：（类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）；
综上所述，您可以期望显示宽度对标准查询的结果没有影响，除非将列指定为 ZEROFILL 列，
或者
在数据被拉入应用程序并且该应用程序正在收集数据的情况下用于其他类型填充的显示宽度。

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

主要参考：https: //blogs.oracle.com/jsmyth/entry/what_does_the_11_mean

回答by Sachin Agrawal

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

在 MySQL 中整数的int(11)大小为 4 字节，等于 32 位。

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

签名值为： - 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

无符号值是：0 to 2^32-1= 0 to 4294967295

回答by Alireza Rahmani Khalili

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

MySQL 允许您为整数类型指定“宽度”，例如 INT(11)。这对大多数应用程序来说毫无意义：它不限制值的合法范围，而只是指定 MySQL 的交互式工具将为显示目的保留的字符数。出于存储和计算目的，INT(1) 与 INT(20) 相同。

回答by Samuel

I think max value of int(11) is 4294967295

我认为 int(11) 的最大值是 4294967295