MySQL collation for Portugese

Question

Is there a way in MySQL to have character collated as per the Portugese language? In the same way there is utf8_spanish_ci or Spanish. Or is there a way to add new collations?

There are the following characters:

ç (Gonçalves)
ã (Guimarães)
õ (Simões)
â (Tânger)
ô (Pôrto)
ê (Gouvêa)
é (Féria)
í (Vinícius)
ó (Grijó)
ú (Araújo)
á (Tomás)
ñ (Núñez)

When using utf8_general_ci searching for 'Gonçalves' would also return 'Goncalves'. I need these to be treated separately by MySQL.

use `latin1_danish_ci` as the collation . http://collation-charts.org/mysql60/mysql604.latin1_danish_ci.html — User123456, Nov 27 '17 at 09:39
You can check the collations [_here_](http://mysql.rjweb.org/utf8_collations.html) . I agree that danish seems to do what you want for _those_ accents than spanish or spanish2. — Rick James, Nov 27 '17 at 18:34
Coming to this in a few hours? Is there not a way to add coalitions? I looked in information_schema and this lists the coalitions, though I can't see the actual character tables in there. — Kohjah Breese, Nov 28 '17 at 11:57
@KohjahBreese - There is a web page in the manual that explains the gory details of creating your own collation. — Rick James, May 17 '23 at 22:54

score 1 · Answer 1 · answered Nov 27 '17 at 11:11

Please check for the collation and character set. This is an example create statement for accepting Portugese characters.

CREATE DATABASE dansih_db CHARACTER SET latin1 COLLATE latin1_danish_ci;

try altering Character Sets and Collation in you DB Example output for above character set and collation

[ Check this output[1

score 0 · Answer 2 · answered Jun 03 '20 at 21:47

According to http://mysql.rjweb.org/utf8mb4_collations.html, and tested in MySQL, the latin1_general_ci is the collation that correctly differentiates between the characters the OP has identified

It also sorts it properly, and does not transform Ç into C for instance.

score 0 · Answer 3 · answered Jan 18 '22 at 20:22

The above are answers for the user's question but I hope it's appropriate to warn: with a typical Portuguese collation those characters would not all be separate for searching. Wikipedia explains in https://en.wikipedia.org/wiki/Portuguese_orthography "Accented letters and digraphs are not counted as separate characters for collation purposes." and Unicode's common language data repository explains in https://github.com/unicode-org/cldr/blob/main/common/collation/pt.xml "The root collation order is valid for this language." So MySQL's default Unicode-collation-algorithm collations would usually be desirable.

MySQL collation for Portugese

3 Answers3

Linked