MySQL数据库列具有多个值

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).

我目前有一个包含多列的数据库。大多数列都是相同的（项目，但分为项目1，项目2，项目3等）。

So I have currently in my database ID, Name, Item 1, Item 2 ..... Item 10.

所以我目前在我的数据库ID，名称，项目1，项目2 ......项目10。

I want to condense this into ID, Name, Item.

我想将其浓缩为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?

我可以使用1个数据库表（并且很容易将一个ID的项目与另一个ID匹配），或者我是否需要创建2个表并链接它们？

If so how exactly would I create 2 tables and make them relational?

如果是这样，我将如何创建2个表并使它们成为关系？

Any ideas on how to implement this? Thanks!

有关如何实现这一点的任何想法？谢谢！

3 个解决方案

#1

I don't think one table really makes sense in this case. Instead you can do:

在这种情况下，我不认为一个表真的有意义。相反，你可以这样做：

Main Table:
ID
Name

Item Table:
ID
Item #
Item Value
Main_ID = Main Table.ID

Then when you do queries you can do a simple join

然后，当您进行查询时，您可以进行简单的连接

#2

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:

这是一种经典类型的非规范化数据库。非规范化有时会使某些操作更有效，但更常见的是导致效率低下。（例如，如果您的一个写入查询要更改与id关联的名称，则必须更改许多行而不是单个行。）非规范化应仅在完全规范化的数据库之后由于特定原因而完成设计的。在您的示例中，规范化数据库设计将是：

table_1: ID (key), Name
table_2: ID (foreign key mapped to table_1.ID), Item

table_1：ID（密钥），名称table_2：ID（映射到table_1.ID的外键），Item

#3

You'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:

你在谈论一个非规范化的表，哪些SQL数据库很难处理。您的项目字段与其他字段具有多对一关系。正确的做法是制作两张桌子。典型的例子是专辑和歌曲。歌曲与专辑有多对一的关系，所以你可以像这样构建你的ables：

Table Album
album_id [Primary Key]
Title
Artist

Table Song
song_id [Primary Key]
album_id [Foreign Key album.album_id]
Title

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.

通常这个示例是使用第三个表Artist给出的，您可以将Artist字段替换为artist_id字段，该字段是Artist表的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:

例：

Album
album_id Title       Artist
1        White       Beatles
2        Black       Metallica

Song
song_id  album_id  Title
1        2         Enter Sandman
2        1         Back in the USSR
3        2         Sad but True
4        2         Nothing Else Matters
5        1         Helter Skelter

To query this you just do a JOIN: SELECT * FROM Album INNER JOIN Song ON Album.album_id = Song.album_id

要查询这个，你只需要一个JOIN：SELECT * FROM Album INNER JOIN Song ON Album.album_id = Song.album_id

#1