CODEWITHSUNDEEP
mysql
RKh
RKh

Reputation: 14161

[MySQL]: What is collation method?

When I use phpMyAdmin to see my online tables, it uses collation method "latin_swedish_ce". Why? What is the default? And, for what collation method is used?

Upvotes: 5

Views: 3856

Answers (5)

Andomar
Andomar

Reputation: 238086

A string in MySQL has a character set and a collation. Utf8 is a character set, and utf8_bin is one of its collations. The collation determines how MySQL compares strings.

For example, here's two ways to compare a UTF8 string:

select case when 'test' = 'TEST' collate utf8_bin 
       then 'true' else 'false' end,
       case when 'test' = 'TEST' collate utf8_roman_ci 
       then 'true' else 'false' end

The first uses the utf8_bin collation, which is case sensitive. The second uses utf8_roman_ci, which is case insensitive.

A list of all character sets and collations can be found with:

show character set
show collation

latin1_swedish_ci is the default collation (MySQL was originally developed in Sweden), this is also true for MariaDB.

Upvotes: 9

bobince
bobince

Reputation: 536379

Why? What is the default?

latin1_swedish_ci is the default, because the company MySQL AB comes from Sweden.

In general this collation is considered a reasonable default for any not-too-fussy application using the ISO-8859-1 (Western European) character set. It makes most diacriticals insignificant in a comparison:

'A'='Ã'   -- 1

however having said that it does have a few quirks specific to Swedish: ö, ø, å, æ are considered separate letters (and ä is considered the same as æ); y is considered the same letter as ü. It also has some features that aren't so much Swedish as just wrong:

'['='å'   -- 1, huh?
'd'='ð'   -- 1, tsk. eth is nothing to do with d

See the gory details for more.

latin1_german_ci is a better collation if you want letters to match regardless of diacritical. latin1_general_ci is a better collation if you don't. If you don't care about case-insensitive matching at all and want every character to be unique, go for latin1_general_cs.

Of course these days one would hope you'd be using one of the utf8_ collations instead of anything based on latin1.

Upvotes: 4

balpha
balpha

Reputation: 50908

The collation is used when you compare strings, e.g. for sorting them. For example, in Swedish the letter "Ä" comes after "Z", but in German it is treated like "AE". So, depending on the collation, strings are sorted differently.

Since MySQL comes from Sweden, Swedish is the default.

Upvotes: 8

David Kuridža
David Kuridža

Reputation: 7207

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

You can find more information in MySQL Reference Manual.

Upvotes: 3

o.k.w
o.k.w

Reputation: 25810

Do you mean latin1_swedish_ci? That's the default collation for mySql.

It is used when performing sorting/comparison.

To change it, here's the reference:

http://dev.mysql.com/doc/refman/5.0/en/charset-server.html

Upvotes: -1

Related Questions