MySql 数据类型的使用
使用以下示例，我试图找出设置 MySql 数据库的最佳方法。
FoodGroupTypeCode：谷物、蔬菜、水果、乳制品、蛋白质
什么是最好的用于表示该属性的数据类型？
CHAR(1)：G、V、F、D、PTINYINT：使用数字代码（即 Grain = 1、Vege = 2）ENUM>VARCHAR(9)：使用全名- 其他（解释）
选项4确实不是我的考虑范围，除非有人能真正验证它。我读过很多关于这个主题的“意见”，但寻找更具体的理由来选择其中之一。我很欣赏有人能提供的任何意见，而不是诸如“我喜欢 ENUM 因为它很快”之类的东西。
Using the following as an example I am trying to figure out the best way set up a MySql database.
FoodGroupTypeCode: Grain, Vegetable, Fruit, Dairy, Protein
What is the best Data Type to use to represent this attribute?
CHAR(1): G, V, F, D, PTINYINT: Using number codes (i.e. Grain = 1, Vege = 2)ENUMVARCHAR(9): Using full names- Other (explain)
Option 4 is really not a consideration of mine, unless someone can really validate it. I have read a lot of "opinions" on the subject but looking for more concrete reasons for picking one over the other. I appreciate any input that someone can give to that degree rather then something such as "I like ENUM because it is fast."
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
CHAR(1)：G、V、F、D、P
高性能、存储效率高且代码可读。当我知道这些值是稳定的并且当我需要将行为附加到代码时，我喜欢这个解决方案。
（
如果 code = 'G' 则特定行为）。TINYINT：使用数字代码（即 Grain = 1，Vege = 2）
性能好、存储效率高，但会导致代码可读性较差。如果值发生变化，该解决方案也不会造成混乱（G 最初是 Grain，但现在改为 Brain）。这是最常见的。
枚举
除了微小的性能优势之外，我没有看到使用枚举的任何好处。
当然，如果您确实遇到性能问题，那么小的性能提升永远是不够的。
VARCHAR(9)：使用全名
结果是可读的代码。某些查询将具有较少的联接，这在这些特定情况下提供了性能优势。存储效率不高，如果您想更改值，可能会给您带来问题。如果您有很多包含很多行的表，并且它们都引用该表，那么在冒险之前请仔细考虑。
CHAR(1): G, V, F, D, P
Performant, storage efficient and results in readable code. I favour this solution when I know that the values are stable, and when I need to attach behaviour to the codes.
(
if code = 'G' then specific behaviour).TINYINT: Using number codes (i.e. Grain = 1, Vege = 2)
Performant, storage efficient, but results in less readable code. This solution is also immune to confusion if values change (G was initially Grain, but now it is changed into Brain). This is most common.
ENUM
I don't see a single benefit of using enum other than a tiny performance benefit.
Of course, the small performance gain will never be enough if you are truly having a performance problem.
VARCHAR(9): Using full names
Results in readable code. Certain queries will have less joins which in those specific cases provides performance benefits. Not storage efficient, and may give you problems if you want to change the values. If you have lots of tables with lots of rows and they are all referencing this table, think carefully before you take the risk.
我会推荐 ENUM，因为它会限制您对谷物、蔬菜、水果、乳制品、蛋白质的可能选择。
然而，在数据库中，我通常将其作为带有检查约束（只能是谷物、蔬菜、水果、乳制品或蛋白质）的 VARCHAR(2) 来执行，然后在我的应用程序中将其表示为 ENUM 或列表。
I would recommend ENUM, because it would constrain your possible selections to Grain, Vegetable, Fruit, Dairy, Protein.
However, in the database I usually do this as a VARCHAR(2) with a Check Constraint (can only be Grain, Vegetable, Fruit, Dairy, or Protein), then represent it in my app as an ENUM or List.
适当数据类型的选择完全取决于您将如何处理应用程序中的表数据（如果有），如果您的表只是用于存储记录，那么
VARCHAR将是更好的选择，因为它会使您的数据更加详细，但是如果要对表数据进行处理，例如根据某些条件过滤结果，那么在应用程序逻辑中比较它们时，长名称可能会令人困惑，并且在这种情况下，CHAR或TINYINT将是更好的选择。而基于所使用的数据类型的性能影响应该是第二要务。The choice of appropriate datatype entirely depends on how you'll be dealing with the table data in your application (if any), if you're having this table just for storing the records than
VARCHARwould be a better option, since it'll make your data more verbose, but if there's a processing to be done on the table data like, filter results on certain criteria, long names could be confusing when it comes to comparing them in the application logic, and in such situations,CHARorTINYINTwould be a better option. While the performance impact based on the datatypes used should be a second priority.我会使用
CREATE TABLE FoodGroupType (id int unsigned not null auto_increment PRIMARY KEY, code VARCHAR(20), description VARCHAR(100)。所以食物表看起来
I'd use
CREATE TABLE FoodGroupType (id int unsigned not null auto_increment PRIMARY KEY, code VARCHAR(20), description VARCHAR(100).So Food table will look
我个人不会选择 Char(1) 或tinyint，因为它们没有描述性。从数据来看，并没有想象的那么清楚。 Varchar 和 enum 是我会考虑的两个。不过，我喜欢枚举的想法，因为这就是它的本质。您是说所有食物都必须是这些食物类型之一。
另外我听说枚举很快！
I personally would not go with Char(1) or tinyint because they are not descriptive. Looking at the data it is not as clear as it could be. Varchar and enum would be the two I would consider. I like the idea of enum though, as that is really what it is. You are saying all foods have to be one of these food types.
Plus I hear enum is fast!