mysql多对多关系概念数据库多对多关系模型

转载

mob64ca141a2a87 2024-04-28 20:39:59

文章标签 mysql多对多关系概念 mysql多对多关系创建语句 SQL User 多对多 文章分类 MySQL 数据库

Sequelize 是一个基于 Promise 的 Node.js ORM，目前支持 Postgres、MySQL、SQLite 和 Microsoft SQL Server。它具有强大的事务支持，关联关系、读取和复制等功能。在阅读本文前，如果你对 Sequelize 还不了解，建议先阅读 Sequelize 快速入门这篇文章。

数据模型中的表关系一般有三种：一对一、一对多、多对多。Sequelize 为开发者提供了清晰易用的接口来定义关系、进行表之间的操作。本文我们将介绍在 Sequelize 中如何定义多对多的表关系。

基本概念

Source & Target

我们首先从一个基本概念开始，你将会在大多数关联中使用 source 和 target 模型。假设您正试图在两个模型之间添加关联。这里我们在 User 和 Project 之间添加一个 hasOne 关联。

const User = sequelize.define('User', {
name: Sequelize.STRING,
email: Sequelize.STRING
});
const Project = sequelize.define('Project', {
name: Sequelize.STRING
});
User.hasOne(Project);

User 模型(函数被调用的模型)是 source 。 Project 模型(作为参数传递的模型)是 target 。

belongsToMany

多对多关联用于将源与多个目标相连接。此外，目标也可以连接到多个源。1

2Project.belongsToMany(User, { through: 'UserProject' });

User.belongsToMany(Project, { through: 'UserProject' });

这将创建一个名为 UserProject 的新模型，具有等效的外键 projectId 和 userId。属性是否为 camelcase 取决于由表(在这种情况下为 User 和 Project )连接的两个模型。User.belongsToMany(Project, {through: 'UserProject'}) —— 将添加方法 getUsers, setUsers, addUser, addUsers 到 Project 上。

User.belongsToMany(Project, {through: 'UserProject'}) —— 将添加方法 getPorjects, setProjects, addProject, addProjects 到 User 上。

有时，您可能需要在关联中使用它们时重命名模型。让我们通过使用别名(as)选项将 users 定义为 workers 而 projects 定义为 tasks。我们还将手动定义要使用的外键：

User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey:
'userId' });
Project.belongsToMany(User, { as: 'Workers', through: 'worker_tasks', foreignKey:
'projectId' })

如果你想要连接表中的其他属性，则可以在定义关联之前为连接表定义一个模型，然后再说明它应该使用该模型进行连接，而不是创建一个新的关联：

const User = sequelize.define('user', {})
const Project = sequelize.define('project', {})
const UserProjects = sequelize.define('userProjects', {
status: DataTypes.STRING
})
User.belongsToMany(Project, { through: UserProjects })
Project.belongsToMany(User, { through: UserProjects })

默认情况下，上面的代码会将 projectId 和 userId 添加到 UserProjects 表中，删除任何先前定义的主键属性 - 表将由两个表的键的组合唯一标识，并且没有其他主键列。若需要在 UserProjects 模型上添加一个主键，你可以手动添加它。

const UserProjects = sequelize.define('userProjects', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
status: DataTypes.STRING
})

使用多对多你可以基于 through 关系查询并选择特定属性，比如：

User.findAll({
include: [{
model: Project,
through: {
attributes: ['createdAt', 'startedAt', 'finishedAt'],
where: {completed: true}
}
}]
});

多对多关系

模型定义

model/note.js
const Sequelize = require("sequelize");
module.exports = sequelize => {
const Note = sequelize.define("note", {
title: {
type: Sequelize.CHAR(64),
allowNull: false
}
});
return Note;
};

model/tag.js
const Sequelize = require("sequelize");
module.exports = sequelize => {
const Tag = sequelize.define("tag", {
name: {
type: Sequelize.CHAR(64),
allowNull: false,
unique: true
}
});
return Tag;
};

model/tagging.js
const Sequelize = require("sequelize");
module.exports = sequelize => {
const Tagging = sequelize.define("tag", {
type: {
type: Sequelize.INTEGER,
allowNull: false
}
});
return Tagging;
};

数据库连接及关系定义

db.js
const Sequelize = require('sequelize');
const sequelize = new Sequelize('exe', 'root', '', {
host: 'localhost',
dialect: 'mysql',
operatorsAliases: false,
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
});
sequelize
.authenticate()
.then(async () => {
console.log('Connection has been established successfully.');
const Note = require('./model/note')(sequelize);
const Tag = require('./model/tag')(sequelize);
const Tagging = require('./model/tagging')(sequelize);
// Note的实例拥有getTags、setTags、addTag、addTags、createTag、
// removeTag、hasTag方法
Note.belongsToMany(Tag, { through: Tagging });
// Tag的实例拥有getNotes、setNotes、addNote、addNotes、createNote、
// removeNote、hasNote方法
Tag.belongsToMany(Note, { through: Tagging });
sequelize.sync({
force: true
})
.then(async () => {
})
})
.catch(err => {
console.error('Unable to connect to the database:', err);
});

以上代码运行后，终端将会输出以下信息：新建 notes 表

CREATE TABLE IF NOT EXISTS `notes` (
`id` INTEGER NOT NULL auto_increment ,
`title` CHAR(64) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB;新建 tags 表

CREATE TABLE IF NOT EXISTS `tags` (
`id` INTEGER NOT NULL auto_increment ,
`type` INTEGER NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB;新建 taggings 表

CREATE TABLE IF NOT EXISTS `taggings` (
`type` INTEGER NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`noteId` INTEGER ,
`tagId` INTEGER ,
PRIMARY KEY (`noteId`, `tagId`),

FOREIGN KEY (`noteId`) REFERENCES `notes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`tagId`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

可以看到，多对多关系中我们单独生成了一张关系表，并设置了 2 个外键 tagId 和 noteId 来和 tags 和notes 进行关联。

关系操作新增

方式一

const note = await Note.create({ title: 'note' }); // (1)

await note.createTag({ name: 'tag' }, { through: { type: 0 }}); // (2)

步骤一：新增 note 记录，对应的 SQL 语句如下：1INSERT INTO `notes` (`id`,`title`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'note','2018-10-12 09:19:11','2018-10-12 09:19:11');

步骤二(1)：新建 tag 记录，对应的 SQL 语句如下：1INSERT INTO `tags` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'tag','2018-10-12 09:19:11','2018-10-12 09:19:11');

步骤二(2)：新建 tagging 记录，对应的 SQL 语句如下：1INSERT INTO `taggings` (`type`,`createdAt`,`updatedAt`,`noteId`,`tagId`) VALUES (0,'2018-10-12 09:19:11','2018-10-12 09:19:11',1,1);

关系表本身需要的属性，通过传递一个额外的对象给设置方法来实现。

方式二

const note = await Note.create({ title: 'note' });

const tag = await Tag.create({ name: 'tag' });

await note.addTag(tag, { through: { type: 0 } });

这种方法和上面的方法实际上是一样的。只是我们先手动 create 了一个 Tag 模型。

方式三

const note = await Note.create({ title: 'note' }); // (1)

const tag1 = await Tag.create({ name: 'tag1' }); // (2)

const tag2 = await Tag.create({ name: 'tag2' }); // (3)

wait note.addTags([tag1, tag2], { through: { type: 2 }}); // (4)

步骤一：新增 note 记录，对应的 SQL 语句如下：1INSERT INTO `notes` (`id`,`title`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'note','2018-10-12 11:33:17','2018-10-12 11:33:17');

步骤二：新建第一条 tag 记录，对应的 SQL 语句如下：1INSERT INTO `tags` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'tag1','2018-10-12 11:33:17','2018-10-12 11:33:17');

步骤三：新建第二条 tag 记录，对应的 SQL 语句如下：1INSERT INTO `tags` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'tag2','2018-10-12 11:33:17','2018-10-12 11:33:17');

步骤四：新增两条 tagging 记录，对应的 SQL 语句如下：1INSERT INTO `taggings` (`type`,`createdAt`,`updatedAt`,`noteId`,`tagId`) VALUES (2,'2018-10-12 11:33:17','2018-10-12 11:33:17',1,1),(2,'2018-10-12 11:33:17','2018-10-12 11:33:17',1,2);修改const note = await Note.create({ title: 'note' });

const tag1 = await Tag.create({ name: 'tag1'});
const tag2 = await Tag.create({ name: 'tag2'});
await note.addTags([tag1, tag2], { through: { type: 2 }});
const tag3 = await Tag.create({ name: 'tag3'}); // (1)
const tag4 = await Tag.create({ name: 'tag4'}); // (2)
await note.setTags([tag3, tag4], { through: { type: 3 }}); // (3)

步骤一：新建第三条 tag 记录，对应的 SQL 语句如下：1INSERT INTO `tags` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'tag3','2018-10-12 11:43:16','2018-10-12 11:43:16');

步骤二：新建第四条 tag 记录，对应的 SQL 语句如下：1INSERT INTO `tags` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'tag4','2018-10-12 11:43:16','2018-10-12 11:43:16');

步骤三(1)：删除当前 note 记录，与 tag1、tag2 之间的关联信息，对应的 SQL 语句如下：1DELETE FROM `taggings` WHERE `noteId` = 1 AND `tagId` IN (1, 2)

步骤三(2)：设置当前 note 记录，与 tag3、tag4 之间的关联信息，对应的 SQL 语句如下：1INSERT INTO `taggings` (`type`,`createdAt`,`updatedAt`,`noteId`,`tagId`) VALUES (3,'2018-10-12 11:43:16','2018-10-12 11:43:16',1,3),(3,'2018-10-12 11:43:16','2018-10-12 11:43:16',1,4);删除

删除单条记录

const note = await Note.create({ title: 'note' });
const tag1 = await Tag.create({ name: 'tag1' });
const tag2 = await Tag.create({ name: 'tag2' });
await note.addTags([tag1, tag2], { through: { type: 2 }});
await note.removeTag(tag1); // (1)

步骤一：删除 tag1 记录，对应的 SQL 语句如下：1DELETE FROM `taggings` WHERE `noteId` = 1 AND `tagId` IN (1)

删除单条记录很简单，直接将关系表 taggings 中的数据删除。

全部删除

const note = await Note.create({ title: 'note' });
const tag1 = await Tag.create({ name: 'tag1' });
const tag2 = await Tag.create({ name: 'tag2' });
await note.addTags([tag1, tag2], { through: { type: 2 }});
await note.setTags([]); // (1)

步骤一(1)：查询关系表 taggings 中与当前 note 相关的记录，对应的 SQL 语句如下：1SELECT `type`, `createdAt`, `updatedAt`, `noteId`, `tagId` FROM `taggings` AS `tagging` WHERE `tagging`.`noteId` = 1;

步骤一(2)：删除所有匹配的数据，对应的 SQL 语句如下：1DELETE FROM taggings WHERE noteId = 1 AND tagId IN (1, 2)查询查询当前 note 中所有满足条件的 tag：

const Op = Sequelize.Op
const tags = await note.getTags({
where: {
name: {
[Op.like]: 'tag%'
}
}
});
console.log(`Note has ${tags.length} tags`);

以上操作对应的 SQL 语句如下：

SELECT `tag`.`id`, `tag`.`name`, `tag`.`createdAt`, `tag`.`updatedAt`, `tagging`.`type` AS `tagging.type`, `tagging`.`createdAt` AS `tagging.createdAt`, `tagging`.`updatedAt` AS `tagging.updatedAt`, `tagging`.`noteId` AS `tagging.noteId`, `tagging`.`tagId` AS `tagging.tagId` FROM `tags` AS `tag`

INNER JOIN `taggings` AS `tagging`

ON `tag`.`id` = `tagging`.`tagId`

AND tagging.noteId = 1 WHERE (tag.name LIKE 'tag%');查询所有满足条件的 tag，同时获取每个 tag 所在的 note：

const tags = await Tag.findAll({
include: {
model: Note
}
});
// tag的notes可以通过tag.notes访问，关系模型可以通过tag.notes[0].tagging访问
console.log(`Has found ${tags.length} tags`);

以上操作对应的 SQL 语句如下：

SELECT `tag`.`id`, `tag`.`name`, `tag`.`createdAt`, `tag`.`updatedAt`, `notes`.`id` AS `notes.id`, `notes`.`title` AS `notes.title`, `notes`.`createdAt` AS `notes.createdAt`, `notes`.`updatedAt` AS `notes.updatedAt`, `notes->tagging`.`type` AS `notes.tagging.type`, `notes->tagging`.`createdAt` AS `notes.tagging.createdAt`, `notes->tagging`.`updatedAt` AS `notes.tagging.updatedAt`, `notes->tagging`.`noteId` AS `notes.tagging.noteId`, `notes->tagging`.`tagId` AS `notes.tagging.tagId` FROM `tags` AS `tag`

LEFT OUTER JOIN

( `taggings` AS `notes->tagging` INNER JOIN `notes` AS `notes`

`notes`.`id` = `notes->tagging`.`noteId`

) ON `tag`.`id` = `notes->tagging`.`tagId`;

首先是 notes 和 taggings 进行了一个inner join，选出 notes ，然后 tags 和刚 join 出的集合再做一次 left join，得到结果。查询所有满足条件的 note，同时获取每个 note 相关联的 tag：

const notes = await Note.findAll({
include: [
{
model: Tag // 支持tags设置查询条件
}
]
});

以上操作对应的 SQL 语句如下：

SELECT `note`.`id`, `note`.`title`, `note`.`createdAt`, `note`.`updatedAt`, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`createdAt` AS `tags.createdAt`, `tags`.`updatedAt` AS `tags.updatedAt`, `tags->tagging`.`type` AS `tags.tagging.type`, `tags->tagging`.`createdAt` AS `tags.tagging.createdAt`, `tags->tagging`.`updatedAt` AS `tags.tagging.updatedAt`, `tags->tagging`.`noteId` AS `tags.tagging.noteId`, `tags->tagging`.`tagId` AS `tags.tagging.tagId` FROM `notes` AS `note`

LEFT OUTER JOIN

( `taggings` AS `tags->tagging` INNER JOIN `tags` AS `tags`

`tags`.`id` = `tags->tagging`.`tagId`

)

ON `note`.`id` = `tags->tagging`.`noteId`;

首先是 tags 和 taggins 进行了一个 inner join，选出 tags，然后 notes 和刚 join 出的集合再做一次 left join，得到结果。

参考资源

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。