ID列推荐使用什么数据类型？ [英] What data type is recommended for ID columns?

问题描述

我意识到这个问题很可能已经被问过了，但是我已经在StackOverflow的一些问题中搜索过，而且我没有真正找到我的答案，所以这里。如果你找到一个重复的，请链接到它。

由于某些原因，我更喜欢使用 Guid s（ uniqueidentifier 在MsSql中）对于我的主键字段，但我真的不知道为什么会更好。在很多教程中，我最近都自动执行了一个自动递增的 int 。我可以看到亲和的两个：

For some reason I prefer to use Guids (uniqueidentifier in MsSql) for my primary key fields, but I really don't know why this would be better. In many of tutorials I've walked myself through lately an automatically incremented int has been used. I can see pro's and cons with both:

• A Guid 始终是相同的大小和长度，没有理由担心用尽它们，而在你用完一个 int 。


• int 是（至少在C＃中）一个可空类型，打开几个快捷方式查询数据时。


• 而 int 更容易阅读。


• 我敢打赌可以在这里找到至少几件事情。

• A Guid is always of the same size and length, and there is no reason to worry about running out of them, whereas there is a limit to how many records you could have before you'd run out of numbers that fit in an int.
• int is (at least in C#) a nullable type, which opens for a couple of shortcuts when querying for data.
• And int is easier to read.
• I bet you could come up with at least a couple of more things here.

所以，简单的标题说：什么是数据库中ID（主键）列的推荐数据类型吗？

So, as simple as the title says it: What is the recommended data type for ID (primary key) columns in a database?

编辑：收到几个简短的答案后，我还必须添加后续问题没有它，你的答案既不引人注目也不是教育...;）你为什么这么认为，而另一个选项的缺点是什么，而不是选择呢？ strong>

After recieving a couple of short answer, I must also add this follow-up question. Without it, your answer is neither compelling nor educating... ;) Why do you think so, and what are the cons of the other option that make you not choose that instead?

推荐答案

任何整数类型的足够的大小来存储预期的数据范围。通常，对于具有大量行或更改的表，通常将32位整数视为太小（正确或错误）。 64位int很多。许多数据库将不会使用该类型，也不会使用该类型，但将使用具有指定比例和精度的NUMBER类型。 10-15个数字是一个相当常见的大小。

Any integer type of sufficient size to store anticipated data ranges. Generally 32 bit ints are viewed as too small (rightly or wrongly) for tables with a lot of rows or changes. A 64 bit int is plenty. Many databases won't have or won't use that integer type but will use a NUMBER type with specified scale and precision. 10-15 digits is a fairly common size.

选择整数类型的原因有两个：

The reason for choosing integer types is twofold:

1. 尺寸;和


2. 速度。

整数的大小是：

• 32位：4字节;


• 64位：8字节;


• 二进制编码十进制：每字节两位数加上符号，比例和/或精度的字节数。

GUID ，它是128位或正常字符串，每个字符至少有一个字节（更多在某些字符编码中）加上可能只有一个字节（终止为零）的开销，或者在某些情况下可能会更多。

Compare that to a GUID, which is 128 bits or a normal string, which is at least one byte per character (more in certain character encodings) plus an overhead that might be as little as one byte (terminating null) or could be much more in some cases.

排序整数是微不足道的，假设它们是唯一的，并且范围足够小，实际上可以在O（n）时间内完成，最好与O（n log n）相比。

Sorting integers is trivial and, assuming they are unique and the range is sufficiently small, can actually be done in O(n) time, compared to, at best, O(n log n).

同样重要的是，大多数数据库可以通过自动增量列和/或序列生成唯一的ID。保证应用程序中的唯一性其实实际上相当困难，并且往往会导致膨胀的密钥。

also, just as importantly, most databases can generate unique IDs by means of auto-increment columns and/or sequences. Guaranteeing uniqueness in an application is otherwise actually quite hard and tends to result in bloated keys.

加自动生成的整数密钥通常是松散或绝对排序（取决于数据库和配置），这是一个有用的质量。随机生成的GUID基本上是无序的，这远远不太有用。

Plus auto-generated integer keys are typically either loosely or absolutely ordered (depending on database and configuration), which is a useful quality. Randomly generated GUIDs are basically unordered, which is far less useful.

这篇关于ID列推荐使用什么数据类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！