社区
其他技术讨论专区
帖子详情
mysql – 如何在没有键限制的情况下防止重复的VARCHAR?
weixin_38118043
2019-09-12 01:49:38
我想将URL存储在数据库列中,并强制执行值必须唯一的约束.不幸的是,MySQL对索引键的长度有限制,这意味着只检查URL的前X个字符的唯一性.因此,我遇到了误报,其中两个不同的URL触发了约束集成违规,因为前X个字符恰好相同. 有没有办法在VARCHAR列上强制执行唯一性而不限制其长度? 例如,是否可以在前X个字符上创建非UNIQUE索引,然后如果其余字符相同则具有触发器块INSERT?
...全文
32
1
打赏
收藏
mysql – 如何在没有键限制的情况下防止重复的VARCHAR?
我想将URL存储在数据库列中,并强制执行值必须唯一的约束.不幸的是,MySQL对索引键的长度有限制,这意味着只检查URL的前X个字符的唯一性.因此,我遇到了误报,其中两个不同的URL触发了约束集成违规,因为前X个字符恰好相同. 有没有办法在VARCHAR列上强制执行唯一性而不限制其长度? 例如,是否可以在前X个字符上创建非UNIQUE索引,然后如果其余字符相同则具有触发器块INSERT?
复制链接
扫一扫
分享
转发到动态
举报
写回复
配置赞助广告
用AI写文章
1 条
回复
切换为时间正序
请发表友善的回复…
发表回复
打赏红包
weixin_38132214
2019-09-12
打赏
举报
回复
我们一直给你答案,不直接回答这个问题,因为这就是我们解决这个问题的方法.无限长度的索引是不切实际且效率低的,但是由于天文学上有意义的碰撞可能性低,因此唯一的散列提供了足以完成任务的解决方案. 与其他提供的解决方案类似,我的标准方法不预先检查重复 – 在这个意义上它是乐观的:它依赖于数据库的约束检查,假设大多数插入不是重复,所以没有意义浪费时间试图确定它们是否存在. 经过测试的工作示例(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
5.7优化视频教程
MySQL
5.7新增了众多特色功能,诸如Optimizer增强、支持多个触发器、P_S增强、增加sys schema、在线修改
VARCHAR
长度、并发复制等,对于提升SQL效率都有很大帮助,作为非专业DBA,如何利用这些特性帮助我们做好SQL...
MySQL
数据库面试题总结(2022最新版)
MySQL
数据库面试题(2022版) 文章目录一、基础基本概念
MySQL
有哪些数据库类型?CHAR 和
VARCHAR
区别?数据库设计什么是三大范式?什么是范式和反范式,以及各自优缺点?二、索引索引的几种类型或分类?索引的优...
MySQL
知识点简述
3、简述在
MySQL
数据库中 MyISAM 和 InnoDB 的区别 4、
MySQL
中 InnoDB 支持的四种事务隔离级别名称,以及逐级之间的区别? 5、CHAR 和
VARCHAR
的区别? 6、主
键
和候选
键
有什么区别? 7、myisamchk 是用来做...
MySQL
数据库面试题(2020最新版)
数据库三大范式是什么
mysql
有关权限的表都有哪几个
MySQL
的binlog有有几种录入格式?分别有什么区别?数据类型
mysql
有哪些数据类型引擎
MySQL
存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别?InnoDB引擎的4...
【2022最新Java面试宝典】——
MySQL
面试题(40道含答案)
3、简述在
MySQL
数据库中 MyISAM 和InnoDB 的区别4、
MySQL
中InnoDB 支持的四种事务隔离级别名称,以及逐级之间的区别?5、CHAR 和
VARCHAR
的区别?6、主
键
和候选
键
有什么区别?7、myisamchk 是用来做什么的?8、...
其他技术讨论专区
433
社区成员
791,270
社区内容
发帖
与我相关
我的任务
其他技术讨论专区
其他技术讨论专区
复制链接
扫一扫
分享
社区描述
其他技术讨论专区
其他
技术论坛(原bbs)
社区管理员
加入社区
获取链接或二维码
近7日
近30日
至今
加载中
查看更多榜单
社区公告
暂无公告
试试用AI创作助手写篇文章吧
+ 用AI写文章