Search code examples
javamysqlsortingcollectionscomparator

Java sorting is not the same with MySQL sorting

I need to check the sorting in the table and table content is given by MySQL. I'm trying the following: Collections.sort(sorted, String.CASE_INSENSITIVE_ORDER);

And get the following result:
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

And this is what I get from MySQL by query:
SELECT 'email' FROM 'user' WHERE 1 ORDER BY 'user'.'email' ASC :

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

Seems like Java sorts according to ASCII table: http://www.asciitable.com/ 4 (52) - @ (64) - _ (95)

But in MySQL result the order is _ -> @ -> 4

The email field collation is: utf8_unicode_ci
What is the problem and is there any comparator to make ordering in the same way?

Solution

  • Use Collator:

    The Collator class performs locale-sensitive String comparison. You use this class to build searching and sorting routines for natural language text.

    And code will be:

    Collator coll = Collator.getInstance(Locale.US);
coll.setStrength(Collator.IDENTICAL); 
Collections.sort(words, coll);