How do MIN() and MAX() work on CHAR/VARCHAR strings in MySQL?

Question

I've been playing with the world database (InnoDB) found here to gain more insight into MySQL.

I entered the following basic query:

SELECT COUNT(Name), MIN(Name), MAX(Name)
FROM Country
GROUP BY Continent

It seems that the way MIN() and MAX() work on the CHAR strings in Name is by alphabetical order, where A is the smallest value and Z is the greatest and so forth.

Can anyone explain what is going on behind the scenes and what values strings are being assigned for them to be sorted in such a way? What would happen with strings of both alphabetic characters and integers, or special characters?

Insight much appreciated.

Answer 1

MySQL string comparison, technically called collation, is locale-dependent. I suggest reading sections 10.1.1 on collation in general, 10.1.2 on collation in MySQL and 10.1.7 about collation issues. On http://collation-charts.org/ you can find out details about the individual collations, e.g. the one called latin1_general_ci (where _ci stands for case insensitive). The collation used when you call MAX and MIN should be the collation of the column in question, unless you specify a different collation using a formulation like MAX(Name COLLATE latin1_german2_ci).