mysql - 为 MySQL 数据库中的长 varchar 字段强制执行唯一值

Question

我有一个非常长的数据库字段(2083 个字符)，因此我无法在 MySQL 中对其设置 UNIQUE 约束。

我尝试修改一个存储过程，以便检查尝试插入的值是否唯一，如果不是，则停止插入。但是存储过程总是出错。我尝试了很多不同的语法。这真的很奇怪，存储过程只适用于 if 语句或插入，但不能同时适用于两者。

非常感谢任何帮助。

BEGIN
SET @insertedid := 0;
SET @alreadyfound := 0;
SELECT COUNT(lref_id) FROM listing_referrers WHERE lref_referring_url=in_lref_referring_url INTO @alreadyfound;

IF (@alreadyfound = 0) THEN
BEGIN
    INSERT INTO listing_referrers (lref_listing_id,lref_referring_url,lref_createdby_userid,lref_created) VALUES (in_lref_listing_id, in_lref_referring_url, in_lref_createdby_userid, in_lref_created);
    SET @insertedid = last_insert_id();
END
SELECT @insertedid;
END

Answer 1

使用触发器的想法

我不会在这里使用过程，但更喜欢使用触发器 ON INSERT .您可以在 How to prevent using digits in a VARCHAR column using mysql? 找到我的答案有趣的是，它主要结合了CREATE TRIGGER的想法带着想法 SIGNAL荷兰国际集团它的情况下检测到错误情况。

所以，在你的情况下，它可能是这样的

DELIMITER //

CREATE TRIGGER trg_listing_referrers_unit_ins before insert on listing_referrers
for each row
begin
    IF EXISTS (SELECT * FROM listing_referrers WHERE lref_referring_url = new.lref_referring_url)
        THEN signal sqlstate '45000' set message_text = 'trying to insert duplicate lref_referring_url';
    END IF;
END
//

DELIMITER ;

但是请注意，这需要 MySQL 5.5 或更高版本。如果您运行的是较早版本的 MySQL，这可能会变得很丑陋...

注意:取决于您的 table 大小listing_referrers SELECT触发器中的语句可能会成为性能消耗者。确保这一点，即使您不能在 lref_referring_url 上定义具有非唯一索引的唯一索引。否则你最终会得到 O(n)算法而不是 O(log n)一个，这可能会产生巨大的差异。

使用散列键列的想法

除了跟踪整个 VARCHAR 的唯一性之外，您还可以检查真实 key 的散列值的唯一性。例如，您可以添加另一个名为 key_hash 的列输入 CHAR(64) ASCII到您的表，您将在其上放置一个 UNIQUE KEY 约束(如果需要，您甚至可以将其设为主键)。

插入时，让 MySQL 即时计算它的 SHA2 散列值，例如

INSERT INTO listing_referrers (lref_referring_url, key_hash)
  VALUES("http://server.bogus/mylong/url", SHA2("http://server.bogus/mylong/url", 256));

如果您两次输入相同的 URL，其 SHA2 值将相同，因此 key_hash 的 UNIQUE KEY 约束会开始反抗。

由于 collision resistance 的加密验证属性使用此功能，可以确保您几乎不可能找到两个具有相同哈希值的 URL。如果遇到请发到cryptographers community - 他们肯定会非常渴望了解您的情况(以及许多情报部门)。

注意:如果记录的 URL 发生变化(通过 UPDATE )，您始终需要确保 hash_key 也发生变化得到相应更新。否则，你会发疯的。如果这对您来说是一个真实的用例，您可能还想看看 MySQL: Computed Column (我心里不清楚，这是否也适用于主键列)。

限制字符唯一性的想法

如果您能够限制第一个 n 的唯一性字符(带有 n < 255 )，那么您可能会找到 Storing email VARCHAR(320) as UNIQUE, #1071 - Specified key was too long; max key length is 767有趣。

限制为 3072 个字符的想法

如果您可以确保您的 URL 不超过 3072 个字符，请将字符集更改为 ASCII会将此边界(与 UTF-8 中的 256 个字符相比)增加到 3072。详细信息在 MySQL unique 1500 varchar field error (#1071 - Specified key was too long) 中描述。