title: MySQL: UNIQUE text field using additional HASH field
body: <p>In my MySQL DB I have a table defined like:</p>
  
  <pre><code>CREATE TABLE `mytablex_cs` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `tag` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',
    `value` text COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`),
    KEY `kt` (`tag`),
    KEY `kv` (`value`(200))
  ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  </code></pre>
  
  <p>I need to implement a UNIQUE constraint (key) on the <code>value</code> field.</p>
  
  <p>I know that is not yet possible to define a unique index on the entire value for a <code>blob</code> or <code>text</code> field, but there is a ticket(?) open to implement such feature (see <a href="http://dev.mysql.com/worklog/task/?id=882" rel="nofollow">this page</a>) where it has been suggested to create a unique key using a hash like it is already implemented for other fields.</p>
  
  <p>Now I would like to use a similar approach adding to the table another field that will contain the hash and creating a unique key on this field.</p>
  
  <p>I gave a look to possible ways to create this hash and, since I would like to avoid collisions (I need to insert several millions of entries), it seems that the RIPEMD-160 algorithm is the best one, even if a quick search gave me several similar solutions that use SHA256 or even SHA1 and MD5.</p>
  
  <p>I totally lack of knowledge in cryptography, so what are the down sides of choosing this approach? </p>
  
  <p>Another question I have is: which algorithm is currently used by MySQL to create the hash?</p>