主页 / dba / 问题 / 157647
Accepted
Gili
Asked: 2016-12-09 18:02:46 +0800 CST

如何防止没有键限制的重复 VARCHAR？

  • 772

我想将 URL 存储在数据库列中，并强制执行值必须唯一的约束。不幸的是，MySQL 对索引键的长度有限制，这意味着只检查 URL 的前 X 个字符的唯一性。因此，我遇到了误报，其中两个不同的 URL 触发了约束集成违规，因为前 X 个字符恰好是相同的。

有没有办法在 VARCHAR 列上强制唯一性而对其长度没有任何限制？

例如，是否可以在前 X 个字符上创建一个非唯一索引，然后如果其余字符相同，则有一个触发器块 INSERTs？

mysql unique-constraint

5 个回答

  1. 我们不断给您提供不直接回答问题的答案，因为这就是我们解决这个问题的方式。无限长度的索引不切实际且效率低下，但唯一的哈希提供了一个足以完成任务的解决方案，因为有意义的碰撞的可能性极低。

    与其他提供的解决方案类似，我的标准方法不会预先检查重复项——从这个意义上说是乐观的：它依赖于数据库的约束检查，假设大多数插入不是重复项，所以没有意义浪费时间试图确定它们是否是。

    经过测试的工作示例（5.7.16，向后兼容 5.6；以前的版本没有内置TO_BASE64()功能）：

    CREATE TABLE web_page (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  url LONGTEXT NOT NULL,
  url_hash CHAR(24) COLLATE ascii_bin,
  PRIMARY KEY(id),
  UNIQUE KEY(url_hash),
  KEY(url(16))
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

    请注意，我存储的是 base64 版本的哈希。与以二进制形式存储相比，这是一个 4:3 大小的权衡，因为它使表内容和错误消息易于阅读，并且表压缩部分抵消了低效率。哈希列具有唯一约束。数据类型是CHAR, 不是VARCHAR，因为这消除了存储大小所需的字节 - 散列始终是固定大小。该列使用ascii带有（区分大小写）排序规则的字符集ascii_bin，使列和唯一索引尽可能小。

    url_hash 由下面的触发器设置，但触发器不检查冲突——由于 url_hash 的唯一约束，因此无需检查。数据库将阻止重复插入。

    注意 url_hash 应该已经被声明了NOT NULL，但是 MySQL 错误地在BEFORE INSERT触发器触发之前而不是之后强制执行这个，所以我们受到了限制。触发器确实阻止它为空。

    url 列的前缀索引长度为 16，这是任意选择的。这不是唯一的约束，只是查找的索引，它可能比您希望的要短，但它的长度对我们正在解决的问题没有操作影响，这里。

    这是设置 url_hash 的触发器。INSERT当我们插入行时，我们不需要在语句中包含这个值。

    DELIMITER $$
DROP TRIGGER IF EXISTS web_page_bi $$
CREATE TRIGGER web_page_bi BEFORE INSERT ON web_page FOR EACH ROW
BEGIN
  SET NEW.url_hash = TO_BASE64(UNHEX(MD5(NEW.url)));
END $$
DELIMITER ;

    您还需要一个更新触发器，如果​​表应该是不可变的，则阻止更新，或者如果 URL 更改，则更新哈希。我们还需要这个触发器来确保不会不恰当地设置 url_hash 列，NULL因为 MySQL 中的限制不允许我们按照我们应该的方式实际声明它。

    现在，进行测试。

    mysql> INSERT INTO web_page (url) VALUES ('http://example.com/');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM web_page;
+----+---------------------+--------------------------+
| id | url                 | url_hash                 |
+----+---------------------+--------------------------+
|  1 | http://example.com/ | pr8XV//wV/JmtpffnPF2/Q== |
+----+---------------------+--------------------------+
1 row in set (0.00 sec)

    到目前为止，一切都很好。现在，一个不同的 URL：

    mysql> INSERT INTO web_page (url) VALUES ('http://example.net/');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM web_page;
+----+---------------------+--------------------------+
| id | url                 | url_hash                 |
+----+---------------------+--------------------------+
|  1 | http://example.com/ | pr8XV//wV/JmtpffnPF2/Q== |
|  2 | http://example.net/ | ZVk/eLfvBI6tHN0Luj3NnQ== |
+----+---------------------+--------------------------+
2 rows in set (0.00 sec)

    仍然有效。现在，一个副本。

    mysql> INSERT INTO web_page (url) VALUES ('http://example.com/');
ERROR 1062 (23000): Duplicate entry 'pr8XV//wV/JmtpffnPF2/Q==' for key 'url_hash'

    完美的。如果您希望哈希冲突的风险比 MD5 提供的更低，请使用 SHA 变体，增加 to 的长度data_hash以CHAR_LENGTH(TO_BASE64(UNHEX( /* your hash function */ )))适应使用中的哈希算法生成的值。

    • 5

  2. 样品表：

    CREATE TABLE `tURL` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `url` text,
  `url_hash` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `url_hash` (`url_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

    插入触发器

    CREATE DEFINER=`root`@`localhost` TRIGGER `test_db`.`tr_uniqURL_ins`
BEFORE INSERT ON 
test_db.tURL
FOR EACH ROW BEGIN

SET new.url_hash = SHA2(new.URL,512);

IF EXISTS (SELECT id FROM tURL WHERE url_hash = new.url_hash AND URL LIKE new.URL) THEN

    set @msg = 'Trigger Error - duplicate detected ';
    signal sqlstate '45000' set message_text = @msg;

END IF;


END

    更新触发器

    CREATE DEFINER=`root`@`localhost` TRIGGER `test_db`.`tr_uniqURL_upd`
BEFORE UPDATE ON 
test_db.tURL
FOR EACH ROW BEGIN

SET new.url_hash = SHA2(new.URL,512);

IF EXISTS (SELECT id FROM tURL WHERE url_hash = new.url_hash AND  URL LIKE new.URL) THEN

    set @msg = 'Trigger Error - duplicate detected ';
    signal sqlstate '45000' set message_text = @msg;

END IF;


END

    添加：

    因为作者一次又一次地不信任社区 :) 让我们尝试解释一下 - 为什么所有建议都相同：

    变体 1 - 如作者所愿：

    子字符串 + 比较所有其他速度取决于子字符串，例如 VARCHAR(200)，这意味着对于具有长 URL 的大型数据库，它在第二步可以比较数千个值

    变体 2 - 使用 HASH 任何散列 - 将从完整 URL 生成散列，因此第二步仅适用于散列将具有重复项的数据库 - 换句话说，数万亿行

    对于 99,99999% 的情况，哈希将在第一步后返回单行 - 查找短列

    • 4

  3. 伪代码：

    if md5 matches then
    compare entire text of url

    要求：

    INDEX(md5) -- not UNIQUE
md5 BINARY(16) NON NULL  -- and use UNHEX(MD5(url)) for assigning

    （如采摘SHA1等根据需要调整）

    我会先在应用程序中构建代码；然后看看转换为存储过程是否合理。

    除了...如果您期望“长”TEXT值，请考虑将列更改为BLOB并在客户端中使用压缩/解压缩（不使用 MySQL 的函数）。压缩可以在使用前完成UNHEX(MD5(...))，所以和上面的推荐一致。

    客户端中的压缩减少了网络流量，如果客户端和服务器位于不同的机器上，则特别有用。压缩消耗客户端 cpu 周期，为其他事情减轻服务器周期；如果您有多个客户，则特别有用。而且，当然，节省了磁盘空间——大多数文本类型的 3 倍；由于常见的前缀，可能更像是 4 的 url。

    几乎可以肯定，两个不同的 url 会有两个不同的 md5。（对于所有实际目的来说足够接近。）前缀索引（不是唯一的！）将占用更多磁盘空间并且需要仔细检查。如果您不想信任 md5，请继续做前缀。

    WHERE md5 = '$md5' AND url = '$url'withINDEX(md5)很少会接触超过一行——而不是表扫描。非唯一INDEX(md5)可让您有效地找到与给定 md5 值匹配的所有行。通常只有 1 行，而不是 100 行。即使表中有 10 亿行，BTree 索引在查找其中唯一或几乎唯一的项目方面也非常有效。Wikipedia 对 BTrees 有很好的讨论。

    • 1
  4. Best Answer

    回答我自己的问题（因为所有其他答案都使用了哈希列或对列长度进行了限制）：

    DROP TABLE IF EXISTS url;
CREATE TABLE url
(
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(2048) NOT NULL
);

CREATE INDEX url_value_idnex ON url (value (191));

DROP TRIGGER IF EXISTS url_prevent_duplicates;
DELIMITER //
CREATE TRIGGER url_prevent_duplicates
BEFORE INSERT ON url
FOR EACH ROW
BEGIN
    DECLARE matches INT;
    DECLARE msg VARCHAR(128);
    SET matches = (SELECT count(*)
        FROM url
        WHERE value = NEW.value);

    IF matches <> 0 THEN
        -- SIGNAL message limited to 128 characters: http://stackoverflow.com/a/31672877/14731
        SET msg = (SELECT CONCAT('Duplicate value: ', SUBSTRING(NEW.value, 1, 128 - CHAR_LENGTH('duplicate value: '))));
        SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = msg;
    END IF;
END//
DELIMITER ;

    回顾：

    1. 在包含 URL 的列上创建一个非唯一索引（上面代码中的“值”）
    2. 使用最长的索引键（在我的例子中是 191，因为我使用的是utf8mb4编码）
    3. 如果 URL 已存在，则添加一个BEFORE INSERT指示错误的触发器。
    4. 当触发器使用 SELECT 检查 URL 是否已经存在时，它使用索引来缩小搜索空间。然后，对于任何索引冲突，它会比较完整的 URL，如果它们相同，它会发出异常信号。

    我想承认Michael - sqlbot和a_vlad的答案非常好，但我想尝试一个没有哈希列的解决方案，因为我怀疑在我的情况下，额外的列是矫枉过正或实际上可能会降低性能（更多在下面）。

    我对这两个选项的理解如下：

    没有哈希列

    1. 我们使用索引的隐式哈希（上面代码中的 191 个字符长）对 URL 进行哈希处理。
    2. 对于任何索引冲突，请进行value完整比较。

    带有哈希列

    使用Michael - sqlbot的答案作为参考...

    1. 我们首先使用 MD5 算法对 URL 进行哈希处理。
    2. 我们使用索引的隐式散列对#1 进行散列（我指的url_hash是索引本身的事实）
    3. 对于任何索引冲突，请url_hash完整比较这些值。
    4. 对于任何匹配url_hash，请完整比较这些url值。

    比较

    我的方法的缺点是索引哈希不是在完整的 URL 上计算的，因此它会导致比 MD5 方法更多的冲突（和完整的 URL 比较）。

    MD5 方法的缺点是它需要两个额外的步骤：计算 MD5 哈希和额外的 SELECT 以比较 MD5 值。

    那么，哪个更好？

    我们用我的方法发生索引冲突的可能性有多大？答案取决于实际数据集，因此我们无法绝对回答。这就是分析器的用途。我建议人们根据真实数据测试这两种方法，并据此做出决定。

    例如，我的具体用例涉及将网页与 HTTP 引荐来源网址相关联。每个 HTML 页面最多有 300 个引用者，这意味着冲突的概率几乎为零。即使较短的索引哈希会导致更多的冲突，也可以保证完整 URL 比较的数量保持在较低水平。

    • 1

  5. 如果 3072 字节足够，您可以启用 innodb_large_prefix，或升级到最新版本的 5.7 以默认使用它：

    http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix

    对于 URL，如果字符真正限于该字符集，则使用 ASCII 作为字符集会有所帮助。每个字符一个字节。

    • -1

相关问题