博客园的写文章方式太不友好了,也不好看,可以访问自己写的静态页面查看新博客地址:
也可以直接在仓库的issue查看
概述
文章主要讨论最近在使用sequelize和mysql的一些总结,有些细节在sequelize文档上并没有,也是经验总结。同时,也是以前端的角度对mysql的思考,不一定是专业,但是是值得讨论思考的。还希望,大家可以提出问题和建议。
什么是sequelize
sequelize 是node下面的一个关系型数据库orm框架。提供了人性化的接口去写sql。简单说,就是告别 select * from person;
烦人的sql语句。采用 Person.findAll({});
这样形式是操作数据库。
内连接,左外连接,右外连接
- 内连接:两个表都满足条件的情况下,返回公有数据
- 左外连接: 左表所有数据,右表匹配不到的数据为null
- 右外连接:保证右表所有数据,左表匹配不到的数据null填充
include
include是sequelize实现连表查询的一个语法。至于具体细节,还请参考官方文档。这不是具体讨论的细节,我想记录的,是关于这样一个场景。
具体场景
这是一个很常见的场景,这里用购物车,商品,规格三种单元去描述。分别对应三张表Cart, Goods, Spec。 购物车和商品是多对多关系,可以理解成购物车是一个中间表,关联顾客和商品的中间表。但是在这个讨论中,顾客的层次可以忽略。所以,只要关注,Cart 有多个 Goods, Goods有多个Spec即可。 可以这样定义多对多关系(可以忽略代码, 作为参考)
Buyer.belongsToMany(Goods, {
through: {
model: Cart,
unique: false, // 取消联合主键的约定
},
foreignKey: 'buyerId',
});
Goods.belongsToMany(Buyer, {
through: {
model: Cart,
unique: false,
},
foreignKey: 'goodId', });
sequelize中,需要定义asscoiation 才能使用include关联。一个asscoiation包含一个关系加一个belongsTo 。其中,关系指的是hasMany(一对多), belongsToMany(多对多), hasOne(一对一)。假设上面的关系定义完毕。 就可以像这样获得所有的购物车:
await Cart.findAll({
attributes: ['id'], include: { attributes: [ 'id' ], model: Goods, include: { attributes: [ 'id' ], model: Spec } } })
对应转义sql语句(为了sql语句足够短,我加了attributes: [ 'id' ]的限制,只取id):
SELECT `cart`.`id`, `good`.`id` AS `good.id`, `good->specs`.`id` AS `good.specs.id` FROM `carts` AS `cart` LEFT OUTER JOIN `goods` AS `good` ON `cart`.`good_id` = `good`.`id` LEFT OUTER JOIN `specs` AS `good->specs` ON `good`.`id` = `good->specs`.`good_id`;
可以看到,默认情况下,include的方式是LEFT OUTER JOIN。就是左外级连的方式。 看关系图也知道,有一个Cart表有一个spec_id字段。这是一个必须的字段,对于购物车而言,它存储的最小单元应该是规格,而不是商品。对于没有规格的商品,这个字段可能为空,此时对应的最小单元应该是商品。 那我们为何不可以可以像这样去写这个include。
const r2 = await Cart.findAll({
attributes: ['id'],
include: [
{
attributes: [ 'id' ],
model: Goods,
},
{
attributes: [ 'id' ], model: Spec, } ] })
对应sql :
SELECT `cart`.`id`, `good`.`id` AS `good.id`, `spec`.`id` AS `spec.id` FROM `carts` AS `cart` LEFT OUTER JOIN `goods` AS `good` ON `cart`.`good_id` = `good`.`id` LEFT OUTER JOIN `specs` AS `spec` ON `cart`.`spec_id` = `spec`.`id`;
第二种方式执行效率会更快一点,测试过程,每多一层join便会有十倍的差距。至于第二种写法,按照sequelize的要求,必须定义关于Spec和Cart两张表的association。从mysql的角度,如果A->B, B->C (意思是A和B是一对多的关系)。这样的关系,定义A->C。这样的关系是不允许的。所以,关于sequelize的定义,可以这样去写:
Cart.belongsTo(Spec, {
foreignKey: 'specId', constraints: false, });
contstraints: fallse
的意思是说,不建立数据库约束和索引。这样如果通过Model生成对应数据库表。也就不会有数据库约束的问题。 正常情况下,sequelize会根据define里面定义的foreignKey和targetKey去定义on clause(上面例子中的ON cart
.spec_id
= spec
.id
;)。 如果默认情况无法满足,则要自己定义on clause。在sequelize中可以这样写(写于include中):
on: {
spec_id: {
[Sequelize.Op.eq]: Sequelize.col('spec.id'),
},
}
下面讲一下include中有条件的情况, 比如连接规格时候去掉已经删除掉的规格,则可以这样写:
const r2 = await Cart.findAll({
attributes: ['id'],
include: [
{
attributes: [ 'id' ],
model: Goods,
},
{
attributes: [ 'id' ], model: Spec, where: { isDelete: false } } ] })
其实直接看是没有问题的,但是如果细心看下面的sql语句,会发现本来默认的左外连接变成了内连接。
SELECT `cart`.`id`, `good`.`id` AS `good.id`, `spec`.`id` AS `spec.id` F AS `cart` LEFT OUTER JOIN `goods` AS `good` ON `cart`.`good_id` = `good`.`id` INNER JOIN `specs` AS `spec` ON `car` = `spec`.`id` AND `spec`.`is_delete` = false;
有时候内连接是不符合数据要求的,这个例子正好说明情况,对于购物车的记录,购物车应该完全展示出来。所以,sequelize默认是左外连接,如果你有条件,它会给你变成内连接。这么做是有道理的,因为情况只返回内外条件都满足的数据。为了能够保持外连接,需要用到required属性,这个文档有说明: 只需要把写上required: false
属性即可。
const r2 = await Cart.findAll({
attributes: ['id'],
include: [
{
attributes: [ 'id' ],
model: Goods,
},
{
attributes: [ 'id' ], model: Spec, required: false, where: { isDelete: false } } ]
SELECT `cart`.`id`, `good`.`id` AS `good.id`, `spec`.`id` AS `spec.id` FROM `carts` AS `cart` LEFT OUTER JOIN `goods` AS `good` ON `cart`.`good_id` = `good`.`id` LEFT OUTER JOIN `specs` AS `spec` ON `cart`.`spec_id` = `spec`.`id` AND `spec`.`is_delete` = false;
这样就可以了。
总结
为什么要讨论这样的include案例,因为除了简单的查询,数据库连表查询是很频繁的。虽然sequelize对于复杂情况无法定制。但实际上,上面例子应该已经适应了大多数场景。另一方面,减少include层级可以有效提高sql语句的执行效率。这些东西文档上可能没有很好的描述,应用过程中也是很有用的。