可以将字符值用作主键吗? [英] Is it ok to use character values for primary keys?

问题描述

与使用基于字符的字段相比，在数据库表中使用唯一的数字ID字段是否有性能提升或最佳实践?

Is there a performance gain or best practice when it comes to using unique, numeric ID fields in a database table compared to using character-based ones?

例如，如果我有两个表:

For instance, if I had two tables:

id ... 17 ，名称 ... Rickey Henderson ， teamid ... 28

id ... 17, name ... Rickey Henderson, teamid ... 28

teamid ... 28 ， teamname ... 奥克兰

teamid ... 28, teamname ... Oakland

如果 teamid 是"OAK"或"SD"而不是"28"或"31"，那么具有成千上万名运动员的运动员桌将更易于阅读.让我们理所当然地认为 teamid 值在字符形式上保持唯一且一致.

The athlete table, with thousands of players, would be easier to read if the teamid was, say, "OAK" or "SD" instead of "28" or "31". Let's take for granted the teamid values would remain unique and consistent in character form.

我知道您可以使用字符，但是出于某种原因对索引，过滤等是一个坏主意吗?

I know you CAN use characters, but is it a bad idea for indexing, filtering, etc for any reason?

请忽略归一化参数，因为这些表比示例更复杂.

推荐答案

我会尽量避免使用文本作为键-将来要更改某个团队的团队ID时会发生什么?当主键可以避免的确切事情时，您就必须在整个数据中级联该键更改.另外，尽管我没有任何经验证据，但我认为INT键会比文本键快得多.

I'd stay away from using text as your key - what happens in the future when you want to change the team ID for some team? You'd have to cascade that key change all through your data, when it's the exact thing a primary key can avoid. Also, though I don't have any emperical evidence, I'd think the INT key would be significantly faster than the text one.

也许您可以为数据创建视图，使其更易于使用，同时仍使用数字主键.

Perhaps you can create views for your data that make it easier to consume, while still using a numeric primary key.

这篇关于可以将字符值用作主键吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！