MySQL Sorting Persian Data
Introduction: When sorting Persian data in mysql, Persian letters (گ,چ,پ,ژ) are not placed in the right order and they will appear after other letters. So what should we do? Lots of options have been introduced until today. Some work and some don't.
Introduction:
When sorting Persian data in MySQL, Persian letters (گ,چ,پ,ژ) are not placed in the right order and they will appear after other letters. So what should we do? Lots of options have been introduced. Some work and some don't.
After days of searching for a solution and testing several suggestions, I found a way that sorts all Persian letters in the correct order without changing our table schema.
I am still not quite sure if this approach is one hundred percent correct or safe, but I couldn't find any flaws in using this approach.
Example:
Imagine we have a table called node with the following schema.
CREATE TABLE `NODE` ( `TID` INT(11) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR(255) NOT NULL, PRIMARY KEY (`TID`) ) ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1 ;
Then add the following data:
Query:
If we run a normal query with an order by clause, the result is not what we want.
Running "SELECT * FROM node ORDER BY name ASC" will result the following table:
As you can see, Persian letters are not placed in their right order.
If we run the following query, we will get the right sort for Persian letters:
"SELECT * FROM node ORDER BY CONVERT(name USING utf8) COLLATE utf8_persian_ci"
