使用 Sequelize(ORM) + mysql2 操作 mysql

ORM 是关系对象映射，通过 ORM 框架，可以自动地把程序中的对象和数据库关联，只需要操作对象就可以修改数据库的数据，并且能轻易完成数据库的移植

安装

npm i sequelize
npm i mysql2

连接

连接数据库

创建 config 文件夹，在 config 中创建 db.js 文件用于放置数据库的配置项

// config/db.js
/**
 * mysql 数据库配置
 */
 
// 生产环境
let sqlConfig = {
    host: 'localhost',
    user: 'root',
    password: 'sql2008',
    database: 'books',
    port: 3306
}
 
// 本地环境
// process.env.NODE_ENV 取决于package.json里面的配置
if (process.env.NODE_ENV !== 'production') {
    let sqlConfig = {
        host: 'localhost',
        user: 'root',
        password: 'sql2008',
        database: 'books',
        port: 3306
    }
}
 
module.exports = sqlConfig

与 config 同级，创建一个db文件夹，用于存放数据操作，在 db 文件夹下创建 sequelize.js 文件，进行数据库的连接

// db/sequelize.js
const Sequelize = require('sequelize')
const {database,host,user,password} = require('../config/db')
// 连接数据库
const sequelize = new Sequelize(database,user,password,{
    host: host,
    port: 3306,
    dialect: 'mysql',// 数据库，默认是 mysql
    logging: null, // 日志，详情可以去官网看
    // 使用数据池
    pool: {
        max: 20,
        min: 1,
        acquire: 60000,
        idle: 10000
    },
})
 
module.exports = sequelize
// 测试连接
// sequelize.authenticate().then(_=>{
//     console.log('连接成功')
// }).catch(err=>{
//     console.log('连接失败'+err)
// })
// 关闭连接
// sequelize.close()

使用

定义模型

sequelize.define(moduleName,attributes,options)

根据数据库建立一个简单的模型

const { DataTypes } = require('sequelize')
const sequelize = require('../sequelize')
 
const BooksModel = sequelize.define('mybooks', {
    id: {
        type: DataTypes.INTEGER(4),
        allowNull: false,
        primaryKey: true,
        autoIncreament: true
    },
    name: {
        type: DataTypes.STRING(50),
        allowNull: false,
        defaultValue: '没有书名'
    },
    author: {
        type: DataTypes.STRING(50),
        defaultValue: '匿名',
    },
    price: {
        type: DataTypes.DECIMAL(10, 2),
        allowNull: false,
        defaultValue: 0.00
    },
    type: {
        type: DataTypes.ENUM('A', 'B', 'C', 'D', 'N'),
        allowNull: false,
        defaultValue: 'N'
    },
    note: {
        type: DataTypes.STRING,
        allowNull: true,
        // 自定义，根据数据库中其他字段的值计算出该字段
        // type: DataTypes.VIRTUAL,
        // get() {
        //     return this.id.toString() + '' + this.name + '€'
        // }
    }
}, {
    // 模型不需要添加事件戳，如果不设置，那么保存到数据库中会因为缺少 createAt 和 updateAt 而报错
    timestamps: false,
    // 该模式中会自动将表格名称修改为复数，有两种解决办法：1. 在配置项中设置：freezeTableName: true；2. 直接设置表格名称（在这里我设置成复数）
    tableName: 'mybooks'
})
module.exports = BooksModel

扩展 Model

const { DataTypes,Model } = require('sequelize')
const sequelize = require('../sequelize')
// 继承 Model 类
class UsersModel extends Model{}
 
UsersModel.init({
    id: {
        type: DataTypes.INTEGER(4),
        allowNull: false,
        primaryKey: true,
        autoIncreament: true
    },
    name: {
        type: DataTypes.STRING(50),
        allowNull: false,
        defaultValue: '没有名字'
    },
    age:{
        type: DataTypes.INTEGER(3),
        allowNull: false,
        defaultValue: 0
    },
    birthdate:{
        type: DataTypes.DATEONLY, 
        allowNull: true, 
        defaultValue: DataTypes.NOW()
    }
}, {
    // 连接实例
    sequelize:sequelize,
    // 模型名称
    moduleName:'UsersModel',
    timestamps: false,
    tableName: 'users'
})
module.exports = UsersModel

数据操作

与 model 文件夹同级，新建 service 文件夹，用于数据操作

新增

build()+save()

const addbooks = async(id,name,price,author,type) =>{
    let books = BookModel.build({
        id:id,
        name:name,
        price:price,
        author:author,
        type:type
    })
    books = await books.save()
    // 将模型实例直接返回会有很多问题，因为实例上有许多附加条件，可以使用 .toJSON() 或者 JSON.stringify() 操作后返回
    return books.toJSON()
}
addbooks(11,'静夜思',10,'李白','D').then(data=>console.log(data))

create()

• create()
  实际上就是 build() + save()，操作起来更快

// db/service/BookService.js
const BookModel = require('../model/BookModel');
const UserModel = require('../model/UserModel')
const { Op } = require('sequelize');
const sequelize = require('../db/sequelize')
 
const addbooks = async(id,name,price,author,type) =>{
    const books = await BookModel.create({
        id:id,
        name:name,
        price:price,
        author:author,
        type:type
    })
    return books.toJSON()
}
addbooks(11,'静夜思',10,'李白','D').then(data=>console.log(data))

删除

• destroy()

// where 条件匹配，用的非常多，可以去官网仔细看看
const deletebooks = async(name)=>{
    const books = await BookModel.destroy({
        where:{name:name}
    })
    return books
}
deletebooks('静夜思').then(data=>console.log(data))

修改

• update

const updatebooks = async()=>{
    const books = await BookModel.update({note:'价格便宜'},{
        where:{
            price:{
                // 查找价格小于 20 的
                [Op.lt]:20
            }
        }
    })
    return books;
}
updatebooks().then(data=>console.log(data))

操作符(官网上的)

where: {
    [Op.and]: [{ a: 5 }, { b: 6 }],            // (a = 5) AND (b = 6)
    [Op.or]: [{ a: 5 }, { b: 6 }],             // (a = 5) OR (b = 6)
    someAttribute: {
      // 基本
      [Op.eq]: 3,                              // = 3
      [Op.ne]: 20,                             // != 20
      [Op.is]: null,                           // IS NULL
      [Op.not]: true,                          // IS NOT TRUE
      [Op.or]: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)
 
      // 使用方言特定的列标识符 (以下示例中使用 PG):
      [Op.col]: 'user.organization_id',        // = "user"."organization_id"
 
      // 数字比较
      [Op.gt]: 6,                              // > 6
      [Op.gte]: 6,                             // >= 6
      [Op.lt]: 10,                             // < 10
      [Op.lte]: 10,                            // <= 10
      [Op.between]: [6, 10],                   // BETWEEN 6 AND 10
      [Op.notBetween]: [11, 15],               // NOT BETWEEN 11 AND 15
 
      // 其它操作符
 
      [Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)
 
      [Op.in]: [1, 2],                         // IN [1, 2]
      [Op.notIn]: [1, 2],                      // NOT IN [1, 2]
 
      [Op.like]: '%hat',                       // LIKE '%hat'
      [Op.notLike]: '%hat',                    // NOT LIKE '%hat'
      [Op.startsWith]: 'hat',                  // LIKE 'hat%'
      [Op.endsWith]: 'hat',                    // LIKE '%hat'
      [Op.substring]: 'hat',                   // LIKE '%hat%'
      [Op.iLike]: '%hat',                      // ILIKE '%hat' (不区分大小写) (仅 PG)
      [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (仅 PG)
      [Op.regexp]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (仅 MySQL/PG)
      [Op.notRegexp]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (仅 MySQL/PG)
      [Op.iRegexp]: '^[h|a|t]',                // ~* '^[h|a|t]' (仅 PG)
      [Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (仅 PG)
 
      [Op.any]: [2, 3],                        // ANY ARRAY[2, 3]::INTEGER (仅 PG)
      [Op.match]: Sequelize.fn('to_tsquery', 'fat & rat') // 匹配文本搜索字符串 'fat' 和 'rat' (仅 PG)
 
      // 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:
      [Op.like]: { [Op.any]: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']
 
    }
  }

查看

// 查找给定类别、给定价格区间内的书本，只要求输出 name，作者和 price，并按照 id 倒序输出
const getbook = async (price1, price2, type) => {
    const books = await BookModel.findAll({
        attributes: ['name', ['author', '作者'], 'price'],
        where: {
            [Op.and]: [
                {
                    price: {
                        [Op.gt]: price1,
                        [Op.lt]: price2,
                    }
                },
                { type: type }
            ]
        },
        order: [
            ['id', 'DESC']
        ]
    })
    return JSON.parse(JSON.stringify(books));
}
getbook(10, 50, 'A').then(data => console.log(data))

• 聚类统计问题

// 统计出给定类的书本价格之和，使用的是聚合函数 SUM 和 group
const getbooks = async (type) => {
    const books = await BookModel.findAll({
        attributes: [
            'type',
            [sequelize.fn('SUM', sequelize.col('price')), 'sum']
        ],
        group: 'type',
        where:{
            type:type
        }
    })
    return JSON.parse(JSON.stringify(books));
}
getbooks('A').then(data => console.log(data))

• 分页问题

// 给定页面大小和页码数，搜索符合要求的书本，默认按照 id 排序
const getbooks = async (pageNum, pageSize) => {
    const books = await BookModel.findAll({
        attributes: { exclude: ['note', 'type'] },
        // 跳过
        offset: (pageNum - 1) * pageSize,
        // 截取
        limit: pageSize,
    })
    return books
}
// 再调用函数外面使用 toJSON() 也是可以的
getbooks(3, 4).then(data => { data.length ? data.map(book => console.log(book.toJSON())) : console.log('No data') })

• 关联问题

// 使用 id join，成功！
UserModel.hasOne(BookModel,{foreignKey:'id'}) // 添加 UserModel 的外键为 id
BookModel.belongsTo(UserModel,{foreignKey:'id'}) // 添加 BookModel 的外键为 id
const getbooks = async () => {
    const books = await BookModel.findAll({
        include:{
            model:UserModel
        }
    })
    return JSON.parse(JSON.stringify(books))
}
 
getbooks().then(data => console.log(data))
 
// 使用 author 和 name join，失败！
UserModel.hasOne(BookModel,{foreignKey:'name'})
BookModel.belongsTo(UserModel,{foreignKey:'author'})
const getbooks = async () => {
    const books = await BookModel.findAll({
        include:{
            model:UserModel
        }
    })
    return JSON.parse(JSON.stringify(books))
}
 
getbooks().then(data => console.log(data))

上面的关联问题中，两表之间的 id 关联成功，两表之间的 author 和 name 关联失败，明明关联的数据值是相等的，为什么一个成功，一个失败呢？
其实只需要把 UsersModel 的主键改成 name 就可以关联成功，这有必要回忆一下主键和外键

• 主键：主键表示唯一标识一条记录，不能有重复的，不允许为空
• 外键：外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性
  也就是说 B 表的主键 b，就可以是 A 表的外键 b，A 表内的外键 b 在 A 表内可以有重复，但是在 B 表内必须是唯一值