MySQL 主键应该总是无符号的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2686813/
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
primary key should always be unsigned?
提问by never_had_a_name
since a primary key (identifier) wont be under 0, i guess it should always be unsigned?
由于主键（标识符）不会低于 0，我想它应该始终是未签名的？
回答by Bill Karwin
TL/DR: Yes, but it almost doesn't matter.
TL/DR：是的，但这几乎无关紧要。
Auto-increment always increases, so it will never use negative values. You might as well make it unsigned, and you get twice the range of values.
自动增量总是增加，所以它永远不会使用负值。您不妨将其设为无符号，这样您将获得两倍的值范围。
On the other hand, if your table uses 231values, it will probably also use 232values in a short time, so having twice the range of values isn't a big difference. You will have to upgrade to BIGINT anyway.
另一方面，如果您的表使用 2 31 个值，它可能也会在短时间内使用 2 32 个值，因此具有两倍的值范围并没有太大区别。无论如何，您都必须升级到 BIGINT。
MySQL supports an optional SERIALdata type (presumably for compatibility with PostgreSQL, since SERIALis not standard ANSI SQL). This data type is just shorthand that creates a BIGINT UNSIGNED.
MySQL 支持可选的SERIAL数据类型（大概是为了与 PostgreSQL 兼容，因为SERIAL它不是标准的 ANSI SQL）。此数据类型只是创建BIGINT UNSIGNED.
Go ahead try it:
来试试吧：
CREATE TABLE test.foo (foo_id SERIAL PRIMARY KEY);
SHOW CREATE TABLE test.foo;
CREATE TABLE `test`.`foo` (
`foo_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`foo_id`),
UNIQUE KEY `foo_id` (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
You get the same number of distinct values whether you declare an integer signed or unsigned: 232for an INTand 264for a BIGINT. If the number is unsigned, you get values from 0 to that max value minus one. If the number is signed, you get values from -max/2to max/2-1. Either way, you get the same absolute number of distinct values.
无论您声明一个有符号整数还是无符号整数，您都会得到相同数量的不同值： 2 32表示 an INT， 2 64表示 a BIGINT。如果数字是无符号的，您将获得从 0 到最大值减 1 的值。如果数字是有符号的，您将获得来自-max/2to 的值max/2-1。无论哪种方式，您都会获得相同绝对数量的不同值。
But since AUTO_INCREMENTstarts at zero by default and increments in the positive direction, it's more convenient to utilize the positive values than the negative values.
但由于AUTO_INCREMENT默认情况下从零开始并沿正方向递增，因此使用正值比使用负值更方便。
But it hardly matters that you get 2X as many positive values. Any table that would exceed the maximum signed integer value 231-1 is likely to continue to grow, so you should just use a BIGINTfor these tables.
但是，获得 2 倍的正值并不重要。任何超过最大有符号整数值 2 31-1 的表都可能继续增长，因此您应该只BIGINT对这些表使用 a 。
You're really, really, reallyunlikely to allocate more than 263-1 primary key values, even if you delete all your rows and re-load them many times a day.
您真的，真的，真的不太可能分配超过 2 63-1 个主键值，即使您删除所有行并每天多次重新加载它们。
回答by JohnFx
Why exactly are you presuming that a primary key won't be under 0? That is not a given. I think you are confusing it with an identity column.
为什么您确切地假设主键不会低于 0？那不是给定的。我认为您将它与身份列混淆了。
In any case it should make no appreciable difference either way, map the data type to the type of data you expect in the column regardless of whether it is a primary key or not.
在任何情况下，无论哪种方式都应该没有明显的区别，将数据类型映射到列中您期望的数据类型，无论它是否是主键。
回答by Jon Black
NO - a primary key wont always be unsigned for example:
NO - 主键不会总是无符号的，例如：
create table user_status
(
status_id tinyint not null primary key,
name varchar(64) not null,
msg varchar(255) default null
)engine=innodb;
insert into user_status values
(-99,'banned', 'Account banned'),
(-2,'closed', 'Account closed'),
(-1,'unverified', 'Account not verified'),
(0,'suspended','Account suspended'),
(1,'active', null);
if this was an orders table however i'd use order_id int unsigned
如果这是一个订单表，但是我会使用 order_id int unsigned
回答by Courtney Miles
since a primary key (identifier) wont be under 0
因为主键（标识符）不会低于 0
I assume from that statement that your primary key also auto-increments.
我从该声明中假设您的主键也会自动递增。
If it does, then is is very sensible to ensure your column is unsigned, for the reason described in the MySQL manual:
如果是这样，那么确保您的列未签名是非常明智的，原因在MySQL 手册中描述：
An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.
AUTO_INCREMENT 列只有在它只包含正值时才能正常工作。插入一个负数被认为是插入一个非常大的正数。这样做是为了避免在数字从正数“换行”到负数时出现精度问题，并确保您不会意外获得包含 0 的 AUTO_INCREMENT 列。
If you are inserting negative numbers into an auto-incrementing column, then you are probably solving a problem the wrong way.
如果您在自动递增的列中插入负数，那么您可能以错误的方式解决问题。
