主键应该总是unsigned？ [英] primary key should always be unsigned?

问题描述

因为主键（标识符）不会低于0，我想应该始终是无符号的。

since a primary key (identifier) wont be under 0, i guess it should always be unsigned?

推荐答案

一个可选的 SERIAL 数据类型（假定与PostgreSQL兼容，因为 SERIAL 不是标准的ANSI SQL）。此数据类型只是创建 BIGINT UNSIGNED 的简写。

MySQL supports an optional SERIAL data type (presumably for compatibility with PostgreSQL, since SERIAL is not standard ANSI SQL). This data type is just shorthand that creates a BIGINT UNSIGNED.

继续尝试：

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 

INT 和2 ⁶⁴ 声明一个有符号整数或无符号整数， / sup>为 BIGINT 。如果数字是无符号的，则从0到该最大值减1。如果数字已签名，您将从 -max / 2 到 max / 2-1 中获取值。

You get the same number of distinct values whether you declare an integer signed or unsigned: 2³² for an INT and 2⁶⁴ for 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/2 to max/2-1. Either way, you get the same absolute number of distinct values.

但是因为 AUTO_INCREMENT 默认情况下从零开始，

But since AUTO_INCREMENT starts at zero by default and increments in the positive direction, it's more convenient to utilize the positive values than the negative values.

但是，你得到2倍的正值是很重要的。超过最大有符号整数值2 ³¹ -1的任何表都可能继续增长，因此您应该使用 BIGINT 表。

But it hardly matters that you get 2X as many positive values. Any table that would exceed the maximum signed integer value 2³¹-1 is likely to continue to grow, so you should just use a BIGINT for these tables.

您真的真的真的不太可能分配超过2个 ⁶³ -1主键值，即使您删除所有行并每天重新加载它们很多次。

You're really, really, really unlikely to allocate more than 2⁶³-1 primary key values, even if you delete all your rows and re-load them many times a day.

这篇关于主键应该总是unsigned？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！