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‘ });
相关推荐
Lingforme 2020-06-10
snowguy 2020-01-24
IBMRational 2019-12-28
chenjiazhu 2019-12-13
tlsmile 2019-12-01
一片荷叶 2019-09-07
dayi 2019-07-01
yanyongtao 2019-06-29
zhaojp0 2019-06-28
zhanbuquan 2019-06-28
Kingonion 2019-06-27
sqliang 2019-06-27
欢子 2019-06-26
欢子 2019-06-25
bluetears 2019-06-25
Yarnyogay 2019-06-25
都嘉浩的编程 2018-01-03
Kele0 2019-04-07
Danialzhou 2019-04-07