数据库主键 C# 映射 - String 或 int [英] Database Primary Key C# mapping - String or int

问题描述

在 Northwind Starters Kit 中，数据库中的主键被映射到 C# 中的字符串.

In the Northwind Starters Kit, Primary keys from database are mapped to Strings in C#.

这是好的做法吗?如果是这样，为什么?

Is this good practice? And if so, why?

谢谢，利文·卡多恩

ps:对不起，可能是错误的问题...

ps: Sorry for the maybe wrong question...

在 Northwind Starters Kit 中，一些表具有数据类型为 int 的自动增量主键，而其他表具有数据类型为 nchar(5) 的非自动增量主键.为什么是这样?好吧，显然有些主键只是代码(nchar(5) 格式).很抱歉占用了您的时间.

In Northwind Starters Kit some tables have a auto-incremental primary key with datatype int and others have a non auto-incremental primary key with datatype nchar(5). Why is this? Well, apparently some primary keys are just codes (nchar(5) format). So sorry to have used your time.

我认为数据类型 int 被映射到 C# 字符串，这对我来说似乎很错误(但事实并非如此).

I thought that a datatype int was mapped to C# string which seemed very wrong to me (but it isn't the case).

推荐答案

为了提高效率，使用 Int 作为主键更好，因为它支持机器代码级别的 Int 比较.使用在数据库级别实现的算法比较字符串.除非你的字符串很短，否则整数键在页面上占用的空间也会更少(db 页面).

For pure efficiency, using an Int as your primary key is better simply due to the support for comparison of Ints at the machine code level. Strings are compared using algorithms implemented at the database level. Unless your strings are very short, an Integer key will take up less space on the page as well (db page).

更新:根据黑板上的其他答案，我不确定我是否正确理解了您的问题.您是在问与字符串(可以选择任何一个)相比，使用整数作为键是否更好?或者你在问你的 C# 类型是否应该匹配你的数据库类型?我假设是前者……如果是后者我会很惊讶——我认为他的答案是显而易见的.

Update: Based on the other answer now on the board, I'm not sure if I've understood your question correctly. Are you asking whether it is better to use an Integer as your key compared to a string (where either could be chosen)? Or are you asking whether your C# type should match your database type? I'm assuming the former...and would be very surprised if it is the latter - whose answer I would think is obvious.

更新:Lieven 现在澄清了他的要求，说他实际上是在问 Int 字段还是 nchar 字段作为索引更好，所以我对这个问题的最初看法是正确的.

Update: Lieven has now clarified his request to say that he was, in fact, asking whether an Int or an nchar field would be better as an index so my original take on this question was correct.

为了补充我的答案，Lieven，将 Int 作为 PK 几乎总是更好.例外情况是当有一个 natural 键可以捕获为短字符串时(例如，在项目"条目是字符字符串的会计系统中).原因有三个.

To add to my answer, Lieven, it is almost always better to have an Int as your PK. The exception is when there is a natural key that can be captured as a short character string (e.g. in an accounting system where "Item" entries are char strings). The reasons are threefold.

首先，整数表示为本地机器类型(32 位或 64 位字)并通过机器本地操作进行操作，而字符串不是，但必须使用逐字符方法进行比较.因此，例如，当遍历 PK 索引(通常是 BTree 的某些变体)以定位记录时，每个节点的比较操作是单个操作.这是一件大事吗?除非您正在处理真正庞大的数据库或事务负载，否则可能不会.如果您有一个自然字符键，那么一定要使用它！但是，如果您的键"是姓氏的前五个字母加上第一个首字母加上一个数字以使其唯一，那么使用 Int 字段显然会好得多.

First, Integers are represented as a native machine type (32 or 64-bit word) and manipulated via machine-native operations whereas strings are not but must be compared using a char-by-char approach. So, for example, when traversing the PK Index (usually some variant of a BTree) to locate a record, the comparison operation at each node is a single operation. Is this a huge thing? Probably not unless you are working with a truly massive database or transaction load. If you have a natural character key then, by all means, use it! However, if your "key" is the first five letters of the last name plus the first initial plus a number to make it unique, then you'd obviously be far better off with an Int field.

其次，整数比几乎任何字符键占用的空间都小(假设使用 Unicode 的 char(1) 除外).并且它不仅仅是主表页面中的房间，请记住索引字段也在索引中表示.再说一遍，这有什么大不了的吗?不是真的，除非您再次使用大型数据库.

Second, Integers simply take up less room than almost any char key (except char(1) assuming the use of Unicode). And it isn't just the room in the main table page, remember that the index fields are represented in the Index as well. Again, is this a big deal? Not really, unless you are, again, working with a massive database.

最后，我们对键的选择通常会对其他地方产生影响.因此，例如，如果您将一个表的主键用作另一个表的外键，那么当您使用外键在表中插入或更新记录时，上述两种效果都会被放大.

Lastly, our choice of keys often has effects elsewhere. So, for example, if you use the primary key on one table as the foreign key on another, both of the above effects are magnified when you are inserting or updating records in the table using the foreign key.

总而言之:使用最自然的键.但是，如果您可以在 Int 和 Char 之间进行选择，并且两者本质上都是任意的，请使用 Int 而不是 Char.

To sum: use the key that is most natural. However, if you have a choice between Int and Char and both are essentially arbitrary, go with the Int over the Char.

这篇关于数据库主键 C# 映射 - String 或 int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！