Swedish language and MySQL sort order

I have the habit of keeping the collation for my databases utf8_general_ci, but recently I had a table containing swedish cities and had to order them by name. The problem was that the swedish accents “åäöÅÄÖ” which normally go at the end of the alphabet were all over the place.

mysql> SELECT * FROM cities ORDER BY stad;
+------------+
| stad |
+------------+
| Åbyn |
| Abborrsjöknoppen |
| Ängsnäs |
| Håsta |
| Kofsta |
| Ockelbo |
| Östveda |
| Raman |
+------------+

 

So apparently me and MySQL had some different thought about the order of the cities…

The obvious solution is to just change the collation of the database using
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_swedish_ci;
In my case this wasn’t an option though, so I just modified my query adding the collate clause at the end and it worked like a charm:

 

mysql> SELECT * FROM cities ORDER BY stad COLLATE utf8_swedish_ci;
+------------+
| stad |
+------------+
| Abborrsjöknoppen |
| Håsta |
| Kofsta |
| Ockelbo |
| Raman |
| Åbyn |
| Ängsnäs |
| Östveda |
+------------+

This seemed to do the trick, and it is especially useful for Multi-user WordPress installations, where you can have different user blogs using different languages…