MySQL/MariaDB固定长度的唯一BLOB [英] MySQL / MariaDB unique BLOB of fixed length

问题描述

我需要一个16字节的二进制数据列才能唯一.当BLOB(16)支持唯一的VARCHAR时，为什么在MySQL和MariaDB中不能唯一?支持此功能，但不支持固定长度的字节集，这似乎很困难.另外，通过在base64编码的字符串中存储二进制值来浪费空间也是不可接受的.因此，有没有比在两个BIGINT之间进行转换(形成一个唯一索引)更好的选择(如果重要的话，此16字节的二进制文件不用作主键)?

I need a 16-byte binary data column to be unique. Why can't a BLOB(16) be unique in MySQL and MariaDB, when it supports unique VARCHAR? That this is supported but not a fixed-length set of bytes seems nuts. Also, it's not acceptable to waste space by storing a binary value in base64 encoded strings. So, any better option than converting to/from two BIGINTs that make a composite unique index (this 16-byte binary is not used as a primary key, if it matters)?

(此外，如果我确实使用了两个BIGINT，那么在复合唯一索引上重复插入是否会像在非复合索引上那样在插入时默默地执行INSERT IGNORE失败?重复的16字节重复插入尝试无提示失败，并从后面的LAST_INSERT_ID()返回0.)

(Also, if I do use two BIGINTs, does INSERT IGNORE silently fail on a duplicate insertion on a composite unique index as it does with a non-composite one? This is critical in my case, as I need in the case of duplicate 16-byte insert attempt the silent failure plus a return of 0 from the subsequent LAST_INSERT_ID().)

推荐答案

您可以在BLOB列上创建UNIQUE索引，您只需要指定索引的最大长度即可(这也意味着它只会是唯一的)到那么多字符).

You can create a UNIQUE index on a BLOB column, you simply need to specify a maximum length for the index (which also means, it will only be unique up to that many characters).

但是，请考虑使用VARBINARY代替它，它允许您固定长度，这意味着您不能插入更长的字段，而这可能会意外打破唯一约束.参见 https://dev.mysql.com/doc/refman/5.6/en/binary-varbinary.html

Consider, however, using VARBINARY instead which allows you to fix the length and means you can't insert a longer field that may accidentally break the unique constraint. See https://dev.mysql.com/doc/refman/5.6/en/binary-varbinary.html

示例，在5.6.23上进行了测试:

Example, tested on 5.6.23:

mysql [localhost] {msandbox} (test) > create table t1 (a BLOB(16), UNIQUE INDEX `a`(`a`(16)));
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values('aaa');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values('aaa');
ERROR 1062 (23000): Duplicate entry 'aaa' for key 'a'
mysql [localhost] {msandbox} (test) > 

mysql [localhost] {msandbox} (test) > create table t2(a VARBINARY(16), UNIQUE INDEX `a`(`a`));
Query OK, 0 rows affected (0.02 sec)

这篇关于MySQL/MariaDB固定长度的唯一BLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！