The concept of mysql table characterset and collation initially confused me a great deal. But the concept is actually rather simple.

Character Set

What is to be stored (ascii, utf8, utfmb4) defines how the bytes are packed in the datebase page files

Collation

How you compare characters for example is a equal to ä. In practice the often decides if you comparisons are case and accent senstive or insenstive. Unicode in email address normalization can be important here too.

The collation defines implicit string comparisons and ordering for indexing. Your data can have a different sort order stored in the table based on its collation.