我可以用VARCHAR作为主键吗?

I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a VARCHAR.

我有一个存储优惠券/折扣的表，我想使用coupon_code列作为主键，它是VARCHAR。

My rationale is that, each coupon will have a unique code, and the only commands I will be running are SELECT ... FROM ... WHERE coupon_code='..'

我的基本原理是，每个优惠券都有一个唯一的代码，我将运行的命令是SELECT…从…coupon_code =“. .”

I won't be doing any joins or indexing, and I don't see there ever being more than a few hundred entries in this table.

我不会做任何连接或索引，我也没有看到该表中有超过几百个条目。

It seems to me that this will be OK, but I don't know if there is anything I'm missing/not thinking about.

在我看来，这是可以的，但我不知道是否有什么我没有想到的。

5 个解决方案

#1

Of course you can, in the sense that your RDBMS will let you do it. The answer to a question of whether or not you should do it is different, though: in most situations, values that have a meaning outside your database system should not be chosen to be a primary key.

当然你可以，你的RDBMS会让你这么做。但是，对于是否应该这样做的问题，答案是不同的:在大多数情况下，不应该选择具有数据库系统之外含义的值作为主键。

If you know that the value is unique in the system that you are modeling, it is appropriate to add a unique index or a unique constraint to your table. However, your primary key should generally be some "meaningless" value, such as an auto-incremented number or a GUID.

如果您知道正在建模的系统中的值是惟一的，那么适当的做法是向您的表添加惟一的索引或惟一的约束。但是，主键通常应该是一些“无意义”的值，比如自动递增的数字或GUID。

The rationale for this is simple: data entry errors and infrequent changes to things that appear non-changeable do happen. They become much harder to fix on values which are used as primary keys.

这样做的理由很简单:数据输入错误和出现不可更改的内容的不常见更改确实会发生。它们变得更难固定作为主键的值。

#2

A blanket "no you shouldn't" is terrible advice. This is perfectly reasonable in many situations depending on your use case, workload, data entropy, hardware, etc.. What you shouldn't do is make assumptions.

一条毯子“不，你不应该”是一个糟糕的建议。根据您的用例、工作负载、数据熵、硬件等，这在很多情况下都是完全合理的。你不应该做的是做假设。

It should be noted that you can specify a prefix which will limit MySQL's indexing, thereby giving you some help in narrowing down the results before scanning the rest. This may, however, become less useful over time as your prefix "fills up" and becomes less unique.

需要注意的是，您可以指定一个前缀，它将限制MySQL的索引，从而在扫描其余部分之前为缩小结果提供一些帮助。然而，随着时间的推移，当你的前缀“填充”并变得不那么独特时，这可能会变得不那么有用。

It's very simple to do, e.g.:

这很简单，例如:

CREATE TABLE IF NOT EXISTS `foo` (
  `id` varchar(128),
  PRIMARY KEY (`id`(4)),
)

Also note that the prefix (4) appears after the column quotes.

还要注意，前缀(4)出现在列引号之后。

Lastly, you should read how index prefixes work and their limitations before using them:

最后，在使用索引前缀之前，您应该阅读索引前缀的工作原理及其局限性:

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

#3

It depends on the specific use case.

它取决于特定的用例。

If your table is static and only has a short list of values (and there is just a small chance that this would change during a lifetime of DB), I would recommend this construction:

如果您的表是静态的，并且只有一个简短的值列表(并且在DB的生命周期中，这个值发生变化的可能性很小)，我建议这样的构造:

CREATE TABLE Foo 
(
    FooCode VARCHAR(16), -- short code or shortcut, but with some meaning.
    Name NVARCHAR(128), -- full name of entity, can be used as fallback in case when your localization for some language doesn't exist
    LocalizationCode AS ('Foo.' + FooCode) -- This could be a code for your localization table...&nbsp;
)

Of course, when your table is not static at all, using INT as primary key is the best solution.

当然，当表不是静态的时候，使用INT作为主键是最好的解决方案。

#4

It is ok for sure. With just few hundred of entries, it will be fast.

当然可以。只有几百个条目，就会很快。

You can add an unique id as as primary key (int autoincrement) ans set your coupon_code as unique. So if you need to do request in other tables it's better to use int than varchar

您可以添加一个惟一的id作为主键(int autoincrement)，将coupon_code设置为惟一的。因此，如果需要在其他表中执行请求，最好使用int而不是varchar

#5

i would preferably use Unique instead of Primary key in this context, as to avoid data entry error or it could used to update the coupen code etc..

在这种情况下，我最好使用Unique而不是Primary key，以避免数据输入错误，或者可以用来更新coupen代码等。

#1