MySQL 数据库列具有多个值
我有一个问题，关于我的实现想法是否易于使用/编写查询。
我目前有一个包含多列的数据库。大多数列都是相同的东西（项目，但分为项目 1、项目 2、项目 3 等）。
所以我目前在我的数据库中有 ID、名称、项目 1、项目 2 ..... 项目 10。
我想将其压缩为 ID、名称、项目。
但我希望 item 具有的功能是将多个值存储为不同的行。即
ID = One Name = Hello Item = This
That
There
有点像它看起来的格式。这是个好主意吗？我到底该如何做呢？我将不会使用数据库中的任何数字，所有信息都将是静态的并且永远不会改变。
我可以使用 1 个数据库表来执行此操作（并且可以轻松地将一个 ID 的项目与另一个 ID 匹配），还是需要创建 2 个表并链接它们？
如果是这样，我究竟如何创建两个表并使它们相互关联？
关于如何实现这一点有什么想法吗？谢谢！
I had a question about whether or not my implementation idea is easy to work with/write queries for.
I currently have a database with multiple columns. Most of the columns are the same thing (items, but split into item 1, item 2, item 3 etc).
So I have currently in my database ID, Name, Item 1, Item 2 ..... Item 10.
I want to condense this into ID, Name, Item.
But what I want item to have is to store multiple values as different rows. I.e.
ID = One Name = Hello Item = This
That
There
Kind of like the format it looks like. Is this a good idea and how exactly would I go about doing this? I will be using no numbers in the database and all of the information will be static and will never change.
Can I do this using 1 database table (and would it be easy to match items of one ID to another ID), or would I need to create 2 tables and link them?
If so how exactly would I create 2 tables and make them relational?
Any ideas on how to implement this? Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是一种经典类型的非规范化数据库。非规范化有时会使某些操作更加高效，但更多时候会导致效率低下。 （例如，如果您的一个写入查询要更改与 id 关联的名称，则您必须更改多行而不是单行。）仅应在出于特定原因之后进行非规范化设计了一个完全规范化的数据库。在您的示例中，规范化的数据库设计将是：
table_1：ID（键），名称
table_2：ID（映射到table_1.ID的外键），项目
This is a classical type of denormalized data base. Denormalization sometimes makes certain operations more efficient, but more often leads to inefficiencies. (For example, if one of your write queries was to change the name associated with an id, you would have to change many rows instead of a single one.) Denormalization should only be done for specific reasons after a fully normalized data base has been designed. In your example, a normalized data base design would be:
table_1: ID (key), Name
table_2: ID (foreign key mapped to table_1.ID), Item
您谈论的是非规范化表，SQL 数据库很难处理该表。据说您的 Item 字段与其他字段具有多对一的关系。正确的做法是制作两张桌子。典型的例子是专辑和歌曲。歌曲与专辑具有多对一的关系，因此您可以像这样构建您的能力：
通常，此示例是用第三个表 Artist 给出的，您可以用 Artist 字段替换 Artist_id 字段，该字段是一个外键艺术家表的artist_id。
当然，现实中歌曲、专辑和艺术家要复杂得多。一首歌可以收录在多张专辑中，多位艺术家可以收录在一张专辑中，同一首歌曲有多个版本，甚至有些歌曲根本没有发行过专辑。
示例：
要查询此内容，您只需执行 JOIN：
从专辑中选择 * INNER JOIN 歌曲 ON Album.album_id = Song.album_idYou're talking about a denormalized table, which SQL databases have a difficult time dealing with. Your Item field is said to have a many-to-one relationship to the other fields. The correct things to do is to make two tables. The typical example is an album and songs. Songs have a many-to-one relationship to albums, so you could structure your ables like this:
Often this example is given with a third table Artist, and you could substitute the Artist field for an artist_id field which is a Foreign Key to an Artist table's artist_id.
Of course, in reality songs, albums, and artists are more complex. One song can be on multiple albums, multiple artists can be on one album, there are multiple versions of the same song, and there are even some songs which have no album release at all.
Example:
To query this you just do a JOIN:
SELECT * FROM Album INNER JOIN Song ON Album.album_id = Song.album_id我认为在这种情况下一张桌子真的没有意义。相反，你可以这样做：
然后当你进行查询时，你可以做一个简单的连接
I don't think one table really makes sense in this case. Instead you can do:
Then when you do queries you can do a simple join