MySQL 我的sql中“int”和“int(2)”数据类型之间的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5562322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between "int" and "int(2)" data types in my sql
提问by A.C.Balaji
Just I wonder why the range is given with the my sql data types. I define a table with a field name "id" and the data type is "int(2)". I inserted the value to the field "id" as "123456".This is accepted and stored. So what is the use of giving the range.
只是我想知道为什么我的 sql 数据类型给出了范围。我定义了一个字段名称为“id”且数据类型为“int(2)”的表。我将值插入到字段“id”中为“123456”。这是接受并存储的。那么给出范围有什么用。
Thanks in advance.
提前致谢。
回答by WhiteFang34
For INTand other numeric types that attribute only specifies the display width.
对于INT和其他数字类型，该属性仅指定显示宽度。
See Numeric Type Attributesin the MySQL documentation:
请参阅MySQL 文档中的数字类型属性：
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. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.
MySQL 支持一个扩展，用于在类型的 base 关键字后面的括号中选择性地指定整数数据类型的显示宽度。例如，INT(4) 指定显示宽度为四位的 INT。应用程序可以使用此可选的显示宽度来显示宽度小于为列指定的宽度的整数值，方法是用空格向左填充它们。（也就是说，这个宽度存在于结果集返回的元数据中。是否使用它取决于应用程序。）
显示宽度不限制可以存储在列中的值的范围。它也不会阻止比列显示宽度更宽的值正确显示。例如，指定为 SMALLINT(3) 的列通常具有 -32768 到 32767 的 SMALLINT 范围，超出三位数字允许范围的值使用三位以上数字完整显示。
回答by Jon Black
The optional display width specifier (for integer data types) is only applicable when using zerofilland has nothing to do with the internal size (in bytes) of the integer data type.
可选的显示宽度说明符（用于整数数据类型）仅在使用zerofill时适用，与整数数据类型的内部大小（以字节为单位）无关。
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
drop table if exists foo;
create table foo
(
bar smallint(4) unsigned zerofill not null default 0
)engine=innodb;
insert into foo (bar) values (781),(727);
select * from foo;
+-----------+
| bar |
+-----------+
| 0781 |
| 0727 |
+-----------+
More importantly, what you should be thinking about is whether your integer data types should be signed or unsigned e.g.
更重要的是，您应该考虑的是您的整数数据类型应该是有符号的还是无符号的，例如
create table users
(
user_id int not null auto_increment primary key, -- -2147483648 to 2147483647
...
)engine=innodb;
vs.
对比
create table users
(
user_id int unsigned not null auto_increment primary key, -- 0 to 4294967295
...
)engine=innodb;
So, which is it to be - unsigned or signed ??
那么，哪个是 - 未签名或签名？
Hope this helps :)
希望这可以帮助 ：）
回答by Ciaran Keating
The (2) doesn't define the size of the integer. It's just number of digits to display in some tools - I'm not sure of the details. The size of an integer is determined by whether it's INT, TINYINT, SMALLINT, MEDIUMINT, or BIGINT.
(2) 没有定义整数的大小。这只是在某些工具中显示的位数 - 我不确定细节。整数的大小取决于它是 INT、TINYINT、SMALLINT、MEDIUMINT 还是 BIGINT。
回答by Sergey Podgornyy
As far as I know, when you define INT(2)(where maximum value is 4), you are allocating in memory 2 bits, but if you insert value, which is bigger, then 4, MySQL will request more memory from system.
据我所知，当您定义时INT(2)（最大值为 4），您在内存中分配了 2 位，但是如果您插入更大的值，那么 4，MySQL 将向系统请求更多内存。
So INT(2)means allocate at least 2 bits for storing this value, if value bigger, then you specified (INT(2)), DBMS will request memory ones again to request this memory. This is no good for performance reason to request memory twice, that's why better to allocate for column more space.
所以INT(2)意味着至少分配2位来存储这个值，如果值更大，那么你指定了（INT(2)），DBMS将再次请求内存来请求这个内存。出于性能原因，两次请求内存是不利的，这就是为什么最好为列分配更多空间的原因。
