What is wrong with the MySql LIKE operator in utf8_turkish_ci setting?

Question

I have a table as shown below:

wordId | word --------------------------------- 1 | axxe 2 | test word 3 | another test word 

I am trying to run the query below to find entries starting with the letters "ax".

 SELECT * FROM `words` WHERE word LIKE 'ax%' 

MySQL cannot find anything.

But, if I try one of the queries below, I can see the correct entry (the word "axxe") from the results.

 SELECT * FROM `words` WHERE word='axxe' SELECT * FROM `words` WHERE word LIKE '%ax%' SELECT * FROM `words` WHERE word LIKE 'a%' 

Why can't MySQL find the correct value for the first query? I tried to run this both on the command line and in phpMyAdmin, but the result is the same.

This is the output of SHOW CREATE TABLE:

 CREATE TABLE `words` ( `wordId` int(11) NOT NULL auto_increment, `word` text collate utf8_turkish_ci NOT NULL PRIMARY KEY (`word`) ) ENGINE=MyISAM AUTO_INCREMENT=2853 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci 

Answer 1

TL DR:

Upgrade your version of MySQL.

---

I created a simulation of your problem here:

Create table:

 CREATE TABLE `turky` ( `id` int(5) NOT NULL AUTO_INCREMENT, `word` text COLLATE utf8_turkish_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci 

Insert data:

 INSERT INTO `turky` (`id`, `word`) VALUES (1, 'axxe'), (2, 'test word'), (3, 'axxxxxe'), (4, 'another test word'); 

---

Run a test request (which works):

 SELECT * FROM `turky` WHERE `word`='axxe' 

Result:

1, 'axxe',

Run test request 2 (which works):

 SELECT * FROM `turky` WHERE word LIKE '%ax%' 

Result:

1, 'axxe',
3, 'axxxxxe',

Run test request 3 (which works):

 SELECT * FROM `turky` WHERE word LIKE 'a%' 

Result:

1, 'axxe',
3, 'axxxxxe',
4, "another test word";

---

Run test request 4 (which does not work initially):

 SELECT * FROM `turky` WHERE `word` LIKE 'ax%' 

Result:

1, 'axxe',
3, 'axxxxxe',

This works in MySQL using PHPMyAdmin.

Versions :
MySQL: 5.6.35
PHPMyAdmin: 4.6.6

---

The current Turkish alphabet does not contain the letter "x", so this fact may [probably not] cause some unclear interference in the process of sorting SQL (as in the absence of a language guide when searching for this character).

Web search for errors in Turkish in MySQL, and although there are half a dozen, none of them are suitable for your specific instance.

But the only option that I can see in my own testing (see above), using the table data and SQL that you provided to us, is that you have an older version of MySQL installed, in which there are some errors on Turkish language.

---

If your version of MySQL is updated

(or at least later than mine)

Then the problem seems specific to your installation and your data, so I doubt very much that we can find and reproduce this problem :-(

---

More diagnostic materials :
As Jacob H commented, see if this problem persists after casting to binary:

 SELECT * FROM `turky` WHERE BINARY `word` LIKE CONCAT(BINARY 'ax','%'); 

Result:

1, 'axxe',
3, 'axxxxxe',