2023年6月23日发(作者：)

sequelize多条件_Sequelize和MySQL对照如果你觉得的⽂档有点多、杂，不⽅便看，可以看看这篇。在使⽤NodeJS来关系型操作数据库时，为了⽅便，通常都会选择⼀个合适的ORM(Object Relationship Model)框架。毕竟直接操作SQL⽐较繁琐，通过ORM框架，我们可以使⽤⾯向对象的⽅式来操作表。NodeJS社区有很多的ORM框架，我⽐较喜欢Sequelize，它功能丰富，可以⾮常⽅便的进⾏连表查询。这篇⽂章我们就来看看，Sequelize是如何在SQL之上进⾏抽象、封装，从⽽提⾼开发效率的。安装这篇⽂章主要使⽤MySQL、Sequelize、co来进⾏介绍。安装⾮常简单：$ npm install --save co$ npm install --save sequelize$ npm install --save mysql代码模板如下：var Sequelize = require('sequelize');var co = require('co');co(function* () {// code here}).catch(function(e) {(e);});基本上，Sequelize的操作都会返回⼀个Promise，在co的框架⾥⾯可以直接进⾏yield，⾮常⽅便。建⽴数据库连接var sequelize = new Sequelize('sample', // 数据库名'root', // ⽤户名'zuki', // ⽤户密码{'dialect': 'mysql', // 数据库使⽤mysql'host': 'localhost', // 数据库服务器ip'port': 3306, // 数据库服务器端⼝'define': {// 字段以下划线(_)来分割(默认是驼峰命名风格)'underscored': true}});定义单张表Sequelize：var User = (// 默认表名(⼀般这⾥写单数)，⽣成时会⾃动转换成复数形式// 这个值还会作为访问模型相关的模型时的属性名，所以建议⽤⼩写形式'user',// 字段定义(主键、created_at、updated_at默认包含，不⽤特殊定义){'emp_id': {'type': (10), // 字段类型'allowNull': false, // 是否允许为NULL'unique': true // 字段是否UNIQUE},'nick': {'type': (10),'allowNull': false},'department': {'type': (64),'allowNull': true}});SQL：CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment ,`emp_id` CHAR(10) NOT NULL UNIQUE,`nick` CHAR(10) NOT NULL,`department` VARCHAR(64),`created_at` DATETIME NOT NULL,`updated_at` DATETIME NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;⼏点说明：建表SQL会⾃动执⾏的意思是你主动调⽤sync的时候。类似这样：({force: true});(加force:true，会先删掉表后再建表)。我们也可以先定义好表结构，再来定义Sequelize模型，这时可以不⽤sync。两者在定义阶段没有什么关系，直到我们真正开始操作模型时，才会触及到表的操作，但是我们当然还是要尽量保证模型和表的同步(可以借助⼀些migration⼯具)。⾃动建表功能有风险，使⽤需谨慎。所有数据类型，请参考⽂档数据类型。模型还可以定义虚拟属性、类⽅法、实例⽅法，请参考⽂档：模型定义其他⼀些特殊定义如下所⽰：var User = ('user',{'emp_id': {'type': (10), // 字段类型'allowNull': false, // 是否允许为NULL'unique': true // 字段是否UNIQUE},'nick': {'type': (10),'allowNull': false},'department': {'type': (64),'allowNull': true}},{// ⾃定义表名'freezeTableName': true,'tableName': 'xyz_users',// 是否需要增加createdAt、updatedAt、deletedAt字段'timestamps': true,// 不需要createdAt字段'createdAt': false,// 将updatedAt字段改个名'updatedAt': 'utime'// 将deletedAt字段改名// 同时需要设置paranoid为true(此种模式下，删除数据时不会进⾏物理删除，⽽是设置deletedAt为当前时间'deletedAt': 'dtime','paranoid': true});单表增删改查通过Sequelize获取的模型对象都是⼀个DAO(Data Access Object)对象，这些对象会拥有许多操作数据库表的实例对象⽅法(⽐如：save、update、destroy等)，需要获取“⼲净”的JSON对象可以调⽤get({'plain': true})。通过模型的类⽅法可以获取模型对象(⽐如：findById、findAll等)。增Sequelize：// ⽅法1：build后对象只存在于内存中，调⽤save后才操作dbvar user = ({'emp_id': '1','nick': '⼩红','department': '技术部'});user = yield ();(({'plain': true}));// ⽅法2：直接操作dbvar user = yield ({'emp_id': '2','nick': '⼩明','department': '技术部'});(({'plain': true}));SQL：INSERT INTO `users`(`id`, `emp_id`, `nick`, `department`, `updated_at`, `created_at`)VALUES(DEFAULT, '1', '⼩红', '技术部', '2015-11-02 14:49:54', '2015-11-02 14:49:54');Sequelize会为主键id设置DEFAULT值来让数据库产⽣⾃增值，还将当前时间设置成了created_at和updated_at字段，⾮常⽅便。改Sequelize：// ⽅法1：操作对象属性(不会操作db)，调⽤save后操作 = '⼩⽩';user = yield ();(({'plain': true}));// ⽅法2：直接update操作dbuser = yield ({'nick': '⼩⽩⽩'});(({'plain': true}));SQL：UPDATE `users`SET `nick` = '⼩⽩⽩', `updated_at` = '2015-11-02 15:00:04'WHERE `id` = 1;更新操作时，Sequelize将将当前时间设置成了updated_at，⾮常⽅便。如果想限制更新属性的⽩名单，可以这样写：// ⽅法_id = '33'; = '⼩⽩';user = yield ({'fields': ['nick']});// ⽅法2user = yield ({'emp_id': '33', 'nick': '⼩⽩'},{'fields': ['nick']}});这样就只会更新nick字段，⽽emp_id会被忽略。这种⽅法在对表单提交过来的⼀⼤推数据中只更新某些属性的时候⽐较有⽤。删Sequelize：yield y();SQL：DELETE FROM `users` WHERE `id` = 1;这⾥有个特殊的地⽅是，如果我们开启了paranoid(偏执)模式，destroy的时候不会执⾏DELETE语句，⽽是执⾏⼀个UPDATE语句将deleted_at字段设置为当前时间(⼀开始此字段值为NULL)。我们可以使⽤y({force: true})来强制删除，从⽽执⾏DELETE语句进⾏物理删除。查查全部Sequelize：var users = yield l();(users);SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at` FROM `users`;限制字段Sequelize：var users = yield l({'attributes': ['emp_id', 'nick']});(users);SQL：SELECT `emp_id`, `nick` FROM `users`;字段重命名Sequelize：var users = yield l({'attributes': ['emp_id', ['nick', 'user_nick']]});(users);SQL：SELECT `emp_id`, `nick` AS `user_nick` FROM `users`;where⼦句Sequelize的where配置项基本上完全⽀持了SQL的where⼦句的功能，⾮常强⼤。我们⼀步步来进⾏介绍。基本条件Sequelize：var users = yield l({'where': {'id': [1, 2, 3],'nick': 'a','department': null}});(users);SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`WHERE`user`.`id` IN (1, 2, 3) AND`user`.`nick`='a' AND`user`.`department` IS NULL;可以看到，k: v被转换成了k = v，同时⼀个对象的多个k: v对被转换成了AND条件，即：k1: v1, k2: v2转换为k1 = v1 AND k2 = v2。这⾥有2个要点：如果v是null，会转换为IS NULL(因为SQL没有= NULL这种语法)如果v是数组，会转换为IN条件(因为SQL没有=[1,2,3]这种语法，况且也没数组这种类型)操作符操作符是对某个字段的进⼀步约束，可以有多个(对同⼀个字段的多个操作符会被转化为AND)。Sequelize：var users = yield l({'where': {'id': {'$eq': 1, // id = 1'$ne': 2, // id != 2'$gt': 6, // id > 6'$gte': 6, // id >= 6'$lt': 10, // id < 10'$lte': 10, // id <= 10'$between': [6, 10], // id BETWEEN 6 AND 10'$notBetween': [11, 15], // id NOT BETWEEN 11 AND 15'$in': [1, 2], // id IN (1, 2)'$notIn': [3, 4] // id NOT IN (3, 4)},'nick': {'$like': '%a%', // nick LIKE '%a%''$notLike': '%a' // nick NOT LIKE '%a'},'updated_at': {'$eq': null, // updated_at IS NULL'$ne': null // created_at IS NOT NULL}}});SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`WHERE(`user`.`id` = 1 AND`user`.`id` != 2 AND`user`.`id` > 6 AND`user`.`id` >= 6 AND`user`.`id` < 10 AND`user`.`id` <= 10 AND`user`.`id` BETWEEN 6 AND 10 AND`user`.`id` NOT BETWEEN 11 AND 15 AND`user`.`id` IN (1, 2) AND`user`.`id` NOT IN (3, 4))AND(`user`.`nick` LIKE '%a%' AND`user`.`nick` NOT LIKE '%a')AND(`user`.`updated_at` IS NULL AND`user`.`updated_at` IS NOT NULL);这⾥我们发现，其实相等条件k: v这种写法是操作符写法k: {$eq: v}的简写。⽽要实现不等条件就必须使⽤操作符写法k: {$ne: v}。条件上⾯我们说的条件查询，都是AND查询，Sequelize同时也⽀持OR、NOT、甚⾄多种条件的联合查询。AND条件Sequelize：var users = yield l({'where': {'$and': [{'id': [1, 2]},{'nick': null}]}});SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`WHERE(`user`.`id` IN (1, 2) AND`user`.`nick` IS NULL);OR条件Sequelize：var users = yield l({'where': {'$or': [{'id': [1, 2]},{'nick': null}]}});SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`WHERE(`user`.`id` IN (1, 2) OR`user`.`nick` IS NULL);NOT条件Sequelize：var users = yield l({'where': {'$not': [{'id': [1, 2]},{'nick': null}]}});SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`WHERENOT (`user`.`id` IN (1, 2) AND`user`.`nick` IS NULL);转换规则我们这⾥做个总结。Sequelize对where配置的转换规则的伪代码⼤概如下：function translate(where) {for (k, v of where) {if (k == 表字段) {// 先统⼀转为操作符形式if (v == 基本值) { // k: 'xxx'v = {'$eq': v};}if (v == 数组) { // k: [1, 2, 3]v = {'$in': v};}// 操作符转换for (opk, opv of v) {// op将opk转换对应的SQL表⽰=> k + op(opk, opv) + AND;}}// 逻辑操作符处理if (k == '$and') {for (item in v) {=> translate(item) + AND;}}if (k == '$or') {for (item in v) {=> translate(item) + OR;}}if (k == '$not') {NOT +for (item in v) {=> translate(item) + AND;}}}function op(opk, opv) {switch (opk) {case $eq => ('=' + opv) || 'IS NULL';case $ne => ('!=' + opv) || 'IS NOT NULL';case $gt => '>' + opv;case $lt => 'case $gte => '>=' + opv;case $lte => '<=' + opv;case $between => 'BETWEEN ' + opv[0] + ' AND ' + opv[1];case $notBetween => 'NOT BETWEEN ' + opv[0] + ' AND ' + opv[1];case $in => 'IN (' + (',') + ')';case $notIn => 'NOT IN (' + (',') + ')';case $like => 'LIKE ' + opv;case $notLike => 'NOT LIKE ' + opv;}}}我们看⼀个复杂例⼦，基本上就是按上述流程来进⾏转换。Sequelize：var users = yield l({'where': {'id': [3, 4],'$not': [{'id': {'$in': [1, 2]}},{'$or': [{'id': [1, 2]},{'nick': null}]}],'$and': [{'id': [1, 2]},{'nick': null}],'$or': [{'id': [1, 2]},{'nick': null}]}});SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`WHERE`user`.`id` IN (3, 4)ANDNOT(`user`.`id` IN (1, 2)AND(`user`.`id` IN (1, 2) OR `user`.`nick` IS NULL))AND(`user`.`id` IN (1, 2) AND `user`.`nick` IS NULL)AND(`user`.`id` IN (1, 2) OR `user`.`nick` IS NULL);排序Sequelize：var users = yield l({'order': [['id', 'DESC'],['nick']]});SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`ORDER BY `user`.`id` DESC, `user`.`nick`;分页Sequelize：var countPerPage = 20, currentPage = 5;var users = yield l({'limit': countPerPage, // 每页多少条'offset': countPerPage * (currentPage - 1) // 跳过多少条});SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`LIMIT 80, 20;其他查询⽅法查询⼀条数据Sequelize：user = yield Id(1);user = yield e({'where': {'nick': 'a'}});SQL：SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`WHERE `user`.`id` = 1 LIMIT 1;SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`WHERE `user`.`nick` = 'a' LIMIT 1;查询并获取数量Sequelize：var result = yield dCountAll({'limit': 20,'offset': 0});(result);SQL：SELECT count(*) AS `count` FROM `users` AS `user`;SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at`FROM `users` AS `user`LIMIT 20;这个⽅法会执⾏2个SQL，返回的result对象将包含2个字段：是数据总数，是符合查询条件的所有数据。批量操作插⼊Sequelize：var users = yield eate([{'emp_id': 'a', 'nick': 'a'},{'emp_id': 'b', 'nick': 'b'},{'emp_id': 'c', 'nick': 'c'}]);SQL：INSERT INTO `users`(`id`,`emp_id`,`nick`,`created_at`,`updated_at`)VALUES(NULL,'a','a','2015-11-03 02:43:30','2015-11-03 02:43:30'),(NULL,'b','b','2015-11-03 02:43:30','2015-11-03 02:43:30'),(NULL,'c','c','2015-11-03 02:43:30','2015-11-03 02:43:30');这⾥需要注意，返回的users数组⾥⾯每个对象的id值会是null。如果需要id值，可以重新取下数据。更新Sequelize：var affectedRows = yield ({'nick': 'hhhh'},{'where': {'id': [2, 3, 4]}});SQL：UPDATE `users`SET `nick`='hhhh',`updated_at`='2015-11-03 02:51:05'WHERE `id` IN (2, 3, 4);这⾥返回的affectedRows其实是⼀个数组，⾥⾯只有⼀个元素，表⽰更新的数据条数(看起来像是Sequelize的⼀个bug)。删除Sequelize：var affectedRows = yield y({'where': {'id': [2, 3, 4]}});SQL：DELETE FROM `users` WHERE `id` IN (2, 3, 4);这⾥返回的affectedRows是⼀个数字，表⽰删除的数据条数。关系关系⼀般有三种：⼀对⼀、⼀对多、多对多。Sequelize提供了清晰易⽤的接⼝来定义关系、进⾏表间的操作。当说到关系查询时，⼀般会需要获取多张表的数据。有建议⽤连表查询join的，有不建议的。我的看法是，join查询这种⿊科技在数据量⼩的情况下可以使⽤，基本没有什么影响，数据量⼤的时候，join的性能可能会是硬伤，应该尽量避免，可以分别根据索引取单表数据然后在应⽤层对数据进⾏join、merge。当然，查询时⼀定要分页，不要findAll。⼀对⼀模型定义Sequelize：var User = ('user',{'emp_id': {'type': (10),'allowNull': false,'unique': true}});var Account = ('account',{'email': {'type': (20),'allowNull': false}});/** User的实例对象将拥有getAccount、setAccount、addAccount⽅法*/(Account);/** Account的实例对象将拥有getUser、setUser、addUser⽅法*/sTo(User);SQL：CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment ,`emp_id` CHAR(10) NOT NULL UNIQUE,`created_at` DATETIME NOT NULL,`updated_at` DATETIME NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS `accounts` (`id` INTEGER NOT NULL auto_increment ,`email` CHAR(20) NOT NULL,`created_at` DATETIME NOT NULL,`updated_at` DATETIME NOT NULL,`user_id` INTEGER,PRIMARY KEY (`id`),FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;可以看到，这种关系中外键user_id加在了Account上。另外，Sequelize还给我们⽣成了外键约束。⼀般来说，外键约束在有些⾃⼰定制的数据库系统⾥⾯是禁⽌的，因为会带来⼀些性能问题。所以，建表的SQL⼀般就去掉约束，同时给外键加⼀个索引(加速查询)，数据的⼀致性就靠应⽤层来保证了。关系操作增Sequelize：var user = yield ({'emp_id': '1'});var account = Account({'email': 'a'});(({'plain': true}));SQL：INSERT INTO `users`(`id`,`emp_id`,`updated_at`,`created_at`)VALUES(DEFAULT,'1','2015-11-03 06:24:53','2015-11-03 06:24:53');INSERT INTO `accounts`(`id`,`email`,`user_id`,`updated_at`,`created_at`)VALUES(DEFAULT,'a',1,'2015-11-03 06:24:53','2015-11-03 06:24:53');SQL执⾏逻辑是：使⽤对应的的user_id作为外键在accounts表⾥插⼊⼀条数据。改Sequelize：var anotherAccount = yield ({'email': 'b'});(anotherAccount);anotherAccount = yield ount(anotherAccount);(anotherAccount);SQL：INSERT INTO `accounts`(`id`,`email`,`updated_at`,`created_at`)VALUES(DEFAULT,'b','2015-11-03 06:37:14','2015-11-03 06:37:14');SELECT `id`, `email`, `created_at`, `updated_at`, `user_id`FROM `accounts` AS `account` WHERE (`account`.`user_id` = 1);UPDATE `accounts` SET `user_id`=NULL,`updated_at`='2015-11-03 06:37:14' WHERE `id` = 1;UPDATE `accounts` SET `user_id`=1,`updated_at`='2015-11-03 06:37:14' WHERE `id` = 2;SQL执⾏逻辑是：插⼊⼀条account数据，此时外键user_id是空的，还没有关联user找出当前user所关联的account并将其user_id置为`NUL(为了保证⼀对⼀关系)设置新的acount的外键user_id为user的属性id，⽣成关系删Sequelize：yield ount(null);SQL：SELECT `id`, `email`, `created_at`, `updated_at`, `user_id`FROM `accounts` AS `account`WHERE (`account`.`user_id` = 1);UPDATE `accounts`SET `user_id`=NULL,`updated_at`='2015-11-04 00:11:35'WHERE `id` = 1;这⾥的删除实际上只是“切断”关系，并不会真正的物理删除记录。SQL执⾏逻辑是：找出user所关联的account数据将其外键user_id设置为NULL，完成关系的“切断”查Sequelize：var account = yield ount();(account);SQL：SELECT `id`, `email`, `created_at`, `updated_at`, `user_id`FROM `accounts` AS `account`WHERE (`account`.`user_id` = 1);这⾥就是调⽤user的getAccount⽅法，根据外键来获取对应的account。但是其实我们⽤⾯向对象的思维来思考应该是获取user的时候就能通过t的⽅式来访问account对象。这可以通过Sequelize的eager loading(急加载，和懒加载相反)来实现。eager loading的含义是说，取⼀个模型的时候，同时也把相关的模型数据也给我取过来(我很着急，不能按默认那种取⼀个模型就取⼀个模型的⽅式，我还要更多)。⽅法如下：Sequelize：var user = yield Id(1, {'include': [Account]});(({'plain': true}));/** 输出类似：{ id: 1,emp_id: '1',created_at: Tue Nov 03 2015 15:25:27 GMT+0800 (CST),updated_at: Tue Nov 03 2015 15:25:27 GMT+0800 (CST),account:{ id: 2,email: 'b',created_at: Tue Nov 03 2015 15:25:27 GMT+0800 (CST),updated_at: Tue Nov 03 2015 15:25:27 GMT+0800 (CST),user_id: 1 } }*/SQL：SELECT `user`.`id`, `user`.`emp_id`, `user`.`created_at`, `user`.`updated_at`, `account`.`id` AS ``,`account`.`email` AS ``, `account`.`created_at` AS `d_at`, `account`.`updated_at` AS`d_at`, `account`.`user_id` AS `_id`FROM `users` AS `user` LEFT OUTER JOIN `accounts` AS `account`ON `user`.`id` = `account`.`user_id`WHERE `user`.`id` = 1 LIMIT 1;可以看到，我们对2个表进⾏了⼀个外联接，从⽽在取user的同时也获取到了account。其他补充说明如果我们重复调⽤Account⽅法，实际上会在数据库⾥⾯⽣成多条user_id⼀样的数据，并不是真正的⼀对⼀。所以，在应⽤层保证⼀致性时，就需要我们遵循良好的编码约定。新增就⽤Account，更改就⽤ount。也可以给user_id加⼀个UNIQUE约束，在数据库层⾯保证⼀致性，这时就需要做好try/catch，发⽣插⼊异常的时候能够知道是因为插⼊了多个account。另外，我们上⾯都是使⽤user来对account进⾏操作。实际上反向操作也是可以的，这是因为我们定义了sTo(User)。在Sequelize⾥⾯定义关系时，关系的调⽤⽅会获得相关的“关系”⽅法，⼀般为了两边都能操作，会同时定义双向关系(这⾥双向关系指的是模型层⾯，并不会在数据库表中出现两个表都加上外键的情况，请放⼼)。⼀对多模型定义Sequelize：var User = ('user',{'emp_id': {'type': (10),'allowNull': false,'unique': true}});var Note = ('note',{'title': {'type': (64),'allowNull': false}});/** User的实例对象将拥有getNotes、setNotes、addNote、createNote、removeNote、hasNote⽅法*/y(Note);/** Note的实例对象将拥有getUser、setUser、createUser⽅法*/sTo(User);SQL：CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment ,`emp_id` CHAR(10) NOT NULL UNIQUE,`created_at` DATETIME NOT NULL,`updated_at` DATETIME NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS `notes` (`id` INTEGER NOT NULL auto_increment ,`title` CHAR(64) NOT NULL,`created_at` DATETIME NOT NULL,`updated_at` DATETIME NOT NULL,`user_id` INTEGER,PRIMARY KEY (`id`),FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;可以看到这种关系中，外键user_id加在了多的⼀端(notes表)。同时相关的模型也⾃动获得了⼀些⽅法。关系操作增⽅法1Sequelize：var user = yield ({'emp_id': '1'});var note = yield Note({'title': 'a'});(note);SQL：NSERT INTO `users`(`id`,`emp_id`,`updated_at`,`created_at`)VALUES(DEFAULT,'1','2015-11-03 23:52:05','2015-11-03 23:52:05');INSERT INTO `notes`(`id`,`title`,`user_id`,`updated_at`,`created_at`)VALUES(DEFAULT,'a',1,'2015-11-03 23:52:05','2015-11-03 23:52:05');SQL执⾏逻辑：使⽤user的主键id值作为外键直接在notes表⾥插⼊⼀条数据。⽅法2Sequelize：var user = yield ({'emp_id': '1'});var note = yield ({'title': 'b'});yield e(note);SQL：INSERT INTO `users`(`id`,`emp_id`,`updated_at`,`created_at`)VALUES(DEFAULT,'1','2015-11-04 00:02:56','2015-11-04 00:02:56');INSERT INTO `notes`(`id`,`title`,`updated_at`,`created_at`)VALUES(DEFAULT,'b','2015-11-04 00:02:56','2015-11-04 00:02:56');UPDATE `notes`SET `user_id`=1,`updated_at`='2015-11-04 00:02:56'WHERE `id` IN (1);SQL执⾏逻辑：插⼊⼀条note数据，此时该条数据的外键user_id为空使⽤user的属性id值再更新该条note数据，设置好外键，完成关系建⽴改Sequelize：// 为user增加note1、note2var user = yield ({'emp_id': '1'});var note1 = yield Note({'title': 'a'});var note2 = yield Note({'title': 'b'});// 先创建note3、note4var note3 = yield ({'title': 'c'});var note4 = yield ({'title': 'd'});// user拥有的note更改为note3、note4yield es([note3, note4]);SQL：/* 省去了创建语句 */SELECT `id`, `title`, `created_at`, `updated_at`, `user_id`FROM `notes` AS `note` WHERE `note`.`user_id` = 1;UPDATE `notes`SET `user_id`=NULL,`updated_at`='2015-11-04 12:45:12'WHERE `id` IN (1, 2);UPDATE `notes`SET `user_id`=1,`updated_at`='2015-11-04 12:45:12'WHERE `id` IN (3, 4);SQL执⾏逻辑：根据user的属性id查询所有相关的note数据将note1、note2的外键user_id置为NULL，切断关系将note3、note4的外键user_id置为user的属性id，完成关系建⽴这⾥为啥还要查出所有的note数据呢？因为我们需要根据传⼈setNotes的数组来计算出哪些note要切断关系、哪些要新增关系，所以就需要查出来进⾏⼀个计算集合的“交集”运算。删Sequelize：var user = yield ({'emp_id': '1'});var note1 = yield Note({'title': 'a'});var note2 = yield Note({'title': 'b'});yield es([]);SQL：SELECT `id`, `title`, `created_at`, `updated_at`, `user_id`FROM `notes` AS `note` WHERE `note`.`user_id` = 1;UPDATE `notes`SET `user_id`=NULL,`updated_at`='2015-11-04 12:50:08'WHERE `id` IN (1, 2);实际上，上⾯说到的“改”已经有“删”的操作了(去掉note1、note2的关系)。这⾥的操作是删掉⽤户的所有note数据，直接执⾏es([])即可。SQL执⾏逻辑：根据user的属性id查出所有相关的note数据将其外键user_id置为NULL，切断关系还有⼀个真正的删除⽅法，就是removeNote。如下所⽰：Sequelize：yield Note(note);SQL：UPDATE `notes`SET `user_id`=NULL,`updated_at`='2015-11-06 01:40:12'WHERE `user_id` = 1 AND `id` IN (1);查情况1查询user的所有满⾜条件的note数据。Sequelize：var notes = yield es({'where': {'title': {'$like': '%css%'}}});h(function(note) {(note);});SQL：SELECT `id`, `title`, `created_at`, `updated_at`, `user_id`FROM `notes` AS `note`WHERE (`note`.`user_id` = 1 AND `note`.`title` LIKE '%a%');这种⽅法的SQL很简单，直接根据user的id值来查询满⾜条件的note即可。情况2查询所有满⾜条件的note，同时获取note属于哪个user。Sequelize：var notes = yield l({'include': [User],'where': {'title': {'$like': '%css%'}}});h(function(note) {// note属于哪个user可以通过访问(note);});SQL：SELECT `note`.`id`, `note`.`title`, `note`.`created_at`, `note`.`updated_at`, `note`.`user_id`,`user`.`id` AS ``, `user`.`emp_id` AS `_id`, `user`.`created_at` AS `d_at`, `user`.`updated_at`AS `d_at`FROM `notes` AS `note` LEFT OUTER JOIN `users` AS `user`ON `note`.`user_id` = `user`.`id`WHERE `note`.`title` LIKE '%css%';这种⽅法，因为获取的主体是note，所以将notes去left join了users。情况3查询所有满⾜条件的user，同时获取该user所有满⾜条件的note。Sequelize：var users = yield l({'include': [Note],'where': {'created_at': {'$lt': new Date()}}});h(function(user) {// user的notes可以通过访问(user);});SQL：SELECT `user`.`id`, `user`.`emp_id`, `user`.`created_at`, `user`.`updated_at`,`notes`.`id` AS ``, `notes`.`title` AS ``, `notes`.`created_at` AS `d_at`, `notes`.`updated_at`AS `d_at`, `notes`.`user_id` AS `_id`FROM `users` AS `user` LEFT OUTER JOIN `notes` AS `notes`ON `user`.`id` = `notes`.`user_id`WHERE `user`.`created_at` < '2015-11-05 01:51:35';这种⽅法获取的主体是user，所以将users去left join了notes。⼀点补充关于eager loading我想再啰嗦⼏句。include⾥⾯传递的是去取相关模型，默认是取全部，我们也可以再对这个模型进⾏⼀层过滤。像下⾯这样：Sequelize：// 查询创建时间在今天之前的所有user，同时获取他们note的标题中含有关键字css的所有notevar users = yield l({'include': [{'model': Note,'where': {'title': {'$like': '%css%'}}}],'where': {'created_at': {'$lt': new Date()}}});SQL：SELECT `user`.`id`, `user`.`emp_id`, `user`.`created_at`, `user`.`updated_at`,`notes`.`id` AS ``, `notes`.`title` AS ``, `notes`.`created_at` AS `d_at`, `notes`.`updated_at`AS `d_at`, `notes`.`user_id` AS `_id`FROM `users` AS `user` INNER JOIN `notes` AS `notes`ON `user`.`id` = `notes`.`user_id` AND `notes`.`title` LIKE '%css%'WHERE `user`.`created_at` < '2015-11-05 01:58:31';注意：当我们对include的模型加了where过滤时，会使⽤inner join来进⾏查询，这样保证只有那些拥有标题含有css关键词note的⽤户才会返回。多对多关系在多对多关系中，必须要额外⼀张关系表来将2个表进⾏关联，这张表可以是单纯的⼀个关系表，也可以是⼀个实际的模型(含有⾃⼰的额外属性来描述关系)。我⽐较喜欢⽤⼀个模型的⽅式，这样⽅便以后做扩展。模型定义Sequelize：var Note = ('note',{'title': {'type': (64),'allowNull': false}});var Tag = ('tag',{'name': {'type': (64),'allowNull': false,'unique': true}});var Tagging = ('tagging',{'type': {'type': R(),'allowNull': false}});// Note的实例拥有getTags、setTags、addTag、addTags、createTag、removeTag、hasTag⽅法sToMany(Tag, {'through': Tagging});// Tag的实例拥有getNotes、setNotes、addNote、addNotes、createNote、removeNote、hasNote⽅法sToMany(Note, {'through': Tagging});SQL：CREATE TABLE IF NOT EXISTS `notes` (`id` INTEGER NOT NULL auto_increment ,`title` CHAR(64) NOT NULL,`created_at` DATETIME NOT NULL,`updated_at` DATETIME NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS `tags` (`id` INTEGER NOT NULL auto_increment ,`name` CHAR(64) NOT NULL UNIQUE,`created_at` DATETIME NOT NULL,`updated_at` DATETIME NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS `taggings` (`type` INTEGER NOT NULL,`created_at` DATETIME NOT NULL,`updated_at` DATETIME NOT NULL,`tag_id` INTEGER ,`note_id` INTEGER ,PRIMARY KEY (`tag_id`, `note_id`),FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (`note_id`) REFERENCES `notes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;可以看到，多对多关系中单独⽣成了⼀张关系表，并设置了2个外键tag_id和note_id来和tags和notes进⾏关联。关于关系表的命名，我⽐较喜欢使⽤动词，因为这张表是⽤来表⽰两张表的⼀种联系，⽽且这种联系多数时候伴随着⼀种动作。⽐如：⽤户收藏商品(collecting)、⽤户购买商品(buying)、⽤户加⼊项⽬(joining)等等。增⽅法1Sequelize：var note = yield ({'title': 'note'});yield Tag({'name': 'tag'}, {'type': 0});SQL：INSERT INTO `notes`(`id`,`title`,`updated_at`,`created_at`)VALUES(DEFAULT,'note','2015-11-06 02:14:38','2015-11-06 02:14:38');INSERT INTO `tags`(`id`,`name`,`updated_at`,`created_at`)VALUES(DEFAULT,'tag','2015-11-06 02:14:38','2015-11-06 02:14:38');INSERT INTO `taggings`(`tag_id`,`note_id`,`type`,`created_at`,`updated_at`)VALUES(1,1,0,'2015-11-06 02:14:38','2015-11-06 02:14:38');SQL执⾏逻辑：在notes表插⼊记录在tags表中插⼊记录使⽤对应的值设置外键tag_id和note_id以及关系模型本⾝需要的属性(type: 0)在关系表tagging中插⼊记录关系表本⾝需要的属性，通过传递⼀个额外的对象给设置⽅法来实现。⽅法2Sequelize：var note = yield ({'title': 'note'});var tag = yield ({'name': 'tag'});yield (tag, {'type': 1});SQL：INSERT INTO `notes`(`id`,`title`,`updated_at`,`created_at`)VALUES(DEFAULT,'note','2015-11-06 02:20:52','2015-11-06 02:20:52');INSERT INTO `tags`(`id`,`name`,`updated_at`,`created_at`)VALUES(DEFAULT,'tag','2015-11-06 02:20:52','2015-11-06 02:20:52');INSERT INTO `taggings`(`tag_id`,`note_id`,`type`,`created_at`,`updated_at`)VALUES(1,1,1,'2015-11-06 02:20:52','2015-11-06 02:20:52');这种⽅法和上⾯的⽅法实际上是⼀样的。只是我们先⼿动create了⼀个Tag模型。⽅法3Sequelize：var note = yield ({'title': 'note'});var tag1 = yield ({'name': 'tag1'});var tag2 = yield ({'name': 'tag2'});yield s([tag1, tag2], {'type': 2});SQL：INSERT INTO `notes`(`id`,`title`,`updated_at`,`created_at`)VALUES(DEFAULT,'note','2015-11-06 02:25:18','2015-11-06 02:25:18');INSERT INTO `tags`(`id`,`name`,`updated_at`,`created_at`)VALUES(DEFAULT,'tag1','2015-11-06 02:25:18','2015-11-06 02:25:18');INSERT INTO `tags`(`id`,`name`,`updated_at`,`created_at`)VALUES(DEFAULT,'tag2','2015-11-06 02:25:18','2015-11-06 02:25:18');INSERT INTO `taggings` (`tag_id`,`note_id`,`type`,`created_at`,`updated_at`)VALUES(1,1,2,'2015-11-06 02:25:18','2015-11-06 02:25:18'),(2,1,2,'2015-11-06 02:25:18','2015-11-06 02:25:18');这种⽅法可以进⾏批量添加。当执⾏addTags时，实际上就是设置好对应的外键及关系模型本⾝的属性，然后在关系表中批量的插⼊数据。改Sequelize：// 先添加⼏个tagvar note = yield ({'title': 'note'});var tag1 = yield ({'name': 'tag1'});var tag2 = yield ({'name': 'tag2'});yield s([tag1, tag2], {'type': 2});// 将tag改掉var tag3 = yield ({'name': 'tag3'});var tag4 = yield ({'name': 'tag4'});yield s([tag3, tag4], {'type': 3});SQL：/* 前⾯添加部分的sql，和上⾯⼀样*/INSERT INTO `notes`(`id`,`title`,`updated_at`,`created_at`)VALUES(DEFAULT,'note','2015-11-06 02:25:18','2015-11-06 02:25:18');INSERT INTO `tags`(`id`,`name`,`updated_at`,`created_at`)VALUES(DEFAULT,'tag1','2015-11-06 02:25:18','2015-11-06 02:25:18');INSERT INTO `tags`(`id`,`name`,`updated_at`,`created_at`)VALUES(DEFAULT,'tag2','2015-11-06 02:25:18','2015-11-06 02:25:18');INSERT INTO `taggings`(`tag_id`,`note_id`,`type`,`created_at`,`updated_at`)VALUES(1,1,2,'2015-11-06 02:25:18','2015-11-06 02:25:18'),(2,1,2,'2015-11-06 02:25:18','2015-11-06 02:25:18');/* 更改部分的sql */INSERT INTO `tags`(`id`,`name`,`updated_at`,`created_at`)VALUES(DEFAULT,'tag3','2015-11-06 02:29:55','2015-11-06 02:29:55');INSERT INTO `tags`(`id`,`name`,`updated_at`,`created_at`)VALUES(DEFAULT,'tag4','2015-11-06 02:29:55','2015-11-06 02:29:55');/* 先删除关系 */DELETE FROM `taggings`WHERE `note_id` = 1 AND `tag_id` IN (1, 2);/* 插⼊新关系 */INSERT INTO `taggings`(`tag_id`,`note_id`,`type`,`created_at`,`updated_at`)VALUES(3,1,3,'2015-11-06 02:29:55','2015-11-06 02:29:55'),(4,1,3,'2015-11-06 02:29:55','2015-11-06 02:29:55');执⾏逻辑是，先将tag1、tag2在关系表中的关系删除，然后再将tag3、tag4对应的关系插⼊关系表。删Sequelize：// 先添加⼏个tagvar note = yield ({'title': 'note'});var tag1 = yield ({'name': 'tag1'});var tag2 = yield ({'name': 'tag2'});var tag3 = yield ({'name': 'tag2'});yield s([tag1, tag2, tag3], {'type': 2});// 删除⼀个yield Tag(tag1);// 全部删除yield s([]);SQL：/* 删除⼀个 */DELETE FROM `taggings` WHERE `note_id` = 1 AND `tag_id` IN (1);/* 删除全部 */SELECT `type`, `created_at`, `updated_at`, `tag_id`, `note_id`FROM `taggings` AS `tagging`WHERE `tagging`.`note_id` = 1;DELETE FROM `taggings` WHERE `note_id` = 1 AND `tag_id` IN (2, 3);删除⼀个很简单，直接将关系表中的数据删除。全部删除时，⾸先需要查出关系表中note_id对应的所有数据，然后⼀次删掉。查情况1查询note所有满⾜条件的tag。Sequelize：var tags = yield s({//这⾥可以对tags进⾏where});h(function(tag) {// 关系模型可以通过g来访问(tag);});SQL：SELECT `tag`.`id`, `tag`.`name`, `tag`.`created_at`, `tag`.`updated_at`,`tagging`.`type` AS ``, `tagging`.`created_at` AS `d_at`, `tagging`.`updated_at` AS`d_at`, `tagging`.`tag_id` AS `_id`, `tagging`.`note_id` AS `_id`FROM `tags` AS `tag`INNER JOIN `taggings` AS `tagging`ON`tag`.`id` = `tagging`.`tag_id` AND `tagging`.`note_id` = 1;可以看到这种查询，就是执⾏⼀个inner join。情况2查询所有满⾜条件的tag，同时获取每个tag所在的note。Sequelize：var tags = yield l({'include': [{'model': Note// 这⾥可以对notes进⾏where}]// 这⾥可以对tags进⾏where});h(function(tag) {// tag的notes可以通过访问，关系模型可以通过[0].tagging访问(tag);});SQL：SELECT `tag`.`id`, `tag`.`name`, `tag`.`created_at`, `tag`.`updated_at`,`notes`.`id` AS ``, `notes`.`title` AS ``, `notes`.`created_at` AS `d_at`, `notes`.`updated_at`AS `d_at`,`g`.`type` AS ``, `g`.`created_at` AS `d_at`,`g`.`updated_at` AS `d_at`, `g`.`tag_id` AS `_id`,`g`.`note_id` AS `_id`FROM `tags` AS `tag`LEFT OUTER JOIN(`taggings` AS `g` INNER JOIN `notes` AS `notes`ON`notes`.`id` = `g`.`note_id`)ON `tag`.`id` = `g`.`tag_id`;这个查询就稍微有点复杂。⾸先是notes和taggings进⾏了⼀个inner join，选出notes；然后tags和刚join出的集合再做⼀次left join，得到结果。情况3查询所有满⾜条件的note，同时获取每个note所有满⾜条件的tag。Sequelize：var notes = yield l({'include': [{'model': Tag// 这⾥可以对tags进⾏where}]// 这⾥可以对notes进⾏where});h(function(note) {// note的tags可以通过访问，关系模型通过[0].tagging访问(note);});SQL：SELECT`note`.`id`, `note`.`title`, `note`.`created_at`, `note`.`updated_at`,`tags`.`id` AS ``, `tags`.`name` AS ``, `tags`.`created_at` AS `d_at`, `tags`.`updated_at` AS`d_at`,`g`.`type` AS ``, `g`.`created_at` AS `d_at`,`g`.`updated_at` AS `d_at`, `g`.`tag_id` AS `_id`,`g`.`note_id` AS `_id`FROM `notes` AS `note`LEFT OUTER JOIN(`taggings` AS `g` INNER JOIN `tags` AS `tags`ON`tags`.`id` = `g`.`tag_id`)ON`note`.`id` = `g`.`note_id`;这个查询和上⾯的查询类似。⾸先是tags和taggins进⾏了⼀个inner join，选出tags；然后notes和刚join出的集合再做⼀次left join，得到结果。其他没有涉及东西这篇⽂章已经够长了，但是其实我们还有很多没有涉及的东西，⽐如：聚合函数及查询(having、group by)、模型的验证(validate)、定义钩⼦(hooks)、索引等等。这些主题下次再来写写。