CODEWITHSUNDEEP
mysqldatabaseutf8mb4
Patrick Lim
Patrick Lim

Reputation: 21

What difference in schema VS table VS column CHARSET in MySQL?

What difference in schema CHARSET VS table CHARSET VS column CHARSET in MySQL?

When I change my table's charset to utf8, can I use utf8mb4 charset in my column?

Thanks.

Upvotes: 1

Views: 1103

Answers (2)

Rick James
Rick James

Reputation: 142528

When creating a table, the backup for charset and collation is the settings for the schema.

Once you have created the table, it now has a default charset and collation. (This is subtly different than what fancyPants said.)

Similarly, when creating a column (either as part of creating the table, or with ALTER .. ADD COLUMN), you can be explicit about charset and collation, or it can inherit from the defaults given for the table. Again, the column's definition is now frozen.

Doing SHOW CREATE TABLE will show an override or continue to leave the implicit inheritance. SELECT .. FROM information_schema.columns .. makes it clearer that every column has a charset and collation.

That is, there is no "dynamic" inheritance at "run time". The inheritance is only when the table or column is created.

Note that each charset has a default collation. And each collation belongs to a specific charset (see the first part of the collation name). So, specifying either the charset or collation implicitly specifies the other.

Upvotes: 1

fancyPants
fancyPants

Reputation: 51938

Specifying a character set on database level is in fact defining the default character set for tables.

Doing the same for tables defines the default character set for columns.

Since you can't go further down the road, specifying a character set on a column will definitely use the character set for everything you store in that column.
When you don't specify a character set on column level, the character set of the table is used. And if that is not specified the character set of the database is used.

Upvotes: 2

Related Questions