Sequelize自定义查询构造异常：生成SQL误将条件与NULL比较

我之前也踩过Sequelize语法的类似坑，你现在的情况很明确：想筛选status为read的消息，但生成的SQL莫名多了IS NULL，导致查询完全达不到预期效果。

先复盘下你当前的代码：

import sequelize from 'sequelize';
function getReadMsg(){
  const where = [sequelize.where( sequelize.literal('"Message"."status" = \'read\'') )];
  const attributes = { exclude: ['updated_at'] };
  const order = [['created_at', 'ASC']];
  return model.findAll({ where, attributes, order });
}

生成的错误SQL：

SELECT "id", "title", "body", "to", "from", "status", "created_at" FROM "Messages" AS "Message" WHERE ("Message"."status" = 'read' IS NULL) ORDER BY "Message"."created_at" ASC;

而你期望的正确SQL：

SELECT "id", "title", "body", "to", "from", "status", "created_at" FROM "Messages" AS "Message" WHERE ("Message"."status" = 'read') ORDER BY "Message"."created_at" ASC;

问题根源 #

你错误地把sequelize.literal()嵌套进了sequelize.where()，还放在了数组里。sequelize.where()的设计逻辑是需要至少两个参数（字段、操作符），当你只传一个literal表达式时，Sequelize会默认解析为判断该表达式是否为NULL，所以就生成了(xxx IS NULL)的错误条件。

三种正确的解决方案 #

方案1：直接用sequelize.literal作为WHERE条件

不需要额外套sequelize.where()，直接把literal表达式作为where的值即可：

import sequelize from 'sequelize';
function getReadMsg(){
  const where = sequelize.literal('"Message"."status" = \'read\'');
  const attributes = { exclude: ['updated_at'] };
  const order = [['created_at', 'ASC']];
  return model.findAll({ where, attributes, order });
}

方案2：使用Sequelize标准对象式WHERE条件（强烈推荐）

这是最符合ORM规范的写法，既简洁又安全，完全不需要手动写SQL片段：

function getReadMsg(){
  const where = { status: 'read' };
  const attributes = { exclude: ['updated_at'] };
  const order = [['created_at', 'ASC']];
  return model.findAll({ where, attributes, order });
}

Sequelize会自动帮你处理表名、字段名的引号，还能避免SQL注入风险。

方案3：正确使用sequelize.where方法

如果确实需要用sequelize.where处理复杂条件，要传入完整的字段、操作符和值：

import sequelize from 'sequelize';
function getReadMsg(){
  const where = sequelize.where(model.status, '=', 'read');
  const attributes = { exclude: ['updated_at'] };
  const order = [['created_at', 'ASC']];
  return model.findAll({ where, attributes, order });
}

以上三种方式都能生成你期望的正确SQL，优先选方案2，它更贴合Sequelize的设计初衷，维护起来也更省心。

内容的提问来源于stack exchange，提问作者wokoro douye samuel