sequelize 操作 mysql 基础

官网：https://sequelize.org/v5/manual/querying.html

定义

model/Post.js

const Sequelize = require(‘sequelize‘);
const { INTEGER, STRING, DATE } = Sequelize;

module.exports = (app, database) => {
  database.define(‘post‘, {
    id: {
      type: INTEGER,
      primaryKey: true,
      autoIncrement: true,
      // 可以指定字段映射
      field: ‘id‘,
    },
    code: STRING,
    content: STRING,
    description: STRING,
    status: STRING,
    principals: Sequelize.JSON,
    createdAt: DATE,
    updatedAt: DATE,
  });
}

查

Post.findAll({
  // 分页
  limit: 10,
  offset: 0,
  // 列名，获取此表中的相关列
  attributes: [‘id‘, ‘code‘, ‘content‘],
  // 排序，跟着 id 降序排
  order: [‘id‘, ‘DESC‘],
  // where 条件
  where: {
    // authorId: 2
    authorId: {
      $in: [12, 13]    
    },
    status: ‘active‘
    
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

常用操作符号

const Op = Sequelize.Op

[Op.between]: [6, 10],     // BETWEEN 6 AND 10
[Op.in]: [1, 2],           // IN [1, 2]
[Op.like]: ‘%hat‘,         // LIKE ‘%hat‘

// 别名
$between: Op.between,
$in: Op.in,
$like: Op.like,

调用语句查，内容长度小于 6 个字符

Post.findAll({
  where: sequelize.where(sequelize.fn(‘char_length‘, sequelize.col(‘content‘)), 6)
});
// SELECT * FROM post WHERE char_length(content) = 6;

查 JSON

const options = {
  offset: page.offset,
  limit: page.limit,
  where: {
    
  },
  raw: true,
};


// JSON_CONTAINS mysqk 5.7 加入，可以查看文档
// https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
options.where = {
  [Op.and]: [
    options.where,
    Sequelize.fn(
      ‘JSON_CONTAINS‘,
      Sequelize.col(‘department‘),
        JSON.stringify({
        id: parseInt(params.departmentId),
      }),
    )
  ],
};

Post.findAndCountAll(options);

增

Post.create(params);

批量增

// 数组里有多项
const insertList = [{......}, {......}];
Post.bulkCreate(insertList);

删

Post.destroy({
  where: {
    id: 4
  }
});

// 或者先查后删
const data = await Post.findById(id);
if (!data) throw new Error(‘data not found‘);
return data.destroy();

改

Post.update({
  content: ‘112333‘,
}, {
  where: {
    id: 4,
  }
});

// 或者先查后改
const data = await Post.findById(4);
if (!data) throw new Error(‘data not found‘);
return data.update({
  content: ‘test‘
});