How to Change the Character Set (And Collation) Throughout a Database

How to change the CHARACTER SET (and COLLATION) throughout a database?

change database collation:

ALTER DATABASE  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

change table collation:

ALTER TABLE  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

change column collation:

ALTER TABLE  MODIFY  VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

---

What do the parts of utf8mb4_0900_ai_ci mean?

3 bytes -- utf8
4 bytes -- utf8mb4 (new)

v4.0 --   _unicode_
v5.20 --  _unicode_520_
v9.0 --   _0900_ (new)

_bin      -- just compare the bits; don't consider case folding, accents, etc
_ci       -- explicitly case insensitive (A=a) and implicitly accent insensitive (a=á)
_ai_ci    -- explicitly case insensitive and accent insensitive
_as (etc) -- accent-sensitive (etc)

_bin         -- simple, fast
_general_ci  -- fails to compare multiletters; eg ss=ß, somewhat fast
...          -- slower
_0900_       -- (8.0) much faster because of a rewrite

More info:

• What are the differences between utf8_general_ci and utf8_unicode_ci?
• What's the difference between utf8_general_ci and utf8_unicode_ci?
• How to change collation of database, table, column?
• What's the difference between utf8_general_ci and utf8_unicode_ci?

How to convert an entire MySQL database characterset and collation to UTF-8?

Use the ALTER DATABASE and ALTER TABLE commands.

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Or if you're still on MySQL 5.5.2 or older which didn't support 4-byte UTF-8, use utf8 instead of utf8mb4:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

How to change collation of database, table, column?

You need to either convert each table individually:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 

(this will convert the columns just as well), or export the database with latin1 and import it back with utf8mb4.

How to change table (default) collation in MySQL WITHOUT converting the columns

ALTER TABLE has two different ways to change a table's character set. The one mentioned in the question is meant to convert all character based columns into the given charset.

Setting the tables DEFAULT character set will not change the character set of the existing columns.

ALTER TABLE tbl_name
  [[DEFAULT] CHARACTER SET charset_name]
  [COLLATE collation_name]

Related doc entries:

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
http://dev.mysql.com/doc/refman/5.7/en/charset-table.html

Character set and collation in database

$ mysql ... --default-character-set=utf8 < tables.sql
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

MySQL needs to know what encoding the data you're sending it is in. To do this, you need to set the connection encoding. When connecting to MySQL from a client, you usually run a SET NAMES query or use an equivalent call on your API of choice to do so. On the command line, the --default-character-set option does this. It needs to be set to whatever encoding your file is in.

How to change the default collation of a table?

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

alter 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

How to change all the tables in my database to UTF8 character set?

mysqldump --user=username --password=password --default-character-set=latin1 --skip-set-charset dbname > dump.sql
sed -r 's/latin1/utf8/g' dump.sql > dump_utf.sql
mysql --user=username --password=password --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;"
mysql --user=username --password=password --default-character-set=utf8 dbname < dump_utf.sql

---

Related Topics