首页 / MYSQL / mysql – 如何在没有键限制的情况下防止重复的VARCHAR？

mysql – 如何在没有键限制的情况下防止重复的VARCHAR？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 如何在没有键限制的情况下防止重复的VARCHAR？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3498字，纯文字阅读大概需要5分钟。

内容图文

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

有没有办法在VARCHAR列上强制执行唯一性而不限制其长度？

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

解决方法:

我们一直给你答案,不直接回答这个问题,因为这就是我们解决这个问题的方法.无限长度的索引是不切实际且效率低的,但是由于天文学上有意义的碰撞可能性低,因此唯一的散列提供了足以完成任务的解决方案.

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

经过测试的工作示例(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;

请注意,我正在存储hash64的base64版本.与以二进制形式存储它相比,这是4：3大小的权衡,因为它使表内容和错误消息成为可读的,并且低效率被表压缩部分抵消.哈希列具有唯一约束.数据类型是CHAR,而不是VARCHAR,因为这消除了存储大小所需的字节 – 散列总是固定大小.该列使用带有ascii_bin(区分大小写)排序规则的ascii字符集,使列和唯一索引保持尽可能小.

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变体,将data_hash的长度增加到CHAR_LENGTH(TO_BASE64(UNHEX(/ *您的哈希函数* /)))以适应哈希算法生成的值正在使用.

内容总结

以上是互联网集市为您收集整理的mysql – 如何在没有键限制的情况下防止重复的VARCHAR？全部内容，希望文章能够帮你解决mysql – 如何在没有键限制的情况下防止重复的VARCHAR？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/895608.html

来源：【匿名】