具有许多条目的 MySQL 字段

Question

我有一个页面列表显示一个乐队已经演奏，以及在该节目中演奏的所有其他乐队。到目前为止，我一直在手工编写页面代码，但我计划将所有内容移到一个表中，以便按乐队排序/更易于维护。

截至目前，计划是拥有一个“艺术家”字段，其中典型的条目是 ex。 “布兰妮·斯 PIL 斯、史密斯飞船、妮琪·米娜。”但我想知道是否有更聪明的方法来做到这一点。每个艺术家都在自己的领域？ (请记住，有些节目有三个波段，有些有 100 个，因此实际上创建 100 个字段是不可行的。)或者当前的计划是否足够？

这方面很新，所以只是寻找一般意见/正确方向的观点。谢谢!

Answer 1

您可以从简单的多对多连接开始 - 请参阅 An Introduction to Database Normalization以及如何处理 Many-to-Many relationship with PHP and MySQL以获得对这些主题的良好介绍。

最简单的，你有三个表:

1. 艺术家 table
2. field 表
3. 将艺术家与场馆联系起来的表格

例如:

event <---> event_artist <---> artist

通过这样做，您可以减少重复，并可以更轻松地生成一系列查询，从而使您可以从数据库中提取有用的信息。这是一个使用 MySQL InnoDB 的简单示例表格:

首先，创建一个包含事件列表的表。每个事件都有自己唯一的 ID:

CREATE TABLE `event` (
  `id` INT NOT NULL ,
  `venue` VARCHAR(45) NOT NULL ,
  `date` DATE NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

现在创建一个包含艺术家列表的表。与事件一样，每位艺术家都被分配了一个唯一的 ID:

CREATE TABLE `artist` (
  `id` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

您现在需要一个允许将事件行链接到艺术家行的表。这被称为链接表、联结表、交叉引用表或交集表——我更喜欢术语联结表。

CREATE  TABLE IF NOT EXISTS `event_artist` (
  `event_id` INT NOT NULL ,
  `artist_id` INT NOT NULL ,
  PRIMARY KEY (`event_id`, `artist_id`) ,
  INDEX `fk_event_artist_event` (`event_id` ASC) ,
  INDEX `fk_event_artist_artist` (`artist_id` ASC) ,
  CONSTRAINT `fk_event_artist_event`
    FOREIGN KEY (`event_id` )
    REFERENCES `event` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_event_artist_artist`
    FOREIGN KEY (`artist_id` )
    REFERENCES `artist` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

暂时不要担心 CONSTRAINT 部分——它们有助于维护数据库的完整性，方法是防止您在 event_artist 中插入一个条目不存在。

现在，我们可以插入一些数据。首先，一些事件:

INSERT INTO `event` (`id`, `venue`, `date`) VALUES
(1, 'Roadhouse', '2010-08-19'),
(2, 'Night & Day Café Music Venue', '2010-08-20'),
(3, 'Night & Day Café Music Venue', '2010-08-25'),
(4, 'The Bridgewater Hall', '2010-09-03');

现在一些艺术家:

INSERT INTO `artist` (`id`, `name`) VALUES
(1, 'Starving Dogs'),
(2, 'The Black and Reds'),
(3, 'Caitlin Rose'),
(4, 'A Little Bite Music - Bella Hardy');

最后，我们可以加入事件和艺术家:

INSERT INTO `event_artist` (`event_id`, `artist_id`) VALUES
(1, 1), (2, 2), (3, 3), (4, 4);

在这个非常简单的示例中，ID 为 1 的艺术家在 ID 1 的事件中演奏，艺术家 ID 2 在事件 ID 2 中演奏，依此类推。然而，正如您将看到的，存在让多位艺术家在多个事件中表演的可能性。

您现在需要一种从这些表中提取信息的方法。最简单的方法之一是使用 JOIN :

SELECT
  event.venue AS event_venue,
  event.date AS event_date,
  artist.name AS artist_name
FROM event_artist
JOIN event ON event.id = event_artist.event_id
JOIN artist ON artist.id = event_artist.artist_id;

+------------------------------+------------+-----------------------------------+
| event_venue                  | event_date | artist_name                       |
+------------------------------+------------+-----------------------------------+
| Roadhouse                    | 2010-08-19 | Starving Dogs                     |
| Night & Day Café Music Venue | 2010-08-20 | The Black and Reds                |
| Night & Day Café Music Venue | 2010-08-25 | Caitlin Rose                      |
| The Bridgewater Hall         | 2010-09-03 | A Little Bite Music - Bella Hardy |
+------------------------------+------------+-----------------------------------+

可以通过多种方式改进此示例。您可能会注意到 event 表很容易以重复的地点名称告终。您可能希望将 field 分开到他们自己的表中，这样您就有一个 field 表，其中包含 field 本身的详细信息(地址、电话、网站等)，一个事件表(事件日期、时间、门票价格等) ,)，然后是艺术家表。

更新

您可以使用 MySQL 将多行中的值分组为一行。然而，这种事情实际上是一个 View /显示问题，因此通常会在您的应用程序中完成。话虽如此，对于简单的情况，您可以使用 MySQL [GROUP_CONCAT][5] 函数:

SELECT
  event.venue AS event_venue,
  GROUP_CONCAT(artist.name) AS artists
FROM event_artist
JOIN event ON event.id = event_artist.event_id
JOIN artist ON artist.id = event_artist.artist_id
GROUP BY (event.venue);

+------------------------------+-----------------------------------+
| event_venue                  | artists                           |
+------------------------------+-----------------------------------+
| Night & Day Café Music Venue | The Black and Reds,Caitlin Rose   |
| Roadhouse                    | Starving Dogs                     |
| The Bridgewater Hall         | A Little Bite Music - Bella Hardy |
+------------------------------+-----------------------------------+

请注意，我在上述查询中遗漏了日期。在此处给出的样本数据中，Night & Day Café 的两位艺术家出现在不同的日期。如果日期列包含在上面的查询中，MySQL 将不得不选择两个日期之一，而您将无法控制显示哪个日期。