主键应该总是无符号的?[英] primary key should always be unsigned?

本文是小编为大家收集整理的关于主键应该总是无符号的?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

由于主键(标识符)不会低于 0,我猜它应该始终是无符号的?

推荐答案

MySQL 支持可选的 SERIAL 数据类型(大概是为了与 PostgreSQL 兼容,因为 SERIAL 不是标准的 ANSI SQL).这种数据类型只是创建 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 为 232,BIGINT 为 264.如果该数字是无符号的,您将获得从 0 到该最大值减一的值.如果数字是有符号的,您将获得从 -max/2 到 max/2-1 的值.无论哪种方式,您都会获得相同的绝对数量的不同值.

但由于 AUTO_INCREMENT 默认从零开始,并沿正方向递增,因此使用正值比使用负值更方便.

但你得到 2 倍的正值并不重要.任何超过最大有符号整数值 231-1 的表都可能继续增长,因此您应该对这些表使用 BIGINT.

你真的,真的,真的不太可能分配超过 263-1 个主键值,即使你删除所有行并重新加载它们一天很多次.

本文地址:https://www.itbaoku.cn/post/597525.html

问题描述

since a primary key (identifier) wont be under 0, i guess it should always be 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.

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: 232 for an INT and 264 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.

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.

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 BIGINT for these tables.

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