title: Accent-insensitive searches / problems with utf8_general_ci collation
body: <p>Edit: if you're here because you're confused by the polish collation in MySQL, <a href="https://bugs.mysql.com/bug.php?id=9604" rel="nofollow">read this</a>.</p>
  
  <p>I'm trying to perform a full-text search on a table of polish cities and many of them contain accented characters. It's meant to be used in an ajax call for auto completion so it would be nice if the search was accent-insensitive. I've set the collation of the rows to <code>ut8_polish_ci</code>. Now, given the city "Zelów", I query the database like this</p>
  
  <blockquote>
    <p><code>SELECT * FROM</code>cities<code>WHERE MATCH( city ) AGAINST ("zelow")</code></p>
  </blockquote>
  
  <p>but to no avail. Mysql returns an empty result. I've tried different accents, tried adding different collations to the query but nothing helped. I'm not sure how I should approach this because accent-sensitivity seems to be poorly documented. Any ideas?</p>
  
  <p><hr>
  <B>EDIT</B></p>
  
  <p>So I found out that the case-insensitive full-text searches are performed only IN BOOLEAN MODE, so the correct query would be</p>
  
  <blockquote>
    <p><code>SELECT * FROM</code>cities<code>WHERE MATCH( city ) AGAINST ( "zelow" IN BOOLEAN MODE )</code></p>
  </blockquote>
  
  <p>Previously I thought otherwise due to <a href="http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html" rel="nofollow">a misleading comment on dev.mysql.com</a>. There might be more to it but I'm just really confused right now. </p>
  
  <p>Anyway, as mentioned in the comments below, I have UNIQUE index on the cities column so changing the collation of the table to accent-insensitive utf8_general_ci is out of the question. </p>
  
  <p>I realized however, that the following query works quite well on a table with utf8_polish_ci collation:</p>
  
  <blockquote>
    <p><code>SELECT * FROM</code>cities<code>WHERE city LIKE 'zelow' COLLATE utf8_general_ci</code></p>
  </blockquote>
  
  <p>It would seem now that the most reasonable solution would be to do a full-text search in a similar fashion:</p>
  
  <blockquote>
    <p><code>SELECT * FROM</code>cities<code>WHERE MATCH( city ) AGAINST ( 'zelow' IN BOOLEAN MODE ) COLLATE utf8_genral_ci</code></p>
  </blockquote>
  
  <p>This however yields the following error:</p>
  
  <blockquote>
    <p><code>#1253 - COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'</code></p>
  </blockquote>
  
  <p>This is really starting to get on my nerves. Might as well abandon full-text search in favour of a simple where-like approach but it doesn't seem sensible in a table with almost 50k records which will be intensively queried...</p>
  
  <p><hr>
  <b>LAST EDIT</b>
  Ok, the thing with boolean mode was partly bullshit. Only partly because it indeed works as I said, however, on a utf8_general_ci it works the other way around. I'm utterly perplexed and have no will to study this issue further. I decided to drop the UNIQUE index (no further cities will be added anyway so no need to make a big deal out of it) and stick with the utf8_general_ci table collation. I appreciate all the help, it steered me in the right direction.</p>