mysql中如何存储这些字段描述呢？
对于这么长的话题表示歉意，我本来不想这么长，但这是我一直遇到的一个非常简单的问题。 :)
假设您有一个名为 tags 的简单表，其中包含列 tag_id 和 tag。 tag_id 只是一个自动递增列，标签是标签的标题。如果我需要添加描述字段，平均大约为 1-2 个段落（最多可能为 3-4 个段落），我应该简单地向表中添加一个描述字段，还是应该创建一个名为 tag_descriptions 的新表并存储带 tag_id 的描述？
我记得读到最好这样做，因为如果你执行一个不选择描述的查询，该描述字段仍然会减慢 mysql 的速度。这是真的吗？我什至不记得我从哪里读到的，但我已经关注它好几年了......最后我怀疑我是否需要这样做，我有一种感觉我不需要。每当您需要描述字段时，您还需要进行内部联接。
我的另一个问题是，创建最多只能容纳很少行的新表通常是不好的吗？如果这些数据不适合其他地方怎么办？
下面我有一个与这两个问题相关的简单案例。
我有三个表 content、tags 和 content_tags，它们构成了多对多关系：
content
- content_id
- 区域（带有的枚举列 大约 6-7 个不同的值，大多数 以后可能不会增长）
tags
- tag_id
- tag
content_tags
- content_id
- tag_id
我想为每个标签以及每个区域存储大约 1-2 段的描述。我想知道最好的方法是什么？
选项 A：
- 只需向 标签表
- 创建一个新表 Region_descriptions
选项 B：
- 创建一个名为的新表 带有字段的描述：id， 描述和类型
- id 将是内容的 id 或 枚举字段的 id
- 类型将是是否是标签 描述，或区域描述 （为此将使用枚举列）
也许在 id 和类型上有一个主键？
选项C：
- 为tag_descriptions创建一个新表
- 为region_descriptions创建一个新表
如果添加描述列不会减慢不需要描述的mysql选择查询，选项A似乎是一个不错的选择。
假设描述列会减慢 mysql 的速度，选项 B 可能是一个不错的选择。它还消除了对仅包含 6-7 行的小表来保存区域描述的需要。虽然现在我想起来了，如果最初只需要遍历很少的行来获取区域描述，那么连接到该表会很慢吗？
如果描述列会减慢 mysql 的速度，并且像区域描述这样的小表并不重要，那么选项 C 将是理想的选择。
也许这些选项都不是最好的，请随意提供其他选项。谢谢。
PS 用于保存通常为 1-2 段但有时可能会更多的数据的理想列类型是什么？
Apologize for the long topic, I didn't intend for it to be this long, but it's a pretty simple issue I've been having. :)
Let's say you have a simple table called tags that has columns tag_id and tag. The tag_id is simply an auto increment column and the tag is the title of the tag. If I need to add a description field, that would be around 1-2 paragraphs on average (max around 3-4 paragraphs probably), should I simply add a description field to the table or should I create a new table called tag_descriptions and store the descriptions with the tag_id?
I remember reading that it is better to do this because if you do a query that doesn't select the description, that description field will still slow down mysql. Is this true? I don't even remember where I read that from, but I've been kind of following it for a couple years now... Finally I question if I need to do this, I have a feeling I don't. You'd also need to inner join whenever you need the description field.
Another question I have is, is it generally bad to create new tables that will only hold very few rows at the max? What if this data doesn't fit anywhere else?
I have a simple case below which relates to these two questions.
I have three tables content, tags, and content_tags that make up a many to many relationship:
content
- content_id
- region (enum column with
about 6-7 different values and most
likely won't grow later on)
tags
- tag_id
- tag
content_tags
- content_id
- tag_id
I want to store a description around 1-2 paragraphs for each tag, but also for each region. I'm wondering what would be the best way to do this?
Option A:
- Just add a description column to the
tags table - Create a new table for
region_descriptions
Option B:
- Create a new table called
descriptions with fields: id,
description, and type - The id would be id of the content or
id of the enum field - The type would be whether it is a tag
description, or region description
(Would use the enum column for this)
Maybe have a primary key on the id and type?
Option C:
- Create a new table for tag_descriptions
- Create a new table for region_descriptions
Option A seems to be a good choice if adding the description column doesn't slow down mysql select queries that don't need the description.
Assuming the description column would slow down mysql, option B might be a good choice. It also removes the need for a small table with just 6-7 rows that would hold the region descriptions. Although now that I think of it, would it be slow to connect to this table if originally to get a region description you'd only need to go through very little rows.
Option C would be ideal if the description columns would slow down mysql and if a small table like region descriptions would not matter.
Maybe none of these options are the best, feel free to offer another option. Thanks.
P.S. What would be an ideal column type to use to hold data that usually 1-2 paragraphs, but might be a little more sometimes?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为如果您不能每分钟处理数千个查询，那么这并不重要。如果您每分钟要进行无数次查询，那么我将实现各种选项并对所有这些选项执行基准测试。根据结果，您可以做出决定。
I don't think it really matters if you don't handle thousands of queries per minute. If you are going to have a zillion queries per minute, then I would implement the various options and perform benchmarks for all these options. Based on the results, you can make a decision.
在我（诚然有些无知）看来，这实际上取决于您将使用它们的程度。
如果索引正确，那么 JOIN 的成本应该不会很高。此外，较大的表会变慢。它会抑制缓存，并且需要更长的时间来访问内容，尽管索引严重缓解了这个问题。
如果您要经常将标签名称与标签 ID 连接起来，并且很少会使用描述，那么我建议使用单独的表。如果您将更频繁地使用这些描述，请使用一张表格。
In my (admittedly somewhat uninformed) opinion, it really depends on how much you'll be using both of them.
If properly indexed, that JOIN should not be very expensive. Also, a larger table will be slower. It inhibits caching, and takes longer to access stuff, although indexing seriously mitigates this problem.
If you'll be joining tag names to tag IDs a LOT, and only rarely will be using the descriptions, I'd say go with separate tables. If you'll be using the descriptions more often, go with one table.
对于问题的第一部分：如果您有一个带有 id、名称和描述的标签，您应该将其保存在 1 个表中。
此查询不会减慢速度。
现在，如果您有 1、2 或 20 个额外字段，
For the first part of your question: if you have a tag with an id, a name and a description, you should save it in 1 table.
Now, this query
will NOT slow down if you have 1, 2 or 20 extra fields in there.