I understand that collations are a set of rules for making comparisons over a character set. MySQL has table and database collations in addition to column collation. I was wondering what was the difference between a collation on these three (database, table and column).
MySQL's character sets and collations can be interpreted as a top-down list of prioritized items. The topmost is least priority and the bottommost is most priority.
Order of precedence with topmost being least precedence:
The server collation is set by the server, which is set either inside of
my.cnf or when the server was built from source code. By default, this will usually be
utf8, depending on your platform.
The connection-specific collation is set by the client using a query like
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';. Most clients don't set a connection-specific collation, so the server will use its own default as explained above.
The database collation is set during database creation, or manually by updating it later. If you don't specify one, it will use the next higher-level collation, which would either be the connection-specific or the server collation.
The table collation is the same as the database collation, except if left blank, it will use the database as its default, then connection-specific, and then finally the server's collation.
The column collation uses the table's collation as its default, and if there is no collation set, it will then follow up the chain to find a collation to use, stopping at server if all of the others weren't set.
The query collation is specified in the query by using
CONVERT, but otherwise will use the next available collation in the chain. There's no way to set this unless you use a function.
Please also refer to the manual page Character Set Support.