Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-371

Unique indexes for blobs




      Allow a user to create unique constraints of arbitrary length. Only in MyISAM (extending this to InnoDB is a separate task).

      MyISAM supports unique constraints of arbitrary length. They are used in queries like

      SELECT DISTINCT blob_column FROM some_table;

      The server executes this query with the help of a temporary MyISAM table. And this table must be able to maintain uniqueness of all blob_column values. Internally it's implemented as an index over hash values of blobs, with the additional direct value comparison on collisions.

      So, MyISAM can do it internally, but there is no way for the end user to create such a unique constraint explicitly. The following statement

      CREATE TABLE some_table (blob_column BLOB, UNIQUE(blob_column));

      will fail.

      This task is about giving the access to this MyISAM feature to the SQL layer, about allowing the end user to create long unique constraints explicitly.

      original bug report:

      hi guys, i was reading about index... and i have a interesting problem...
      i need to check if a file (ok a big row... no problem...) is inside my table...
      what i´m thinking....
      create table a(b int not null default '',c longblob not null, primary key b)
      ok no problems....
      the problem is... how to know if a file, let´s sai a file of 16MB is inside my table...
      first solution is... MD5 and check each row... OK nice work....
      but could be a other nicer solution?!
      i was thinking something like:

      alter table a
      add index some_index(c) using hash;

      could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example...

      could check if this is possible? today not... i tryed and it return:
      /* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR)

      i think that´s all


          Issue Links



              • Assignee:
                serg Sergei Golubchik
                rspadim roberto spadim
              • Votes:
                1 Vote for this issue
                6 Start watching this issue


                • Created: