mysql 多对多存储

原创

mob649e81607bf3 2023-10-19 07:42:57 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e81607bf3的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 多对多关系存储

在关系型数据库中，多对多（Many-to-Many）关系是指两个实体之间存在多对多的关联关系。例如，一个学生可以选择多门课程，而一门课程也可以被多个学生选择。在 MySQL 中，我们可以使用中间表来存储多对多关系，并使用 JOIN 操作来检索相关数据。

中间表设计

中间表（也称为联结表或连接表）用于存储多对多关系的关联数据。它包含两个外键，分别指向两个实体表。在中间表中，每一行代表两个实体之间的一个关联关系。

让我们以一个示例来说明多对多关系的存储方法。假设我们有两个实体表：students（学生）和 courses（课程）。一个学生可以选择多门课程，而一门课程也可以被多个学生选择。为了存储这种多对多关系，我们需要创建一个中间表 student_courses。

下面是 students 表的结构：

字段名	类型
id	int(11)
name	varchar(255)
enrollment	date

下面是 courses 表的结构：

字段名	类型
id	int(11)
name	varchar(255)
credit	int(11)

创建中间表

使用以下 SQL 语句来创建 student_courses 中间表：

CREATE TABLE student_courses (
    student_id int(11),
    course_id int(11),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

在上述语句中，我们定义了两个外键约束来关联 student_id 列和 course_id 列。PRIMARY KEY 语句定义了这两列作为联合主键，确保每一行的组合值都是唯一的。

插入数据

现在我们已经创建了中间表，接下来我们需要向中间表中插入数据来表示学生和课程之间的关系。

INSERT INTO student_courses (student_id, course_id) VALUES
    (1, 1),
    (1, 2),
    (2, 1),
    (3, 2),
    (3, 3);

上述语句将学生和课程的关联关系插入到中间表中。例如，学生1选择了课程1和课程2，学生2选择了课程1，学生3选择了课程2和课程3。

查询关联数据

我们可以使用 JOIN 操作来检索学生和课程之间的关联数据。以下是一个简单的例子，检索学生及其所选课程的名称：

SELECT students.name AS student_name, courses.name AS course_name
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON courses.id = student_courses.course_id;

上述查询将返回以下结果：