如何在没有密钥限制的情况下防止重复VARCHAR？

Question

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

是否有一种方法可以在VARCHAR列上强制执行其长度不受任何限制的唯一性？

是否有可能，比如说，在第一个X字符上创建一个非唯一的索引，然后在其余字符相同的情况下有一个触发器块插入？

Answer 1

样本表：

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

Add:

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

备选案文1-如作者所愿：

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

变体2-使用散列任何散列-将使哈希从完整的URL，所以第二步将只工作在数据库中，哈希将有重复-万亿行，换句话说

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

Answer 2

如果3072字节就足够了，您可以启用innodb_large_prefix，或者升级到最新的5.7版本，默认情况下：

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_诺姆b_大型_前缀

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