在 MySQL 中签名或未签名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/478235/
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
Signed or unsigned in MySQL
提问by Riho
I wonder is there any positive effect in using UNSIGNED flag on defining some integer field in MySQL? Does it make queries faster or database smaller? Or should I only bother with it if I'm concerned about upper limit?
我想知道在 MySQL 中定义一些整数字段时使用 UNSIGNED 标志有什么积极影响吗？它使查询速度更快还是数据库更小？或者如果我担心上限，我应该只关心它吗？
回答by Kevin Loney
According to section 10.2of the MySQL 5.1 Manual:
根据MySQL 5.1 手册的10.2 节：
In non-strict mode, when an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a TINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.
在非严格模式下，当超出范围的值分配给整数列时，MySQL 存储表示列数据类型范围的相应端点的值。如果将 256 存储到 TINYINT 或 TINYINT UNSIGNED 列中，MySQL 将分别存储 127 或 255。当为浮点或定点列分配的值超出指定（或默认）精度和小数位数所隐含的范围时，MySQL 存储表示该范围对应端点的值。
So using UNSIGNED is really only necessary when you are concerned about the upper bound. Also adding UNSIGNED does not affect the size of the column just how the number is represented.
因此，只有当您关心上限时才真正需要使用 UNSIGNED。此外，添加 UNSIGNED 不会影响列的大小，只是数字的表示方式。
回答by GeoffreyF67
It doesn't matter unless you are trying to get the most bang for your buck out of the values and don't need negative values.
除非您试图从这些值中获得最大的收益并且不需要负值，否则这并不重要。
For instance, let's say you wanted to store 0-255.
例如，假设您想存储 0-255。
You could use a tinyint but only if you use it as unsigned.
您可以使用 tinyint，但前提是您将其用作无符号。
Lots of the databases I've seen, people don't bother optimizing like this and end up with some rather large tables because they just use INTs all the time.
我见过的很多数据库，人们不会像这样费心优化，最终会得到一些相当大的表，因为他们一直都在使用 INT。
Still, if you're talking about int vs unsigned int, there is no performance affect or space effect at all.
尽管如此，如果您在谈论 int 与 unsigned int，则根本没有性能影响或空间影响。
From a standards standpoint, I always use unsigned and only use signed when I know I will need negative values.
从标准的角度来看，我总是使用无符号，只有在我知道需要负值时才使用有符号。
回答by Andrei Iarus
When it comes to performance or storage, it is absolutely the same thing.
在性能或存储方面，这绝对是一回事。
As a GENERAL RULE, use whichever fits better for you: if you will need only positive values, store values as UNSIGNED, otherwise, let it be the default [SIGNED].
作为一般规则，使用更适合您的那个：如果您只需要正值，将值存储为 UNSIGNED，否则，让它成为默认的 [SIGNED]。
One problem arises when a SIGNED value is set for a PRIMARY AUTOINCREMENT column: the counting of the auto-generated numbers begin with 1 (not the smallest negative number) and the possible values will end earlier, as you will use only a half of values. So in this case (PRIMARY + AUTOINCREMENT column) it is better to store as UNSIGNED.
为 PRIMARY AUTOINCREMENT 列设置 SIGNED 值时会出现一个问题：自动生成的数字的计数从 1（不是最小的负数）开始，并且可能的值将提前结束，因为您将只使用一半的值. 所以在这种情况下（PRIMARY + AUTOINCREMENT 列）最好存储为 UNSIGNED。
回答by ???u
Use unsigned when the column is only meant to contain positive numbers.
当列仅包含正数时使用无符号。
It will not affect any I/O performance on the column, as it will still take up exactly the same amount of space.
它不会影响列上的任何 I/O 性能，因为它仍将占用完全相同的空间量。
回答by user1646191
It will improve the peroformance, lets suppose if you want to search for quantity < 50o.
它将提高性能，假设您要搜索数量 < 50o。
Without “unsigned”: Process flow, since the quantity field is an “int” and you have an index of this field, MySQL will define the range as -2147483648 to 500 and it will get the result based on this range.
不带“unsigned”：流程，因为quantity字段是一个“int”并且你有这个字段的索引，MySQL会定义范围为-2147483648到500，它会根据这个范围得到结果。
With “unsigned”: Process flow, since the quantity field is an “int” with “unsigned” and you have an index of this field, MySQL will define the range as 0 to 500 and it will get the result based on this range.
With “unsigned”：流程，由于quantity字段是一个“int”加“unsigned”，并且你有这个字段的索引，MySQL会定义范围为0到500，并根据这个范围得到结果。
回答by zmonteca
Here you can see the MySQL documentationfor the ranges of SIGNED vs. UNSIGNED INTs. You'll quickly notice that the floor for an UNSIGNED INT is always 0, thus it can never be negative.
在这里您可以看到有关 SIGNED 与 UNSIGNED INT 范围的MySQL 文档。您很快就会注意到 UNSIGNED INT 的下限始终为 0，因此它永远不会为负。
Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615
回答by Yorro
Warning, there is an issue with Unsigned Int (UINT) and Entity Framework or ADO.NET. It might involve an integration problem by MySql Connector version 6.
警告，Unsigned Int (UINT) 和实体框架或 ADO.NET 存在问题。它可能涉及 MySql Connector 版本 6 的集成问题。
In my experience, UIntis read as Longby the EF, which could raise some precision issues, since UIntis not Long. It might cause headaches with anyone not familiar with the issue.
根据我的经验，UInt被LongEF解读为，这可能会引起一些精度问题，因为UInt不是Long. 任何不熟悉这个问题的人都可能会头疼。
Other concerns:
其他问题：
