CODEWITHSUNDEEP
mysqlsqlcollation
simplfuzz
simplfuzz

Reputation: 12935

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?

Upvotes: 227

Views: 247666

Answers (4)

AssyK
AssyK

Reputation: 57

You may need to change the schema, not only the table.

ALTER SCHEMA `<database name>` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

As Rich said, it is utf8mb4.

Upvotes: 5

Nikki Erwin Ramirez
Nikki Erwin Ramirez

Reputation: 9964

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

Upvotes: 685

fredrik
fredrik

Reputation: 13550

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.

Upvotes: 38

Don Werve
Don Werve

Reputation: 5120

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.

Upvotes: 9

Related Questions