mysql - 在 MySQL 中验证从一个表到另一个表的字段

Question

问题:

我有 1 个大约 5000 行 的表，名为 imported_cities

我有 1 个名为 postal_codes 的大约 800 000 行 表，其中包含邮政编码城市

我需要根据城市名称及其省份根据邮政编码表中的城市验证 imported_cities 中的每个不同城市。请参阅下面的表格结构。

如果它们完全匹配(是的，完全匹配。其余城市是手动验证的)我必须更新 imported_city 上的列 并且将 city from imported_cities 和 city from postal_codes(并排)输入到名为 imported_cities_equiv的第三个表中p>

我尝试过的:为表添加索引并在下面进行查询。 它需要永远... :(

explain SELECT DISTINCT ic.destinationCity, pc.city FROM (imported_cities ic, postalcodes pc)
WHERE LOWER(ic.destinationCity) = LOWER(pc.city) 

结果

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ip index NULL  company_city 478 NULL 4221 Using index; Using temporary 
1 SIMPLE pc index NULL city_prov 160 NULL 765407 Using where; Using index; Using join buffer (Block...

--

-- 表 postalcodes

的表结构

CREATE TABLE IF NOT EXISTS `postalcodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(11) NOT NULL,
  `city` varchar(50) NOT NULL,
  `province` varchar(50) NOT NULL,
  `provinceISO` varchar(2) NOT NULL,
  `latitude` decimal(17,13) NOT NULL,
  `longitude` decimal(17,13) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
  KEY `city_prov` (`city`,`provinceISO`)

--

-- 表 imported_cities

的表结构

CREATE TABLE IF NOT EXISTS `imported_cities` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `companyName` varchar(30) CHARACTER SET utf8 NOT NULL,
  `destinationCity` varchar(128) CHARACTER SET utf8 NOT NULL,
  `destinationProvince` varchar(20) CHARACTER SET utf8 NOT NULL,
  `equivCity` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `minAmount` decimal(6,2) NOT NULL
  PRIMARY KEY (`id`),
  KEY `company_city` (`companyName`,`destinationCity`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci     AUTO_INCREMENT=7933 ;

--

-- 表 imported_cities_equiv

的表结构

CREATE TABLE IF NOT EXISTS `imported_cities_equiv` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `imported_city` varchar(128) CHARACTER SET utf8 NOT NULL,
  `pc_city` varchar(128) CHARACTER SET utf8 NOT NULL,
  `province` varchar(20) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=149 ;

如有任何帮助或建议，我们将不胜感激。谢谢。

Answer 1

您要获取信息的查询是:

SELECT ip.*, (pc.city is not null) as exact match
FROM imported_prices ip left join
     postalcodes pc
     on LOWER(ip.destinationCity) = LOWER(pc.city)  and
        lower(ip.province) = lower(pc.province);

但是，这会产生非常糟糕的性能。摆脱 lower() 会有所帮助:

SELECT ip.*, (pc.city is not null) as exact match
FROM imported_prices ip left join
     postalcodes pc
     on(ip.destinationCity) =(pc.city)  and
       (ip.province) = (pc.province);

因为这样你就可以在 postalcodes(city, province) 上添加索引。

如果您不能使用 remove lower()，则更改表以添加新列并将小写值放入这些列中。然后在新列上构建索引并在连接中使用它们。