MySQL diacritic insensitive search (Arabic)

Question

I have trouble making a diacritic insensitive search with arabic text.

I have tested multiple setups for the table in question: encodings in utf8 and utf16 as well as collations in utf8_general_ci, utf16_general_ci and utf16_unicode_ci.

The search works for åä special characters. I.e:

select * from test where text like '%a%'

Would return columns where text is a, å or ä. But it won't work with the Arabic diacritics. I.e if the text is بِسْمِ and I search for بسم, I don't get any hits.

Any ideas how to get pass this?

The real usage will later be PHP (a search function), but I'm working directly in the MySQL db just for testing before I port it over to PHP.

(from Comment)

CREATE TABLE test (
    ↵ id int(11) unsigned NOT NULL AUTO_INCREMENT,
    ↵ text text COLLATE utf8_unicode_ci,
    ↵ PRIMARY KEY (id)↵
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

Answer 1

SHOW COLLATIONS; to see what collations you have available. On my version, I don't see anything that looks tailored to Arabic. However, utf8_unicode_ci seems to do the folding you want. Here is a simple way to try it:

SELECT 'بِسْمِ' = 'بسم' COLLATE utf8_unicode_ci;

The result I got back was 1 (true), meaning they are considered equal. With utf8_general_ci it came back with 0, meaning not equal.

Then declare your fields to be VARCHAR(...) (or TEXT) CHARACTER SET utf8 COLLATE utf8_unicode_ci; Similarly for utf8mb4.

To build your own collation (and submit it for inclusion in future versions), see http://dev.mysql.com/doc/refman/5.6/en/adding-collation.html