mysql datetime列(col为null)失败

fcy6dtqo  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(451)

我无法让非常基本的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);

我应该提到,我是用pythonwebscraper和sqlalchemy插入数据的。如果数据没有被删除,它将被删除 None 插入时。
下面是datagrip的屏幕截图,显示行中的空值

编辑

好吧,我继续对表中已经存在值的一些条目运行下面的代码 <null> ```
UPDATE closes
SET close_date = NULL
WHERE
lead_id =
;

现在有趣的是,当运行原始查询时,我实际上返回了我运行更新查询的2条记录(预期结果)。这使我相信问题在于我的sqlalchemy模型如何Mapinsert上的值。

### 型号.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)
但我很有信心它的价值是一条巨蟒 `None` 如果没有价值是从网站上刮。我的理解是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` 即使它的行为不像它。
trnvg8h3

trnvg8h31#

我猜您对连接有问题,而不是空值。下面的查询为我返回1个结果。关于您的数据、用于查询的软件(我用sqlyog测试过)和适用版本的更多信息可能会有所帮助。
编辑
可能是mysql的“零日期”有问题。https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
mysql允许您将“0”值“0000-00-00”存储为“伪日期”。这在某些情况下比使用空值更方便,并且使用更少的数据和索引空间。若要禁用“0000-00-00”,请启用无零日期模式。
我已经更新了下面的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';

相关问题