searching arabic words that have diacritics in mysql

Question

to make my question simple anyone knows why this query returns true

SELECT 'الجنة' ='الْجِنَّةِ' COLLATE utf8_unicode_ci ;

while this query returns false

SELECT 'الجنة' LIKE '%الْجِنَّةِ%' COLLATE utf8_unicode_ci ;

and how could I let the later returns true ? thanks .

Answer 1

here the sqlfiddle of AllInOne that I modified to make it work : http://sqlfiddle.com/#!2/4a7004/3
changed NATURAL LANGUAGE MODE to BOOLEAN MODE
and added DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci to the db structure .

Answer 2

I think you want to read up on MATCH... AGAINST... for handling "Natural Language Full-Text Searches"

I loaded the example given by mysql.com into a sql fiddle, slightly modifying it to include your arabic string and it worked as expected.

See my sqlfiddle here: http://sqlfiddle.com/#!2/92317/1

UPDATE ANSWER TO INCLUDE ADDITIONAL PARAMETERS ADDED BY ASKER

(now uses BOOLEAN MODE and defines CHARSET and COLLATION)

Updated sqlfiddle by @Nyran91: http://sqlfiddle.com/#!2/4a7004/3

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO articles (title,body) VALUES
('الْجِنَّةِ DCKIEW', 'DAVADV الْجِنَّةِ AVADV')


SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('الجنة' IN BOOLEAN MODE);