使用GUID和自动递增整数 [英] Using both a GUID and an auto-incrementing integer

问题描述

我一直在研究使用GUID作为数据库中的主键。到目前为止，职业球员似乎超过了利弊。但是，我看到一个点，其中GUID可能不是我想要的。

I've been investigating the use of GUIDs as primary keys in databases. So far, the pros seem to outweigh the cons. However, I see one point where GUIDs may not be what I want.

在我的应用程序中，用户应该能够基于用户友好的ID识别对象。因此，例如，如果他们想要获取特定的产品而不输入全名，他们可以使用产品的ID。 GUID不容易记住这样的东西。

In my application, users should be able to identify objects based on a user-friendly ID. So, for example, if they want to get a specific product without typing in the full name, they can use the product's ID. GUIDs aren't easy to remember for something like that.

我一直在想的解决方案是使用GUID和自动递增的整数。 GUID将是行的主键，而自动递增整数将是应用程序的过滤函数使用的索引。然而，所有SQL SELECT，UPDATE，DELETE语句都将使用GUID。

The solution I've been thinking about is to use both a GUID and an auto-incrementing integer. The GUID would be the row's primary key, while the auto-incrementing integer would be an index used by the application's filtering functions. All SQL SELECT, UPDATE, DELETE statements would use the GUID, however.

我想使用GUID的主要原因是防止在合并两个数据库时发生冲突。如果数据库＃1和数据库＃2都有产品＃2，则导入器脚本必须更改ID和引用它的所有外键。使用GUID，我只需要更改表格中的用户友好ID，而外键将使用每个导入记录唯一的GUID，因此可以无修改地工作。

The main reason I want to use GUIDs is to prevent clashes when merging two databases. If Database #1 and Database #2 both have a Product #2, the importer script would have to change the ID and all foreign keys referring to it. With GUIDs, I only have to change the user-friendly ID in the table itself, while foreign keys would use the GUID unique to each imported record and will therefore work without modification.

所以，我的问题是：有任何主要的问题（除了GUID字段的大小和容易的页面碎片）与自动递增整数索引和GUID主键？

So, my question is: are there any major problems (besides the size of the GUID field and easy page fragmentation) with having an auto-incrementing integer index and a GUID primary key?

推荐答案

我总是倾向于在我的数据库中使用代理主键。
即：这些主键在问题域中没有实际意义，因此，这些主键从不暴露给用户。
（如果此代理主键的类型为GUID或身份，我不在乎;这取决于要求）。

I always tend to use surrogate primary keys in my database. That is: those primary keys have no actual meaning in the problem domain, and thus, those primary keys are never exposed to users. (If this surrogate primary key is of type GUID or an identity, I don't care; this depends on the requirements).

如果你说用户应该能够基于用户友好的ID识别对象，然后，我认为这个用户友好的ID是属于你的问题域的值。
这意味着，这个ID应该确实是你的表中的一个属性，但它不应该用作你的表中的主键。

If you say that users should be able to identify objects based on a user-friendly ID, then, I think that this user-friendly ID is a value that belongs to your 'problem domain'. This means, that this ID should indeed be an attribute in your table, but it should not be used as the primary key in your table.

允许您轻松修改这种用户友好的ID的值（如果这是必要的），而不必担心修改相关的外键。

This also allows you to easily modify the value of such an user-friendly ID (if that should be necessary), without you having to worry about modifying related foreign keys as well.

这篇关于使用GUID和自动递增整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！