python - MySQL 日期时间列 WHERE col IS NULL 失败

Question

我无法让我的非常基本的 SQL 查询工作，因为它返回 0 值，尽管事实上存在明显的空值

查询

SELECT
  *
FROM
  leads AS l
  JOIN closes c ON l.id = c.lead_id
 WHERE
  c.close_date IS NULL

DDL

CREATE TABLE closes
(
  id               INT AUTO_INCREMENT
    PRIMARY KEY,
  lead_id          INT          NOT NULL,
  close_date       DATETIME     NULL,
  close_type       VARCHAR(255) NULL,
  primary_agent    VARCHAR(255) NULL,
  price            FLOAT        NULL,
  gross_commission FLOAT        NULL,
  company_dollar   FLOAT        NULL,
  address          VARCHAR(255) NULL,
  city             VARCHAR(255) NULL,
  state            VARCHAR(10)  NULL,
  zip              VARCHAR(10)  NULL,
  CONSTRAINT closes_ibfk_1
  FOREIGN KEY (lead_id) REFERENCES leads (id)
)
  ENGINE = InnoDB;

CREATE INDEX lead_id
  ON closes (lead_id); 

我应该提到我正在使用 python web scraper 和 SQLAlchemy 插入数据。如果不抓取数据，它将是 None插入时。

这是 datagrip 的屏幕截图，显示行中存在空值

编辑

好吧，我继续对表中的一些条目运行以下命令，其中值已经是 <null>

UPDATE closes
  SET close_date = NULL
WHERE
  lead_id = <INTEGERVAL>
; 

现在有趣的是，当运行原始查询时，我实际上返回了我运行更新查询的 2 条记录(预期结果)。这会让我相信问题出在我的 SQLAlchemy 模型如何映射插入值上。

模型.py

class Close(db.Model, ItemMixin):
    __tablename__ = 'closes'

    id = db.Column(db.Integer, primary_key=True)
    lead_id = db.Column(db.Integer, db.ForeignKey('leads.id'), nullable=False)
    close_date = db.Column(db.DateTime)
    close_type = db.Column(db.String(255))
    primary_agent = db.Column(db.String(255))
    price = db.Column(db.Float)
    gross_commission = db.Column(db.Float)
    company_dollar = db.Column(db.Float)
    address = db.Column(db.String(255))
    city = db.Column(db.String(255))
    state = db.Column(db.String(10))
    zip = db.Column(db.String(10))

    def __init__(self, item):
        self.build_from_item(item)

    def build_from_item(self, item):
        for k, v in item.items():
            setattr(self, k, v) 

但我相当有信心该值是 python None如果没有从网站上删除任何值(value)。我的理解是 SQLAlchemy 会映射 None至NULL插入并考虑到 nullable=True是默认设置，可以在生成的 DDL 上看到，我仍然不知道为什么它看起来是 NULL但实际上它的行为并非如此。

编辑2

我认为会发生问题的唯一地方是我的蜘蛛实际上抓取数据并将其分配给 Item如下所示

关闭.py

# item['close_date'] = None at this point
try:
    item['close_date'] = arrow.get(item['close_date'], 'MMM D, YYYY').format('YYYY-MM-DD')
except ParserError as e:
    # Maybe item['close_date'] = None here?
    spider.logger.error(f'Parse error: {item["close_date"]} - {e}')

在我编写的 python 代码中，这似乎是出现问题的地方。但如果arrow.get抛出值 item['close_date'] 的异常应该仍然是None 。如果情况并非如此，即使是这样，也无法解释为什么记录值为 NULL甚至认为它的行为并非如此。

Answer 1

我猜您遇到的是连接问题，而不是 NULL 值问题。下面的查询为我返回 1 个结果。有关您的数据、用于查询的软件(我使用 SQL Yog 进行测试)以及适用版本的更多信息可能会有所帮助。

编辑

您可能遇到了 MySQL 的“零日期”问题。 https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html

MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

我更新了下面的 SQL 数据，在 INSERT 和 SELECT 的 WHERE 中包含零日期。

DROP TABLE IF EXISTS closes;
DROP TABLE IF EXISTS leads;

CREATE TABLE leads (
  id INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT  INTO leads(id) VALUES (1),(2),(3);

CREATE TABLE closes (
  id INT(11) NOT NULL AUTO_INCREMENT,
  lead_id INT(11) NOT NULL,
  close_date DATETIME DEFAULT NULL,
  close_type VARCHAR(255) DEFAULT NULL,
  primary_agent VARCHAR(255) DEFAULT NULL,
  price FLOAT DEFAULT NULL,
  gross_commission FLOAT DEFAULT NULL,
  company_dollar FLOAT DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  city VARCHAR(255) DEFAULT NULL,
  state VARCHAR(10) DEFAULT NULL,
  zip VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY lead_id (lead_id),
  CONSTRAINT closes_ibfk_1 FOREIGN KEY (lead_id) REFERENCES leads (id)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT  INTO closes(id,lead_id,close_date,close_type,primary_agent,price,gross_commission,company_dollar,address,city,state,zip) 
VALUES 
(1,3,'0000-00-0000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(2,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(3,2,'2018-01-09 17:01:44',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

SELECT
  *
FROM
  leads AS l
  JOIN closes c ON l.id = c.lead_id
 WHERE
  c.close_date IS NULL OR c.close_date = '0000-00-00';