MySQL——唯一和不同的值不起作用

Question

我正在尝试从所有数据库表中获取我在网络服务器上运行的脚本的电子邮件。不幸的是，某些表中存在一些重复的电子邮件。这应该是一个简单的解决方案，使用 SELECT DISTINCT、INSERT IGNORE 等，但似乎没有任何效果。

这是我当前的查询:

CREATE TEMPORARY TABLE `all_emails` (
     id int(10) unsigned NOT NULL AUTO_INCREMENT,
     email VARCHAR(80) NOT NULL,
     ip_address VARCHAR(20) NOT NULL,
     PRIMARY KEY (id),
     UNIQUE INDEX(email)
);

INSERT IGNORE INTO `all_emails` (email, ip_address)  
SELECT email,IP_address
  FROM `table_1_emails`
 WHERE unsubscribed_at IS NULL;

INSERT IGNORE INTO `all_emails` (email, ip_address)  
SELECT email,ip_address
  FROM `table_2_emails`
 WHERE unsubscribed_at IS NULL;

INSERT IGNORE INTO all_emails (email, ip_address)  
SELECT email,ip_address
  FROM `table_3_emails`;

INSERT IGNORE INTO `all_emails` (email, ip_address)  
SELECT email,ip_address
  FROM `table_4_emails`;

SELECT DISTINCT email, ip_address, id FROM `all_emails`;

这仍然会产生重复的电子邮件。

Answer 1

只需使用 UNION 删除单个 INSERT 中的重复项:

INSERT IGNORE INTO `all_emails` (email, ip_address)  
SELECT email,IP_address
  FROM `table_1_emails`
 WHERE unsubscribed_at IS NULL
UNION
SELECT email,ip_address
  FROM `table_2_emails`
 WHERE unsubscribed_at IS NULL
UNION
SELECT email,ip_address
  FROM `table_3_emails`
UNION
SELECT email,ip_address
  FROM `table_4_emails`;