How to change the default collation of a table?

  • Added:
  • |
  • In: Basic PHP
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?

This Question Has 3 Answeres | Orginal Question | dharm0us

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 utf8 collate utf8_unicode_ci;

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 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.

Meet with owner

Sajjad Hossain

Hey, I am Sajjad, working in web development sector since 2012. I love to do amazing things. Let's do a project together.
Connect Social With PHPAns