Removing per column and table collation and charset

I recently completed a transition of a database from CHARSET=utf8 COLLATE=latin1 to CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. During the process tables were migrated one at a time by altering their respective charsets and collation properties.

Now that the transition has been completed and all of the servers and the database itself have now been set to use utf8mb4 and utf8mb4_unicode_ci, how would I go about removing those properties from the columns and tables so they inherit the default value set on the server?

For instance presently if I execute a SHOW FULL COLUMNS FROM table; Collation for most columns is set to utf8mb4_unicode_ci rather than NULL.

As a programmer, it is easy to assume that there is "inheritance" in the database. If a value is null, look at the parent.

There is no inheritance

Text-type columns cannot have a null character set or collation. Each field will have a fixed character set and collation when created. Imagine changing to a completely different character set at the table or database level, and existing data in the existing columns automatically inherit the new character set. It would probably look corrupt.

Here is some documentation:
https://dev.mysql.com/doc/refman/8.0/en/charset-table.html


MySQL chooses the table character set and collation in the following manner:


If both CHARACTER SET charset_name and COLLATE collation_name are specified, character set charset_name and collation collation_name are used.


If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used. To see the default collation for each character set, use the SHOW CHARACTER SET statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.


If COLLATE collation_name is specified without CHARACTER SET, the character set associated with collation_name and collation collation_name are used.


Otherwise (neither CHARACTER SET nor COLLATE is specified), the database character set and collation are used.

Just to be clear, the character set and collation are chosen when the column is created. If you change them, you should also convert the data at the same time.

StackExchange Database Administrators Q#102227, answer score: 3