[MySQL]: What is collation method?

Question

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?

Answer 1

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.