Difference between database, table, column collation

Question

I understand that collations are a set of rules for making comparisons over a character set. MySQL / MariaDB 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).

Thanks.

Answer 1

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:

• Server collation
• Connection-specific collation
• Database collation
• Table collation
• Column collation
• Query collation (using CAST or CONVERT)

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 latin1 or 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 CAST or 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.

Answer 2

In short. When you set Server collation. to UTF-8. All Databases created without defining collation will inherit it from Server.

column iherits from table
table inherits from database
database inherits from server

However, you can overwrite default server at one of those points. Then everything will inherit from it.