217
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?

kenorb
  • 155,785
  • 88
  • 678
  • 743
simplfuzz
  • 12,479
  • 24
  • 84
  • 137
  • possible duplicate of [Change database collation](http://stackoverflow.com/questions/5906585/change-database-collation) – Michel Ayres Feb 14 '14 at 10:40
  • You may wish to alter the correct answer as myself and many others seem to agree that the 2nd one is the correct one. – Ahi Tuna Jun 28 '14 at 15:42

4 Answers4

670

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

Nikki Erwin Ramirez
  • 9,676
  • 6
  • 30
  • 32
  • 6
    What are the implications of changing the default character set? Does it update existing data and therefore need to run through the table and make updates, locking it, etc.? – grant Nov 03 '15 at 21:12
  • `utf8_bin` is better, isn't? What's the difference between `utf8_bin` and `utf8_unicode_ci`? – Jasom Dotnet Dec 31 '15 at 03:22
  • 1
    That's just an example I used. The difference is that `utf8_bin` is case-sensitive and `utf8_unicode_ci` is case-insensitive. One isn't necessarily better than the other -- use whatever suits your needs. – Nikki Erwin Ramirez Jan 06 '16 at 08:07
  • 12
    @JasomDotnet you should now use utf8mb4_unicode_ci http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci – baptx Aug 07 '16 at 17:57
  • 4
    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
  • 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 https://stackoverflow.com/q/30074492/772035 – Paul Mar 12 '19 at 17:46
36

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.

fredrik
  • 13,282
  • 4
  • 35
  • 52
  • 9
    In fact, MySQL has **FIVE** Levels of collation, there is a character set level default collation setting that many people forget about. – Devy Sep 24 '15 at 18:07
  • 7
    There's also connection collation `show variables like "collation%";`, so total is ***SIX***. – Dzmitry Lazerka Nov 02 '16 at 21:09
9

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.

Don Werve
  • 5,100
  • 2
  • 26
  • 32
4

may need to change the SCHEMA not only table

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

as Rich said - utf8mb4

(mariaDB 10)

Jazerix
  • 4,729
  • 10
  • 39
  • 71
AssyK
  • 37
  • 1
  • 6