MySQL / MariaDB unique BLOB of fixed length

Question

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)?

(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().)

Answer 1

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).

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

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)

Answer 2

BINARY(16) is what you meant to have.