Accent insensitive search query in MySQL

by

You can change the collation at runtime in the sql query,

...where title like '%torun%' collate utf8_unicode_ci

but beware that changing the collation on the fly at runtime forgoes the possibility of mysql using an index, so performance on large tables may be terrible.

Or, you can copy the column to another column, such as searchable_title, but change the collation on it. It’s actually common to do this type of stuff, where you copy data but have it in some slightly different form that’s optimized for some specific workload/purpose. You can use triggers as a nice way to keep the duplicated columns in sync. This method has the potential to perform well, if indexed.

Note – Make sure that your db really has those characters and not html entities.
Also, the character set of your connection matters. The above assumes it’s set to utf8, for example, via set names like set names utf8

If not, you need an introducer for the literal value

...where title like _utf8'%torun%' collate utf8_unicode_ci

and of course, the value in the single quotes must actually be utf8 encoded, even if the rest of the sql query isn’t.

More Related Contents:

  1. Implementation of Levenshtein distance for mysql/fuzzy search?
  2. MySQL find_in_set with multiple search string
  3. How to remove accents in MySQL?
  4. MySQL search and replace some text in a field
  5. Search for string within text column in MySQL
  6. Search all columns of a table using a single where condition with single keyword in mysql
  7. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  8. SQL search multiple values in same field
  9. Laravel – search with multiple keywords against multiple columns with the search result to be ordered in relevance
  10. How to allow fulltext searching with hyphens in the search query
  11. multiple query same table but in different columns mysql
  12. How can I make SQL case sensitive string comparison on MySQL?
  13. Import data in MySQL from a CSV file using LOAD DATA INFILE
  14. Library not loaded: libmysqlclient.16.dylib error when trying to run ‘rails server’ on OS X 10.6 with mysql2 gem
  15. Get current AUTO_INCREMENT value for any table
  16. Fulltext Search with InnoDB
  17. INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE
  18. mysql trigger stored trigger is already used by statement which invoked stored trigger
  19. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  20. mysql update query with sub query
  21. What is the difference between the backtick and the square bracket in SQL statements?
  22. Integrity constraint violation: 1452 Cannot add or update a child row:
  23. What does SQL Select symbol || mean?
  24. What’s the recommended way to connect to MySQL from Go? [closed]
  25. MySQL. Can’t create table errno 150
  26. How can “set timestamp” be a slow query?
  27. WooCommerce: Finding the products in database
  28. How can I “select *” from a table in MySQL but omit certain columns?
  29. For homebrew mysql installs, where’s my.cnf?
  30. MySQL Select Multiple VALUES

Leave a Comment