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

User · Answer

MySQL has 4 levels of collation  server  database  table  column  If you change the collation of the server  database or table  you don t change the setting for each column  but you change the default collations   E g if you change the default collation of a database  each new table you create in that database will use that collation  and if you change the default collation of a table  each column you create in that table will get that collation

User · Answer

To change the default character set and collation of a table including those of existing columns  note the convert to clause    alter table  lt some table gt  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

User · Answer

may need to change the SCHEMA not only table  ALTER SCHEMA   lt database name gt    DEFAULT CHARACTER SET utf8mb4  DEFAULT COLLATE utf8mb4 unicode ci  as Rich said - utf8mb4      mariaDB 10

User · Answer

It sets the default collation for the table  if you create a new column  that should be collated with latin general ci -- I think   Try specifying the collation for the individual column and see if that works   MySQL has some really bizarre behavior in regards to the way it handles this

[mysql] How to change the default collation of a table?

Examples related to mysql

Examples related to sql

Examples related to collation