Sequelize学习经验（ORM框架）

什么是ORM？

简单的讲就是对SQL查询语句的封装，让我们可以用OOP的方式操作数据库，优雅的生成安全、可维护的SQL代码。直观上，是一种Model和SQL的映射关系。

模型常用类型定义

BOOLEAN  布尔类型
STRING 字符串类型，类似varchar
CHAR 定长字符串
TEXT 文本类型
INTEGER 整数类型
FLOAT
DOUBLE
DECIMAL
DATE
ENUM 枚举类型
NOW 当前时间戳
UUID UUID类型列，其默认值可以为UUIDV1或UUIDV4
UUIDV1  v1：是基于时间的uuid，通过计算当前时间戳、随机数和机器MAC地址得到。
UUIDV2  v4：根据随机数，或者伪随机数生成UUID
  uuid.v4().replace(/\-/g, '') //去除‘-’

更多看官方文档说明,或者这个老哥的博客

模型字段验证

id: {
        type: Sequelize.UUIDV4,
        primaryKey: true, // 主键
        allowNull: false,   //不为空
        // autoIncrement: true, //自增
        unique: true,
        defaultValue: NOW, //默认值的设置
        comment:'', // 说明
        validate: {
            is: ["^[a-z]+$",'i'],     // 只允许字母
            is: /^[a-z]+$/i,          // 只允许字母
            not: ["[a-z]",'i'],       // 不能使用字母
            isEmail: true,            // 检测邮箱格式 ([email protected])
            isUrl: true,              // 检查Url格式 (http://foo.com)
            isIP: true,               // 检查 IPv4 或 IPv6 格式
            isIPv4: true,             // 检查 IPv4
            isIPv6: true,             // 检查 IPv6
            isAlpha: true,            // 不能使用字母
            isAlphanumeric: true,     // 只允许字母数字字符
            isNumeric: true,          // 只能使用数字
            isInt: true,              // 只能是整数
            isFloat: true,            // 只能是浮点数
            isDecimal: true,          // 检查数字
            isLowercase: true,        // 检查小写字母
            isUppercase: true,        // 检查大写字母
            notNull: true,            // 不允许null
            isNull: true,             // 只能为null
            notEmpty: true,           // 不能空字符串
            equals: 'specific value', // 只能使用指定值
            contains: 'foo',          // 必须包含子字符串
            notIn: [['foo', 'bar']],  // 不能是数组中的任意一个值
            isIn: [['foo', 'bar']],   // 只能是数组中的任意一个值
            notContains: 'bar',       // 不能包含子字符串
            len: [2, 10],              // 值的长度必在 2 和 10 之间
            isUUID: 4,                // 只能是UUID
            isDate: true,             // 只能是日期字符串
            isAfter: "2011-11-05",    // 只能使用指定日期之后的时间
            isBefore: "2011-11-05",   // 只能使用指定日期之前的时间
            max: 23,                  // 允许的最大值
            min: 23,                  // 允许的最小值
            isArray: true,            // 不能使用数组
            isCreditCard: true,       // 检查是有效的信用卡

            // 也可以自定义验证:
            isEven: function(value) {
                if(parseInt(value) % 2 != 0) {
                throw new Error('Only even values are allowed!')
                // we also are in the model's context here, so this.otherField
                // would get the value of otherField if it existed
                }
            }
        },
        // 假设昵称后要加上 id 值
        get() {
            const id = this.getDataValue('id');
            return this.getDataValue('nickName') + '-' + id;
        },
        // set 假设数据库中存储的邮箱都要是大写的，可以在此处改写
        set(val) {
            this.setDataValue('email', val.toUpperCase());
        },
    },

详情看官方文档

模型使用

查询

• findById

await Role.findById(1);

• findByPk

await Role.findByPk(123)

• findOne

await Role.findOne({
        where: {
            level: 1,
        },
        
        attributes: ['id', 'role_id']
    });

• findAndCountAll

await Role.findAndCountAll({
        where: {
            level: 1,
        }
    });

• all()是 findAll 的别名

await Role.all({
        where: {
            level: {
                [Op.gt]: 1,

                // [Op.and]: {a: 5},           // AND (a = 5)
                // [Op.or]: [{a: 5}, {a: 6}],  // (a = 5 OR a = 6)
                // [Op.gt]: 6,                // id > 6
                // [Op.gte]: 6,               // id >= 6
                // [Op.lt]: 10,               // id < 10
                // [Op.lte]: 10,              // id <= 10
                // [Op.ne]: 20,               // id != 20
                // [Op.between]: [6, 10],     // BETWEEN 6 AND 10
                // [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
                // [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'
            }
        },
        limit: 3,
        // 注意 raw, 默认为 false, 这时候 Sequelize 会为搜索出的每一条数据生成一个 Role 实例，用于更新，删除等操作
        // 但当我们只想搜索出数据用于显示，并不想操作它，这个时候设置 raw: true 就会直接返回数据，而不会生成实例
        raw: true,
    });

• count

await Role.count();

• findAndCountAll

await Role.findAndCountAll({
     where: {
        title: {
          [Op.like]: 'foo%'
        }
     },
     offset: 10,
     limit: 2
  })

• findAndCountAll

await Role.findAndCountAll({
     where: {
        title: {
          [Op.like]: 'foo%'
        }
     },
     offset: 10,
     limit: 2
  })

• 查询结果重命名

const user = await User.findOne({
                where:{
                    id:userId
                },
                attributes:[['id','user_id'],['username','user_name']]
            })

• order

await Role.findAll({
    'order': "id DESC"
});

Role.findAll({
  order: [
    // 将转义用户名，并根据有效的方向参数列表验证DESC
    ['title', 'DESC'],

    // 将按最大值排序(age)
    sequelize.fn('max', sequelize.col('age')),

    // 将按最大顺序(age) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],

    // 将按 otherfunction 排序(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

    // 将使用模型名称作为关联的名称排序关联模型的 created_at。
    [Task, 'createdAt', 'DESC'],

    // Will order through an associated model's created_at using the model names as the associations' names.
    [Task, Project, 'createdAt', 'DESC'],

    // 将使用关联的名称由关联模型的created_at排序。
    ['Task', 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at using the names of the associations.
    ['Task', 'Project', 'createdAt', 'DESC'],

    // Will order by an associated model's created_at using an association object. (优选方法)
    [Subtask.associations.Task, 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at using association objects. (优选方法)
    [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],

    // Will order by an associated model's created_at using a simple association object.
    [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],

    // 嵌套关联模型的 created_at 简单关联对象排序
    [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
  ]
  
  // 将按年龄最大值降序排列
  order: sequelize.literal('max(age) DESC')

  // 按最年龄大值升序排列，当省略排序条件时默认是升序排列
  order: sequelize.fn('max', sequelize.col('age'))

  // 按升序排列是省略排序条件的默认顺序
  order: sequelize.col('age')
})

• limit

// 获取10个实例/行
Project.findAll({ limit: 10 })

• offset

// 跳过8个实例/行
Project.findAll({ offset: 8 })

• max

await Role.max('level', {
        where: {
            id: {
                [Op.gt]: 5,
            }
        }
    });

• sum

await Role.sum('level');

• COUNT,AVG,MAX,MIN,SUM等函数

//  可以使用sequelize.fn来执行这些函数
Role.findAll({
    attributes: [[sequelize.fn('COUNT', sequelize.col('*')), 'total_count']],
});
// SELECT COUNT(*) AS `total_count` ...

Role.findAll({
    attributes: [[sequelize.fn('COUNT', 1), 'total_count']],
});
// SELECT COUNT(1) AS `total_count` ...

• OR写法

Role.findAll({
    where: {
      [Op.or]: [{ id: 2 }, { id: 3 }],
    },
});
// ... where `role`.`id` = 2 OR `role`.`id` = 3
  
// 另一种写法
Role.findAll({
    where: {
      id: {
        [Op.or]: [2, 3],
      },
    },
});
// ... where `role`.`id` = 2 OR `role`.`id` = 3
 
// 不同字段的写法
Role.findAll({
    where: {
      [Op.or]: [{ id: 2 }, { level: 3 }],
    },
});
// ... where `role`.`id` = 2 OR `role`.`level` = 3

Role.findAll({
    where: {
      [Op.or]: [{ id: 2 }, { level: 3 }],
      [Op.and]: [{ role_id: { [Op.ne]: 10001 } }],
    },
});
// ... where (`role`.`id` = 2 OR `role`.`level` = 3) AND (`role`.`role_id` != 10001)

• findOrCreate

await Role.findOrCreate({
        where: {
            role_name: 'alex'
        },
        defaults: {
            role_id: 5,
            role_name: 'alex',
            level: 15,
        },
    });

新建

• 创建一个没有写入数据库的实例，用save命令存入数据库中

const role = Role.build({
    role_id: 1,
    role_name: 'name-1'
});
await role.save(); // 在需要的时候调用 save 命令，可以将数据存入数据库中

• 直接使用create命令直接创建一条写入数据库的数据

await Role.create({
    role_id: 2,
    role_name: 'name-2',
});

• 批量创建实例

const l = [];
for (let i = 0; i < 5; i++) {
    l.push({
        role_id: 1000 + i,
        role_name: `bulkname-${i}`,
        level: i + 5,
    });
}

const result = await Role.bulkCreate(l, {
    // 这样创建语句中只有 role_id 和 role_name，会忽略 level
    fields: ['role_id', 'role_name']
});

更新

• 查询同时更新

await Role.update({
    level: 4
}, {
    where: {}
});

• 先查询，后更新

const role = await Role.findOne({
    where: {}
});
await role.update({
    // 注意这个role不是原型Role
    level: 5
});

增减

• 增加值使用increment, 减少值使用decrement，用法相同

// level加5
const role = await Role.findById(1);
await role.increment('level', {
    by: 5
});

删除

• 批量删除

await Role.destroy({
    where: {
        id: 1
    }
});

sequelize.query（原生sql）

默认情况下，函数将返回两个参数 - 一个结果数组，以及一个包含元数据（受影响的行等）的对象。 请注意，由于这是一个原始查询，所以元数据（属性名称等）是具体的方言。 某些方言返回元数据 "within" 结果对象（作为数组上的属性）。 但是，将永远返回两个参数，但对于MSSQL和MySQL，它将是对同一对象的两个引用。

const result1 =  await mysql.query('SELECT id, img_url, url from carousel where status = 1');
 //返回值是两个相同元素组成的数组
 "result1": [
        [
            {
                "id": 1,
                "url": "/ClassDetail?id=4"
            },
            {
                "id": 4,
                "url": "/ClassDetail?id=2"
            }
        ],
        [
            {
                "id": 1,
                "url": "/ClassDetail?id=4"
            },
            {
                "id": 4,
                "url": "/ClassDetail?id=2"
            }
        ]
    ]

解决办法有两种

1. 传递一个查询类型来告诉后续如何格式化结果

const result= await mysql.query('SELECT id, img_url, url from carousel where status = 1', {
    replacements: {},
    type: mysql.QueryTypes.SELECT
  });

1. 传递模型

const Carousel = require('../../models/Carousel');

const result2 = await mysql.query('SELECT id, img_url, url from carousel where status = 1',{ model: Carousel });

替换

查询中的替换可以通过两种不同的方式完成：使用命名参数（以：开头），或者由？表示的未命名参数。 替换在options对象中传递。

• ?

如果传递一个数组, ? 将按照它们在数组中出现的顺序被替换

const banner2 = await mysql.query('SELECT id, img_url, url from carousel where id = ?', {
    replacements: [1,4,5,6],
    type: mysql.QueryTypes.SELECT,
});
// 返回的结果只有数组第一个元素下标对应的数据

• ：

const banner3 = await mysql.query('SELECT id, img_url, url from carousel where id in (:[1,4,5,6])', {
    replacements: {[1,4,5,6]},
    type: mysql.QueryTypes.SELECT,
});
// 返回结果为符合数组的数据

// 拓展：模糊查询
sequelize.query('SELECT * FROM users WHERE name LIKE :search_name ',
  { replacements: { search_name: 'ben%'  }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

注意：
type: mysql.QueryTypes.SELECT
进行不同的原生操作是， QueryTypes的取值也不相同，这里误以为都是select坑死

参考文档：
官方文档
Sequelize 中文文档 v4 - Raw queries - 原始查询
alex_my https://blog.csdn.net/alex_my...