MySQL/玛丽亚数据库 : How best to ensure BLOB of variable size is Unique

Question

MySQL 中的 BLOB 可以长达 65,535 字节。虽然我的大部分数据每条记录仅使用 100 字节左右的 BLOB，但在某些情况下，数据可能会大得多。

我在以下答案中看到:MySQL / MariaDB unique BLOB of fixed length建议对 BLOB 中的有效字节数进行限制。这在此处提供的用例中是有意义的，但我想知道什么是处理数据大小在需要保存之前未知的情况的最佳方法，也不遵循任何可预测的分布并且可以使用所有可用空间(或者至少是相当大的空间，比如 10000+ 字节)。

为了安全起见，我是否应该使用 BLOB (65535)(1) 定义字段？我担心当数据库中有大量记录时，这样做会需要(并浪费)大量存储空间。

(1) 看来MariaDB/MySQL只能接受3072的大小，所以不能用65535。

表定义:

CREATE TABLE IF NOT EXISTS `bibtexAlt` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `resource` INT NOT NULL,
  `bibtexAlt` BLOB (3072),
  CONSTRAINT `bibtexAltFK` FOREIGN KEY (`resource`) REFERENCES `resources` (`rID`) ON DELETE CASCADE,
  PRIMARY KEY (`id`),
  CONSTRAINT `bibtexAltNDX` UNIQUE KEY (`resource`, `id`),
  UNIQUE (`bibtexAlt`(3072)) USING HASH
) ENGINE = InnoDB;

Answer 1

不幸的是MySQL has a key prefix limit of 3072因此，即使您愿意，也无法将整个内容编入索引。

您可以做的是将 blob 的校验和与 blob 一起存储，并在其上放置一个唯一索引。为确保它始终存在，我们可以使用触发器，但最好使用 generated column 来完成。作为@Shadow suggested .

create table blobtest (
  stuff blob not null,
  checksum binary(32)
    as (unhex(sha2(stuff, 256))) stored
    not null unique
);

瞧!

mysql> insert into blobtest (stuff) values ("Basset hounds got long ears");
Query OK, 1 row affected (0.00 sec)

mysql> insert into blobtest (stuff) values ("Basset hounds got long ears");
ERROR 1062 (23000): Duplicate entry '\xCC\x17\xED\x7Fp\xB6+\xCE1\xDC\xFA\x8D\x12\xDE8\xBAU"\xEA6af\xF' for key 'checksum'

mysql> update blobtest set stuff = "abc" where stuff != 'abc';
ERROR 1062 (23000): Duplicate entry '\xBAx\x16\xBF\x8F\x01\xCF\xEAAA@\xDE]\xAE"#\xB0\x03a\xA3\x96\x17' for key 'checksum'

---

这是触发解决方案。

首先，让我们为触发器编写一个小函数来共享。

create function blob_checksum(data blob)
  returns binary(32) deterministic
  return unhex(sha2(data, 256));

然后更新和插入触发器。

create trigger before_insert_blobtest_checksum
before insert on blobtest
for each row
set new.checksum = blob_checksum(new.stuff);

create trigger before_update_blobtest_checksum
before update on blobtest
for each row
set new.checksum = blob_checksum(new.stuff);