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

问题描述

在罗斯文起动工具包，从数据库主键映射到字符串在C＃。

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

这是很好的做法？如果是这样，为什么？

Is this good practice? And if so, why?

THX，列文Cardoen

thx, Lieven Cardoen

PS：对不起，也许错误的问题...

ps: Sorry for the maybe wrong question...

在罗斯文起动工具包的一些表有int类型的一个自动增量主键和其他与数据类型NCHAR非自动增量的主键（5）。为什么是这样？ 好了，显然有些主键只是codeS（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主键简单地归结为在机器code级整型的比较更好的支持。字符串使用在数据库级别上实现的算法进行比较。除非你的字符串是很短，一个Integer键，将在页面上更少的空间，以及（分贝页）。

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.

更新：列文现在已经澄清了他的请求，说他是，实际上，问一个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.

要添加到我的回答，列文，几乎总是最好有一个Int作为您PK。唯一的例外是当有可以被捕捉为短字符串（例如，在会计制度，其中项目条目字符的字符串）一个的自然的关键。原因有三。

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.

首先，整数被重新presented作为本机机器类型（32或64位字），并通过机本地操作处理，而字符串不是，但必须使用一个char逐字符的方法进行比较。因此，例如，遍历的PK指数（通常是一些一B树的变体）来定位的记录时，在各节点的比较操作是一个单一的操作。这是一个巨大的东西？大概不会，除非你是一个真正庞大的数据库或事务负载工作。如果你有一个自然的字符键，然后，通过各种手段，使用它！但是，如果你的钥匙是最后的名称加上首字母加一个数字使其具有唯一性的前五个字母，那么你显然会好得多用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.

二，整数只需占用较少的空间比几乎任何字符键（除CHAR（1）假设采用UNI code的）。而且它不只是在主表页面房间，请记住，索引字段重新在指数psented以及$ P $。再次，这是一个大问题？不是真的，除非你是，一次，一个庞大的数据库工作。

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和煤焦之间的选择，而两种本质上是任意的，去与诠释过字符。

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＃映射 - 字符串或者int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！