如何快速检测数据库中是否已存在重复地址？

问题描述 #

我希望避免数据库中出现重复的地址记录，请问检测地址是否已存在的最优（及/或最快）方法是什么？

我的初步方案如下：

CREATE TABLE addresses ( 
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  line_1 VARCHAR(40),
  line_2 VARCHAR(40),
  line_3 VARCHAR(40),
  postal_code VARCHAR(40),
  hash_code VARCHAR(32) AS (MD5(CONCAT(line_1, line_2, line_3, postal_code)))
);

该方案会为地址字段生成MD5哈希值，我只需检查是否已存在相同哈希值的地址即可。我知道此方法区分大小写，但这对我无关紧要。我需要一种无需逐一比较各列的快速简便方法，来检测是否存在完全相同的地址记录。

方案分析与优化建议 #

你的方案的合理性 #

这个思路是可行的：通过将所有地址字段拼接后生成MD5哈希，把多列的唯一性校验转化为单列的哈希值校验，确实能避免逐一比较各列的繁琐，而且MD5计算速度快、哈希值长度固定，适合快速检索。

关键优化点 #

• 给hash_code添加索引
  当前表定义中没有对hash_code加索引，会导致检测重复时需要全表扫描，数据量增大后速度会急剧下降。建议修改表定义，给hash_code添加普通索引：

  CREATE TABLE addresses ( 
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    line_1 VARCHAR(40),
    line_2 VARCHAR(40),
    line_3 VARCHAR(40),
    postal_code VARCHAR(40),
    hash_code VARCHAR(32) AS (MD5(CONCAT(line_1, line_2, line_3, postal_code))),
    INDEX idx_hash_code (hash_code)
  );
  

  有了索引后，检测重复的查询（比如SELECT 1 FROM addresses WHERE hash_code = ?）就能通过索引快速定位，时间复杂度从O(n)降到O(log n)。

• 哈希碰撞的极低风险
  MD5存在理论上的哈希碰撞概率，但在地址类数据的业务场景中，产生碰撞的概率几乎可以忽略不计，方案可靠性足够。如果追求绝对零碰撞，可以改用SHA-256哈希，但SHA-256哈希值更长（64位），索引占用空间更大，检索速度略逊于MD5，对于地址检测来说没必要。

替代方案：多列唯一约束 #

如果不需要“无需逐一比较各列”的简便性，直接给地址字段添加多列唯一约束也是可靠方案：

CREATE TABLE addresses ( 
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  line_1 VARCHAR(40),
  line_2 VARCHAR(40),
  line_3 VARCHAR(40),
  postal_code VARCHAR(40),
  UNIQUE KEY uk_address (line_1, line_2, line_3, postal_code)
);

这种方案不需要计算哈希，数据库会自动维护唯一性，插入重复数据时会直接抛出错误。但缺点是检测重复时，查询条件需要包含所有列，不如哈希值的单条件查询简洁。

最终推荐 #

如果核心需求是快速且简便的重复检测，给hash_code添加索引的方案是最优选择：既保留了单字段查询的简便性，又通过索引保证了检索速度。

内容的提问来源于stack exchange，提问作者SPRTK