MySQL 一对多关系|极客笔记

MySQL 一对多关系

阅读更多：MySQL 教程

什么是One to Many？

One to Many是一种常见的关系型数据库设计模式，即一个表与另一个表之间的一对多关系。在此关系中，一个表的某个列包含另一个表的行中的值。这使得一个表可以保存多个相关行。

例如，在一个在线商店中，一个用户可以购买多个商品。这种关系可以表示为单个用户ID与多个购买记录ID的关系。在这个例子中，用户表是One表，而购买记录表是Many表。

One-to-Many关系在MySQL中非常常见，可以用于许多应用程序。接下来，我们将学习如何在MySQL中实现One-to-Many关系。

One to Many实现

在MySQL中实现One-to-Many关系，需要先创建两个表：One表和Many表。 One表具有唯一的ID（主键），而Many表与One表之间相互关联，其中包含对One表的外键。

下面是One表的简单示例：

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL
);

在此示例中，我们创建了一个名为“user”的表，其中包含三列：ID，名称和电子邮件。在此示例中，ID是该表的主键。

接下来，我们将创建用于Many表的表。在此示例中，我们创建了一个名为“order”的表，作为Many表：

CREATE TABLE `order` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `qty` INT NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

在此示例中，我们创建了一个名为“order”的表，其中包含五列：ID，用户ID，产品名称，数量和价格。在此示例中，“user_id”的列是一个外键，引用One表中的“ID”列。

插入数据

要在insert语句中插入数据，您需要在One表中插入数据并使用生成的ID在Many表中插入数据。例如，以下是如何在上述表中插入数据的示例：

INSERT INTO `user` (`name`, `email`) VALUES ('John', 'john@example.com');

此语句将在One表中插入一行。请注意，没有提供ID，因为MySQL会自动分配一个唯一的ID值。

接下来，我们将使用新创建的One表中生成的ID，在Many表中插入数据。要执行此操作，我们必须将从One表中选择生成的ID。

我们可以使用LAST_INSERT_ID()函数选择最后插入的ID。在这个例子中，我们将使用该函数选择在One表中插入的ID：

INSERT INTO `order` (`user_id`, `product_name`, `qty`, `price`) VALUES (LAST_INSERT_ID(), 'Toy Car', 1, 9.99);

此语句将在Many表中插入一行，其中包含生成的用户ID和产品名称、数量和价格。

您可以重复这个过程，插入更多Many表中的数据。在此示例中，我们可以插入使用相同的One表用户ID的多条记录。

查询数据

要查询One及其Many表中的数据，您必须使用JOIN查询。JOIN查询允许您将两个表合并为一个结果集。

以下是一个简单的JOIN查询示例，将用户的详细信息和他们的订单详细信息联结在一起：

SELECT `user`.`id`, `user`.`name`, `user`.`email`, `order`.`product_name`, `order`.`qty`, `order`.`price`
FROM `user`
JOIN `order` ON `user`.`id`=`order`.`user_id`
WHERE`user`.`name`='John';

此查询将返回用户“John”的详细信息以及他的所有订单详细信息。

在此查询中，我们使用JOIN关键字将user表和order表连接在一起。我们还使用user_id引用一个表中的id列，以便我们可以联结两个表。

更新和删除数据

对于One-to-Many关系，更新和删除数据可能比其他关系更困难。如果您尝试删除One表中的一行，可能会影响Many表。解决此问题的最佳方法是使用外键关系和“级联删除”选项。

使用外键时，您可以设置CASCADE DELETE选项来保证进行一对多操作时不会丢失数据。如果您向One表插入数据，则Many表中的这些数据也必须存在，并且将被保留。如果您从One表中删除数据，则Many表中具有这些外键的行也将被删除。

以下是使用级联删除选项的示例：

CREATE TABLE `order` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `qty` INT NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
);

在此示例中，我们在创建外键关系时指定了ON DELETE CASCADE选项。这样，在One表中删除行时，Many表中具有这些外键的行也将被删除。