How to change the default collation of a table?

Question

create table check2(f1 varchar(20),f2 varchar(20));

creates a table with the default collation latin1_general_ci;

alter table check2 collate latin1_general_cs;

show full columns from check2;

shows the individual collation of the columns as 'latin1_general_ci'.

Then what is the effect of the alter table command?

Answer 1

If you want to change the default character set and collation of a table including those of existing columns (note the convert to clause) then use the below code:

alter table <some_table> convert to character set utf8mb4 collate utf8mb4_unicode_ci;

For more information, you can refer to https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html