登录
首页 >  数据库 >  MySQL

Sequelize 和 MySQL 对照

来源：SegmentFault

时间：2023-01-26 16:13:49 448浏览 收藏

有志者，事竟成！如果你在学习数据库，那么本文《Sequelize 和 MySQL 对照》，就很适合你！文章讲解的知识点主要包括MySQL、Node.js、javascript、sequelize，若是你对本文感兴趣，或者是想搞懂其中某个知识点，就请你继续往下看吧~

如果你觉得Sequelize的文档有点多、杂，不方便看，可以看看这篇。

在使用

$ 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) {
    console.log(e);
});

基本上，

var sequelize = new Sequelize(
    'sample', // 数据库名
    'root',   // 用户名
    'zuki',   // 用户密码
    {
        'dialect': 'mysql',  // 数据库使用mysql
        'host': 'localhost', // 数据库服务器ip
        'port': 3306,        // 数据库服务器端口
        'define': {
            // 字段以下划线（_）来分割（默认是驼峰命名风格）
            'underscored': true
        }
    }
);

定义单张表

var User = sequelize.define(
    // 默认表名（一般这里写单数），生成时会自动转换成复数形式
    // 这个值还会作为访问模型相关的模型时的属性名，所以建议用小写形式
    'user',
    // 字段定义（主键、created_at、updated_at默认包含，不用特殊定义）
    {
        'emp_id': {
            'type': Sequelize.CHAR(10), // 字段类型
            'allowNull': false,         // 是否允许为NULL
            'unique': true              // 字段是否UNIQUE
        },
        'nick': {
            'type': Sequelize.CHAR(10),
            'allowNull': false
        },
        'department': {
            'type': Sequelize.STRING(64),
            'allowNull': true
        }
    }
);


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;

几点说明：

  1. 建表

    var User = sequelize.define(
    'user',
    {
        'emp_id': {
            'type': Sequelize.CHAR(10), // 字段类型
            'allowNull': false,         // 是否允许为NULL
            'unique': true              // 字段是否UNIQUE
        },
        'nick': {
            'type': Sequelize.CHAR(10),
            'allowNull': false
        },
        'department': {
            'type': Sequelize.STRING(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
    }
);

    单表增删改查

    通过

    // 方法1：build后对象只存在于内存中，调用save后才操作db
var user = User.build({
    'emp_id': '1',
    'nick': '小红',
    'department': '技术部'
});
user = yield user.save();
console.log(user.get({'plain': true}));

// 方法2：直接操作db
var user = yield User.create({
    'emp_id': '2',
    'nick': '小明',
    'department': '技术部'
});
console.log(user.get({'plain': true}));

    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');

    // 方法1：操作对象属性（不会操作db），调用save后操作db
user.nick = '小白';
user = yield user.save();
console.log(user.get({'plain': true}));

// 方法2：直接update操作db
user = yield user.update({
    'nick': '小白白'
});
console.log(user.get({'plain': true}));

    UPDATE `users` 
SET `nick` = '小白白', `updated_at` = '2015-11-02 15:00:04' 
WHERE `id` = 1;

    更新操作时，

    // 方法1
user.emp_id = '33';
user.nick = '小白';
user = yield user.save({'fields': ['nick']});

// 方法2
user = yield user.update(
    {'emp_id': '33', 'nick': '小白'},
    {'fields': ['nick']}
});

    这样就只会更新

    yield user.destroy();

    DELETE FROM `users` WHERE `id` = 1;

    这里有个特殊的地方是，如果我们开启了

    var users = yield User.findAll();
console.log(users);

    SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at` FROM `users`;

    限制字段

    var users = yield User.findAll({
    'attributes': ['emp_id', 'nick']
});
console.log(users);

    SELECT `emp_id`, `nick` FROM `users`;

    字段重命名

    var users = yield User.findAll({
    'attributes': [
        'emp_id', ['nick', 'user_nick']
    ]
});
console.log(users);

    SELECT `emp_id`, `nick` AS `user_nick` FROM `users`;

    where子句

    var users = yield User.findAll({
    'where': {
        'id': [1, 2, 3],
        'nick': 'a',
        'department': null
    }
});
console.log(users);

    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;

    可以看到，

    var users = yield User.findAll({
    'where': {
        'id': {
            '$eq': 1,                // id = 1
            '$ne': 2,                // id != 2

            '$gt': 6,                // id > 6
            '$gte': 6,               // id >= 6

            '$lt': 10,               // id 

    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`

    这里我们发现，其实相等条件

    var users = yield User.findAll({
    'where': {
        '$and': [
            {'id': [1, 2]},
            {'nick': null}
        ]
    }
});

    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条件

    var users = yield User.findAll({
    'where': {
        '$or': [
            {'id': [1, 2]},
            {'nick': null}
        ]
    }
});

    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条件

    var users = yield User.findAll({
    'where': {
        '$not': [
            {'id': [1, 2]},
            {'nick': null}
        ]
    }
});

    SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at` 
FROM `users` AS `user` 
WHERE 
NOT (
    `user`.`id` IN (1, 2) AND 
    `user`.`nick` IS NULL
);
    转换规则

    我们这里做个总结。

    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 => ' '>=' + opv;
            case $lte => ' 'BETWEEN ' + opv[0] + ' AND ' + opv[1];
            case $notBetween => 'NOT BETWEEN ' + opv[0] + ' AND ' + opv[1];
            case $in => 'IN (' + opv.join(',') + ')';
            case $notIn => 'NOT IN (' + opv.join(',') + ')';
            case $like => 'LIKE ' + opv;
            case $notLike => 'NOT LIKE ' + opv;
        }
    }

}

    我们看一个复杂例子，基本上就是按上述流程来进行转换。

    var users = yield User.findAll({
    '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}
        ]
    }
});

    SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at` 
FROM `users` AS `user` 
WHERE 
    `user`.`id` IN (3, 4) 
AND 
NOT 
(
    `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
);
    排序

    var users = yield User.findAll({
    'order': [
        ['id', 'DESC'],
        ['nick']
    ]
});

    SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at` 
FROM `users` AS `user` 
ORDER BY `user`.`id` DESC, `user`.`nick`;
    分页

    var countPerPage = 20, currentPage = 5;
var users = yield User.findAll({
    'limit': countPerPage,                      // 每页多少条
    'offset': countPerPage * (currentPage - 1)  // 跳过多少条
});

    SELECT `id`, `emp_id`, `nick`, `department`, `created_at`, `updated_at` 
FROM `users` AS `user` 
LIMIT 80, 20;
    其他查询方法
    查询一条数据

    user = yield User.findById(1);

user = yield User.findOne({
    'where': {'nick': 'a'}
});

    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;
    查询并获取数量

    var result = yield User.findAndCountAll({
    'limit': 20,
    'offset': 0
});
console.log(result);

    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个

    var users = yield User.bulkCreate(
    [
        {'emp_id': 'a', 'nick': 'a'},
        {'emp_id': 'b', 'nick': 'b'},
        {'emp_id': 'c', 'nick': 'c'}
    ]
);

    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');

    这里需要注意，返回的

    var affectedRows = yield User.update(
    {'nick': 'hhhh'},
    {
        'where': {
            'id': [2, 3, 4]
        }
    }
);

    UPDATE `users` 
SET `nick`='hhhh',`updated_at`='2015-11-03 02:51:05' 
WHERE `id` IN (2, 3, 4);

    这里返回的

    var affectedRows = yield User.destroy({
    'where': {'id': [2, 3, 4]}
});

    DELETE FROM `users` WHERE `id` IN (2, 3, 4);

    这里返回的

    var User = sequelize.define('user',
    {
        'emp_id': {
            'type': Sequelize.CHAR(10),
            'allowNull': false,
            'unique': true
        }
    }
);
var Account = sequelize.define('account',
    {
        'email': {
            'type': Sequelize.CHAR(20),
            'allowNull': false
        }
    }
);

/* 
 * User的实例对象将拥有getAccount、setAccount、addAccount方法
 */
User.hasOne(Account);
/*
 * Account的实例对象将拥有getUser、setUser、addUser方法
 */
Account.belongsTo(User);

    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;

    可以看到，这种关系中外键

    var user = yield User.create({'emp_id': '1'});
var account = user.createAccount({'email': 'a'});
console.log(account.get({'plain': true}));

    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');

    var anotherAccount = yield Account.create({'email': 'b'});
console.log(anotherAccount);
anotherAccount = yield user.setAccount(anotherAccount);
console.log(anotherAccount);

    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;

    yield user.setAccount(null);

    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;

    这里的删除实际上只是“切断”关系，并不会真正的物理删除记录。

    var account = yield user.getAccount();
console.log(account);

    SELECT `id`, `email`, `created_at`, `updated_at`, `user_id` 
FROM `accounts` AS `account` 
WHERE (`account`.`user_id` = 1);

    这里就是调用

    var user = yield User.findById(1, {
    'include': [Account]
});
console.log(user.get({'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 } }
 */

    SELECT `user`.`id`, `user`.`emp_id`, `user`.`created_at`, `user`.`updated_at`, `account`.`id` AS `account.id`, `account`.`email` AS `account.email`, `account`.`created_at` AS `account.created_at`, `account`.`updated_at` AS `account.updated_at`, `account`.`user_id` AS `account.user_id` 
FROM `users` AS `user` LEFT OUTER JOIN `accounts` AS `account` 
ON `user`.`id` = `account`.`user_id` 
WHERE `user`.`id` = 1 LIMIT 1;

    可以看到，我们对2个表进行了一个外联接，从而在取

    var User = sequelize.define('user',
    {
        'emp_id': {
            'type': Sequelize.CHAR(10),
            'allowNull': false,
            'unique': true
        }
    }
);
var Note = sequelize.define('note',
    {
        'title': {
            'type': Sequelize.CHAR(64),
            'allowNull': false
        }
    }
);

/*
 * User的实例对象将拥有getNotes、setNotes、addNote、createNote、removeNote、hasNote方法
 */
User.hasMany(Note);
/*
 * Note的实例对象将拥有getUser、setUser、createUser方法
 */
Note.belongsTo(User);

    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;

    可以看到这种关系中，外键

    var user = yield User.create({'emp_id': '1'});
var note = yield user.createNote({'title': 'a'});
console.log(note);

    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');

    var user = yield User.create({'emp_id': '1'});
var note = yield Note.create({'title': 'b'});
yield user.addNote(note);

    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);

    // 为user增加note1、note2
var user = yield User.create({'emp_id': '1'});
var note1 = yield user.createNote({'title': 'a'});
var note2 = yield user.createNote({'title': 'b'});
// 先创建note3、note4
var note3 = yield Note.create({'title': 'c'});
var note4 = yield Note.create({'title': 'd'});
// user拥有的note更改为note3、note4
yield user.setNotes([note3, note4]);

    /* 省去了创建语句 */
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);

    var user = yield User.create({'emp_id': '1'});
var note1 = yield user.createNote({'title': 'a'});
var note2 = yield user.createNote({'title': 'b'});
yield user.setNotes([]);

    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);

    实际上，上面说到的“改”已经有“删”的操作了（去掉

    yield user.removeNote(note);

    UPDATE `notes` 
SET `user_id`=NULL,`updated_at`='2015-11-06 01:40:12' 
WHERE `user_id` = 1 AND `id` IN (1);
    查
    情况1

    查询

    var notes = yield user.getNotes({
    'where': {
        'title': {
            '$like': '%css%'
        }
    }
});
notes.forEach(function(note) {
    console.log(note);
});

    SELECT `id`, `title`, `created_at`, `updated_at`, `user_id` 
FROM `notes` AS `note` 
WHERE (`note`.`user_id` = 1 AND `note`.`title` LIKE '%a%');

    这种方法的

    var notes = yield Note.findAll({
    'include': [User],
    'where': {
        'title': {
            '$like': '%css%'
        }
    }
});
notes.forEach(function(note) {
    // note属于哪个user可以通过note.user访问
    console.log(note);
});

    SELECT `note`.`id`, `note`.`title`, `note`.`created_at`, `note`.`updated_at`, `note`.`user_id`, 
`user`.`id` AS `user.id`, `user`.`emp_id` AS `user.emp_id`, `user`.`created_at` AS `user.created_at`, `user`.`updated_at` AS `user.updated_at` 
FROM `notes` AS `note` LEFT OUTER JOIN `users` AS `user` 
ON `note`.`user_id` = `user`.`id`
WHERE `note`.`title` LIKE '%css%';

    这种方法，因为获取的主体是

    var users = yield User.findAll({
    'include': [Note],
    'where': {
        'created_at': {
            '$lt': new Date()
        }
    }
});
users.forEach(function(user) {
    // user的notes可以通过user.notes访问
    console.log(user); 
});

    SELECT `user`.`id`, `user`.`emp_id`, `user`.`created_at`, `user`.`updated_at`, 
`notes`.`id` AS `notes.id`, `notes`.`title` AS `notes.title`, `notes`.`created_at` AS `notes.created_at`, `notes`.`updated_at` AS `notes.updated_at`, `notes`.`user_id` AS `notes.user_id` 
FROM `users` AS `user` LEFT OUTER JOIN `notes` AS `notes` 
ON `user`.`id` = `notes`.`user_id`
WHERE `user`.`created_at`

    这种方法获取的主体是

    // 查询创建时间在今天之前的所有user，同时获取他们note的标题中含有关键字css的所有note
var users = yield User.findAll({
    'include': [
        {
            'model': Note,
            'where': {
                'title': {
                    '$like': '%css%'
                }
            }
        }
    ],
    'where': {
        'created_at': {
            '$lt': new Date()
        }
    }
});

    SELECT `user`.`id`, `user`.`emp_id`, `user`.`created_at`, `user`.`updated_at`, 
`notes`.`id` AS `notes.id`, `notes`.`title` AS `notes.title`, `notes`.`created_at` AS `notes.created_at`, `notes`.`updated_at` AS `notes.updated_at`, `notes`.`user_id` AS `notes.user_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`

    注意：当我们对

    var Note = sequelize.define('note',
    {
        'title': {
            'type': Sequelize.CHAR(64),
            'allowNull': false
        }
    }
);
var Tag = sequelize.define('tag',
    {
        'name': {
            'type': Sequelize.CHAR(64),
            'allowNull': false,
            'unique': true
        }
    }
);
var Tagging = sequelize.define('tagging',
    {
        'type': {
            'type': Sequelize.INTEGER(),
            'allowNull': false
        }
    }
);

// 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});

    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个外键

    var note = yield Note.create({'title': 'note'});
yield note.createTag({'name': 'tag'}, {'type': 0});

    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');

    var note = yield Note.create({'title': 'note'});
var tag = yield Tag.create({'name': 'tag'});
yield note.addTag(tag, {'type': 1});

    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');

    这种方法和上面的方法实际上是一样的。只是我们先手动

    var note = yield Note.create({'title': 'note'});
var tag1 = yield Tag.create({'name': 'tag1'});
var tag2 = yield Tag.create({'name': 'tag2'});
yield note.addTags([tag1, tag2], {'type': 2});

    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');

    这种方法可以进行批量添加。当执行

    // 先添加几个tag
var note = yield Note.create({'title': 'note'});
var tag1 = yield Tag.create({'name': 'tag1'});
var tag2 = yield Tag.create({'name': 'tag2'});
yield note.addTags([tag1, tag2], {'type': 2});
// 将tag改掉
var tag3 = yield Tag.create({'name': 'tag3'});
var tag4 = yield Tag.create({'name': 'tag4'});
yield note.setTags([tag3, tag4], {'type': 3});

    /* 前面添加部分的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');

    执行逻辑是，先将

    // 先添加几个tag
var note = yield Note.create({'title': 'note'});
var tag1 = yield Tag.create({'name': 'tag1'});
var tag2 = yield Tag.create({'name': 'tag2'});
var tag3 = yield Tag.create({'name': 'tag2'});
yield note.addTags([tag1, tag2, tag3], {'type': 2});

// 删除一个
yield note.removeTag(tag1);

// 全部删除
yield note.setTags([]);

    /* 删除一个 */
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);

    删除一个很简单，直接将关系表中的数据删除。

    全部删除时，首先需要查出关系表中

    var tags = yield note.getTags({
    //这里可以对tags进行where
});
tags.forEach(function(tag) {
    // 关系模型可以通过tag.tagging来访问
    console.log(tag);
});

    SELECT `tag`.`id`, `tag`.`name`, `tag`.`created_at`, `tag`.`updated_at`, 
`tagging`.`type` AS `tagging.type`, `tagging`.`created_at` AS `tagging.created_at`, `tagging`.`updated_at` AS `tagging.updated_at`, `tagging`.`tag_id` AS `tagging.tag_id`, `tagging`.`note_id` AS `tagging.note_id` 
FROM `tags` AS `tag` 
INNER JOIN `taggings` AS `tagging` 
ON 
`tag`.`id` = `tagging`.`tag_id` AND `tagging`.`note_id` = 1;

    可以看到这种查询，就是执行一个

    var tags = yield Tag.findAll({
    'include': [
        {
            'model': Note
            // 这里可以对notes进行where
        }
    ]
    // 这里可以对tags进行where
});
tags.forEach(function(tag) {
    // tag的notes可以通过tag.notes访问，关系模型可以通过tag.notes[0].tagging访问
    console.log(tag); 
});

    SELECT `tag`.`id`, `tag`.`name`, `tag`.`created_at`, `tag`.`updated_at`, 
`notes`.`id` AS `notes.id`, `notes`.`title` AS `notes.title`, `notes`.`created_at` AS `notes.created_at`, `notes`.`updated_at` AS `notes.updated_at`, 
`notes.tagging`.`type` AS `notes.tagging.type`, `notes.tagging`.`created_at` AS `notes.tagging.created_at`, `notes.tagging`.`updated_at` AS `notes.tagging.updated_at`, `notes.tagging`.`tag_id` AS `notes.tagging.tag_id`, `notes.tagging`.`note_id` AS `notes.tagging.note_id` 
FROM `tags` AS `tag` 
LEFT OUTER JOIN 
(
    `taggings` AS `notes.tagging` INNER JOIN `notes` AS `notes` 
    ON 
    `notes`.`id` = `notes.tagging`.`note_id`
) 
ON `tag`.`id` = `notes.tagging`.`tag_id`;

    这个查询就稍微有点复杂。首先是

    var notes = yield Note.findAll({
    'include': [
        {
            'model': Tag
            // 这里可以对tags进行where
        }
    ]
    // 这里可以对notes进行where
});
notes.forEach(function(note) {
    // note的tags可以通过note.tags访问，关系模型通过note.tags[0].tagging访问
    console.log(note);
});

    SELECT 
`note`.`id`, `note`.`title`, `note`.`created_at`, `note`.`updated_at`, 
`tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`created_at` AS `tags.created_at`, `tags`.`updated_at` AS `tags.updated_at`, 
`tags.tagging`.`type` AS `tags.tagging.type`, `tags.tagging`.`created_at` AS `tags.tagging.created_at`, `tags.tagging`.`updated_at` AS `tags.tagging.updated_at`, `tags.tagging`.`tag_id` AS `tags.tagging.tag_id`, `tags.tagging`.`note_id` AS `tags.tagging.note_id` 
FROM `notes` AS `note` 
LEFT OUTER JOIN 
(
    `taggings` AS `tags.tagging` INNER JOIN `tags` AS `tags` 
    ON 
    `tags`.`id` = `tags.tagging`.`tag_id`
) 
ON 
`note`.`id` = `tags.tagging`.`note_id`;

    这个查询和上面的查询类似。首先是

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

    其他没有涉及东西

    这篇文章已经够长了，但是其实我们还有很多没有涉及的东西，比如：聚合函数及查询（

    having
    、
    group by
    ）、模型的验证（
    validate
    ）、定义钩子（
    hooks
    ）、索引等等。

    这些主题下次再来写写。

    以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。

mysql MySQL Node.js javascript sequelize
声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表