什么时候在MySQL中使用无符号和有符号INT ?

When should I use UNSIGNED and SIGNED INT in MySQL ? What is better to use or this is just personal prefernce ? Because I've seen it used like this;

什么时候在MySQL中使用无符号和有符号INT ?用什么更好，或者这只是个人偏好?因为我看到过它是这样使用的;

id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT

and

和

id INT(11) NOT NULL AUTO_INCREMENT

7 个解决方案

#1

108

UNSIGNED only stores positive numbers (or zero). On the other hand, signed can store negative numbers (i.e., may have a negative sign).

无符号只存储正数(或零)。另一方面，符号可以存储负数(例如。)。

Here's a table of the ranges of values each INTEGER type can store:

下面是每个整数类型可以存储的值范围的表:

什么时候在MySQL中使用无符号和有符号INT ?
^{Source: http://dev.mysql.com/doc/refman/5.6/en/integer-types.html}

来源:http://dev.mysql.com/doc/refman/5.6/en/integer-types.html

UNSIGNED ranges from 0 to n, while signed ranges from about -n/2 to n/2.

无符号范围从0到n，而有符号范围从-n/2到n/2。

In this case, you have an AUTO_INCREMENT ID column, so you would not have negatives. Thus, use UNSIGNED. If you do not use UNSIGNED for the AUTO_INCREMENT column, your maximum possible value will be half as high (and the negative half of the value range would go unused).

在本例中，您有一个AUTO_INCREMENT ID列，因此不会出现负号。因此,使用无符号。如果您不使用UNSIGNED for AUTO_INCREMENT列，您的最大可能值将是一半高(并且值范围的负值将会被使用)。

#2

Use UNSIGNED for always positive integers.

对总是正整数使用无符号。

#3

Basically with UNSIGNED, you're giving yourself twice as much space for the integer since you explicitly specify you don't need negative numbers (usually because values you store will never be negative).

基本上，对于无符号的整数，您为自己提供了两倍的空间，因为您显式地指定了不需要负数(通常是因为存储的值永远不会是负数)。

#4

I'm not agree with vipin cp.

我不同意vipin cp。

The true is that first bit is used for represent the sign. But 1 is for negative and 0 is for positive values. More over negative values are coded in different way (two's complement). Example with TINYINT:

第一个位用于表示符号。1是负数，0是正值。更多的负数以不同的方式编码(2的补码)。非常小的整数的例子:

The sign bit
|
1000 0000b = -128d  
...  
1111 1101b = -3d  
1111 1110b = -2d  
1111 1111b = -1d  

0000 0000b = 0d  
0000 0001b = 1d  
0000 0010b = 2d  
...  
0111 1111b = 127d

#5

I think, UNSIGNED would be the best option to store something like time_duration(Eg: resolved_call_time = resolved_time(DateTime)-creation_time(DateTime)) value in minutes or hours or seconds format which will definitely be a non-negative number

我认为，UNSIGNED将是存储类似time_duration(例如:resolved_call_time = resolved_time(DateTime)-creation_time(DateTime))的值的最佳选项，以分钟或小时或秒的格式存储，这肯定是非负数

#6

One thing i would like to add In a signed int, which is the default value in mysql , 1 bit will be used to represent sign. -1 for negative and 0 for positive. So if your application insert only positive value it should better specify unsigned.

我想要添加一个符号int，它是mysql的默认值，1位将被用来表示符号。-1代表负，0代表正。因此，如果应用程序只插入正值，那么最好指定unsigned。

#7

If you know the type of numbers you are going to store, your can choose accordingly. In this case your have 'id' which can never be negative. So you can use unsigned int. Range of signed int: -n/2 to +n/2 Range of unsigned int: 0 to n So you have twice the number of positive numbers available. Choose accordingly.

如果你知道你要存储的数字类型，你可以相应地选择。在这种情况下，你的id永远不会是负数。所以你可以使用unsigned int. Range of signed int: -n/2到+n/2的unsigned int: 0到n，所以你有两倍的正数。相应的选择。

#1

108