MySQL VARCHAR 作为数据库中的外键/主键好还是坏？

VARCHAR as foreign key/primary key in database good or bad?

提问by

Is it better if I use ID nr:s instead of VARCHARS as foreign keys? And is it better to use ID nr:s isntead of VARCHARS as Primary Keys? By ID nr I mean INT!

如果我使用 ID nr:s 而不是 VARCHARS 作为外键会更好吗？使用 ID nr:s isstead of VARCHARS 作为主键是否更好？通过 ID nr 我的意思是 INT！

This is what I have now:

这就是我现在所拥有的：

category table:
cat_id ( INT ) (PK)
cat_name (VARCHAR)

category options table:
option_id ( INT ) (PK)
car_id ( INT ) (FK)
option_name ( VARCHAR ) 

I COULD HAVE THIS I THINK:

我可以有这个我认为：

category table:
cat_name (VARCHAR) (PK)

category options table:
cat_name ( VARCHAR ) (FK)
option_name ( VARCHAR ) ( PK )

Or am I thinking completely wrong here?

或者我在这里认为完全错误？

采纳答案by Armstrongest

The problem with VARCHAR being used for any KEY is that they can hold WHITE SPACE. White space consists of ANY non-screen-readable character, like spaces tabs, carriage returns etc. Using a VARCHAR as a key can make your life difficult when you start to hunt down why tables aren't returning records with extra spaces at the end of their keys.

将 VARCHAR 用于任何 KEY 的问题是它们可以容纳空格。空白由任何非屏幕可读字符组成，例如空格制表符、回车符等。当您开始追查为什么表不返回带有额外空格的记录时，使用 VARCHAR 作为键会使您的生活变得困难他们的钥匙。

Sure, you CANuse VARCHAR, but you do have to be very careful with the input and output. They also take up more space and are likely slower when doing a Queries.

当然，你CAN使用VARCHAR，但你必须要非常小心的输入和输出。它们还占用更多空间，并且在执行查询时可能会更慢。

Integer types have a small list of 10 characters that are valid, 0,1,2,3,4,5,6,7,8,9. They are a much better solution to use as keys.

整数类型有一个包含 10 个有效字符的小列表，0,1,2,3,4,5,6,7,8,9。它们是用作密钥的更好的解决方案。

You could always use an integer-based key and use VARCHAR as a UNIQUE value if you wanted to have the advantages of faster lookups.

如果您想获得更快查找的优势，您始终可以使用基于整数的键并使用 VARCHAR 作为 UNIQUE 值。

回答by Bob Jarvis - Reinstate Monica

When I'm doing design work I ask myself: have I got anything in this data that I can guarantee is going to be non-NULL, unique, and unchanging? If so that's a candidate to be the primary key. If not, I know I have to generate a key value to use. Assuming, then, that my candidate key happens to be a VARCHAR I then look at the data. Is it reasonably short in length (meaning, say, 20 characters or less)? Or is the VARCHAR field rather long? If it's short it's usable as a key - if it's long, perhaps it's better to not use it as a key (although if it's in consideration for being the primary key I'm probably going to have to index it anyways). At least part of my concern is that the primary key is going to have to be indexed and will perhaps be used as a foreign key from some other table. Comparisons of VARCHAR fields tend to be slower than the comparison of numeric fields (particularly binary numeric fields such as integers) so using a long VARCHAR field as a key may result in slow performance. YMMV.

当我在做设计工作时，我会问自己：我有没有在这些数据中得到任何可以保证非 NULL、唯一和不变的东西？? 如果是这样，那就是主键的候选者。如果没有，我知道我必须生成一个键值才能使用。假设我的候选键恰好是一个 VARCHAR，然后我查看数据。它的长度是否合理（即 20 个字符或更少）？还是 VARCHAR 字段相当长？如果它很短，它可以用作键 - 如果它很长，也许最好不要将它用作键（尽管如果考虑将其用作主键，我可能无论如何都必须对其进行索引）。至少我担心的部分是主键必须被索引，并且可能会被用作其他表的外键。VARCHAR 字段的比较往往比数字字段（特别是二进制数字字段，如整数）的比较慢，因此使用长 VARCHAR 字段作为键可能会导致性能下降。天啊。

回答by jrvidotti

My 2 cents:

我的 2 美分：

From a performance perspective, using CHAR or VARCHAR as primary key or index is a nightmare.

从性能的角度来看，使用 CHAR 或 VARCHAR 作为主键或索引是一场噩梦。

I've tested compound primary keys (INT + CHAR, INT + VARCHAR, INT + INT) and by far INT + INT was the best performance (loading a data warehouse). Lets say about twice more performance if you keep only numeric primary keys/indexes.

我测试了复合主键（INT + CHAR、INT + VARCHAR、INT + INT），到目前为止 INT + INT 是最好的性能（加载数据仓库）。如果您只保留数字主键/索引，可以说性能提高两倍。

回答by SorcyCat

If you make the category name into the ID you will have a problem if you ever decide to rename a category.

如果您将类别名称放入 ID 中，如果您决定重命名类别，就会遇到问题。

回答by SQLMenace

with an int you can store up to 2 billion in 4 bytes with varchars you cannot you need to have 10 bytes or so to store that, if you use varchars there is also a 2 byte overhead

使用 int，您可以使用 varchars 在 4 个字节中存储多达 20 亿个数据，您不需要有 10 个左右的字节来存储它，如果您使用 varchars，那么还有 2 个字节的开销

so now you add up the 6 extra bytes in every PK and FK + the 2 byte varchar overhead

所以现在你将每个 PK 和 FK 中的 6 个额外字节加起来 + 2 个字节的 varchar 开销

回答by Adriaan Stander

I would say it is fine to use VARCHARas both PRIMARY and FOREIGN KEYS.

我会说将VARCHAR用作PRIMARY 和 FOREIGN KEYS很好。

Only issue I could forsee is if you have a table, lets say Instruments(share instruments) and you create the PRIMARY/FOREIGN KEYas VARCHAR, and it happens that the CODEchanges.

我可以预见的唯一问题是，如果您有一个表格，比如说Instruments（共享工具），并且您将PRIMARY/FOREIGN KEY创建为VARCHAR，并且CODE发生了变化。

This does happen on Stock Exchanges, and would require you to rename all references to this CODE, where as a ID nr would not require this from you.

这确实发生在证券交易所，并且需要您重命名对此CODE 的所有引用，而 ID nr 不需要您这样做。

So to conclude, I would say this dependes on your intended use.

所以总而言之，我会说这取决于您的预期用途。

EDIT

编辑

When I say CODE, I mean the Ticker Code for lets say GOOG, or any other share. It is possible for these codes to change over time, lets say you look at Dirivative/Future instruments.

当我说 CODE 时，我的意思是说 GOOG 或任何其他共享的代码。这些代码可能会随着时间的推移而改变，假设您查看衍生品/未来工具。

回答by Tony

There's nothing wrong with either approach, although this question might start the usual argument of which is better: natural or surrogate keys.

这两种方法都没有错，尽管这个问题可能会引发通常的争论，即哪种更好：自然键或代理键。

If you use CHAR or VARCHAR as a primary key you'll end up using it as a forign key at some point. When it comes down to it, as @astander says, it depends on your data and how you are going to use it.

如果您使用 CHAR 或 VARCHAR 作为主键，您最终会在某个时候将其用作外键。归根结底，正如@astander 所说，这取决于您的数据以及您将如何使用它。