MySQL 我可以使用 VARCHAR 作为 PRIMARY KEY 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19299874/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I use VARCHAR as the PRIMARY KEY?
提问by Mike Brady
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 ... FROM ... WHERE 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.
在我看来这没问题，但我不知道是否有什么我遗漏/没有考虑的地方。
回答by dasblinkenlight
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 shoulddo it is different, though: in most situations, values that have a meaning outside your database system should notbe 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.
这样做的理由很简单：数据输入错误和对看似不可更改的事物的不频繁更改确实会发生。它们变得更难修复用作主键的值。
回答by Josh
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'tdo 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 afterthe column quotes. Where the 4means that it should use the first 4 characters of the 128 possible characters that can exist as the id.
另请注意，前缀(4)出现在列引号之后。其中4意味着它应该使用 128 个可能字符中的前 4 个字符作为id.
Lastly, you should read how index prefixes work and their limitations before using them: https://dev.mysql.com/doc/refman/8.0/en/create-index.html
最后，您应该在使用之前阅读索引前缀的工作原理及其限制：https: //dev.mysql.com/doc/refman/8.0/en/create-index.html
回答by Tomino
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...
)
Of course, when your table is not static at all, using INTas primary key is the best solution.
当然，当您的表根本不是静态的时，使用INT作为主键是最好的解决方案。
回答by guikk
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 自动增量）并将您的coupon_code 设置为唯一的。因此，如果您需要在其他表中执行请求，最好使用 int 而不是 varchar
