mysqlsqlcollation

How to change the default collation of a table?

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?

Solution

  • To change the default character set and collation of a table including those of existing columns (note the convert to clause):

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

    Edited the answer, thanks to the prompting of some comments:

    Should avoid recommending utf8. It's almost never what you want, and often leads to unexpected messes. The utf8 character set is not fully compatible with UTF-8. The utf8mb4 character set is what you want if you want UTF-8. – Rich Remer Mar 28 '18 at 23:41

    and

    That seems quite important, glad I read the comments and thanks @RichRemer . Nikki , I think you should edit that in your answer considering how many views this gets. See here https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html and here What is the difference between utf8mb4 and utf8 charsets in MySQL? – Paulpro Mar 12 at 17:46