如何在 MySQL 的单元格中存储单个项目的多个值 [英] How do I store multiple values for a single item in a cell in MySQL

问题描述

序列化是最好的方法吗?

Is serialize the best way to do this?

假设我有一个音乐数据库，我想按流派对音乐进行分类.如果我允许给定专辑有多种流派，我是否将它们存储为数组并序列化它们?像这样吗?

If, say, I have a database of music and I want to classify music by genres. If I allow for multiple genres for a given album, do I store them as an array and serialize them? Like this?

$array = serialize(array(1,2,3));//数字是流派的 ID

$array = serialize(array(1,2,3)); // numbers are IDs of genres

我是从这里的另一篇文章中得到的.我知道我可以反序列化它们.我不明白的是我将如何编写一个 SQL 语句来检索部分或全部数据.如果我想检索给定专辑的所有流派，我的 SQL 语句会是什么样的?

I get that from another post here. I know I can unserialize them. What I don't get is how I would write an SQL statement that would retrieve some or all of the data. What would my SQL statement look like if I wanted to retrieve all of the genres for a given album?

推荐答案

您真的想要规范化数据并将流派和专辑与流派关联存储在不同的表中.

You really want to normalise the data and store the genres and the album to genre association in different tables.

即:您最好拥有三张桌子:

i.e.: You'd ideally have three tables:

1. 专辑专辑数据
2. 流派流派数据
3. 专辑 -> 流派查找 形成每个关联的专辑 ID 和流派 ID

1. Albums The album data
2. Genres The genre data
3. Album -> Genre lookup The album id and genre id that forms each association

通过这样做，您将能够根据将来的需要轻松添加/删除等类型，并且能够通过加入表格来执行查找，而不必执行字符串操作.

By doing this you'll be able to trivially add/remove, etc. genres as required in the future and will be able to perform lookups by joining the tables rather than having to perform string manipulation.

这篇关于如何在 MySQL 的单元格中存储单个项目的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！