时间戳

首页 > 解决方案 > 使用连接更新以填补 MySQL 中的缺失值

mysql - 使用连接更新以填补 MySQL 中的缺失值

问题描述

我编写了这些行来填充邮政编码列中的空值。它应该根据位置列的值填写邮政编码列。

当我执行它时，它会无限期地运行并且没有给我想要的结果。有人可以告诉我我做错了什么吗？或者我可以使用什么其他方法来获得所需的结果？

UPDATE nycaccidents2020 a
JOIN nycaccidents2020 b
    USING(location)
SET a.`zip code` = b.`zip code`
WHERE a.`zip code` IS NULL AND b.`zip code` IS NOT NULL;

样品表：

地点 邮政编码
点 (-73.946556 40.8165) 10030
点 (-73.83376 40.8921) 10466
点 (-73.919914 40.6905) 11221
点 (-73.946556 40.8165) 无效的

标签： mysqlsqljoinsql-update

解决方案

该查询在技术上是正确的，但它可能会很慢，因为有许多行具有相同的位置，并且它正在与所有行连接，从而产生很大的叉积。您可以通过加入一个子查询来改进它，该子查询将它们减少到每个位置一行。

UPDATE nycaccidents2020 AS a
JOIN (
    SELECT DISTINCT location, `zip code`
    FROM nyaccidents2020
    WHERE `zip code` IS NOT NULL
) AS b USING (location)
SET a.`zip code` = b.`zip code`
WHERE a.`zip code` IS NULL

这也可能存在性能问题，因为子查询没有被索引。您可以通过创建一个临时表并对其进行索引来改进它。

CREATE TEMPORARY TABLE locations AS
    SELECT DISTINCT location, `zip code`
    FROM nyaccidents2020
    WHERE `zip code` IS NOT NULL;

ALTER TABLE locations ADD INDEX (location);

UPDATE nycaccidents2020 a
JOIN locations b USING(location)
SET a.`zip code` = b.`zip code`
WHERE a.`zip code` IS NULL

通过规范化数据可以避免整个问题。您可以有一个包含位置和邮政编码的单独表，并且事故表可以使用引用该表的外键。

推荐阅读