CODEWITHSUNDEEP
mysqldatabasephpmyadmin
mathieulbt
mathieulbt

Reputation: 83

How to change the collation of an existing MySQL database?

How to change the collation of an existing MySQL database?

This is a Drupal 8 site

Currently my database is with collation utf8_general_ci

Is it possible to convert an existing database with collation utf8mb4_unicode_ci?

Is there a risk of damaging the database?

enter image description here

EDIT

Finally the tables are in the format utf8_general_ci

Why is it written in the "Operations" tab utf8_general_ci?

What is the difference between utf8mb4_general_ci and utf8mb4_unicode_ci?

Which "Collation" is currently recommended ?

enter image description here

Upvotes: 4

Views: 5922

Answers (2)

Isaac Bennetch
Isaac Bennetch

Reputation: 12462

Changing the collation of a database will only affect new tables you create in that database, but it's easy to do: look for the "Collation" dropdown from the database's "Operations" tab:

Collation dropdown

You can go further and similarly change the table-level collation, again from the Operations tab of each table.

Now if you want to change the existing data, it gets to be a little more difficult. You can use phpMyAdmin to do so, but should have a backup of your database first. From the table level Structure tab, click the "Change" link to edit an individual column, or edit several at once from the same column. There, you can change the existing data and phpMyAdmin will attempt to convert it. This should work, but there's always the chance of data loss or corruption when changing collations (in particular when a character doesn't exist in the new collation), so a warning is shown:

Collation conversion warning

Upvotes: 1

Barmar
Barmar

Reputation: 782693

You can use the ALTER DATABASE query:

ALTER DATABASE databasename COLLATE utf8mb4_unicode_ci;

This just changes the default collation of the database, which is used when creating new tables, it doesn't modify any existing tables. However, the documentation mentions this caveat regarding stored routines:

If you change the default character set or collation for a database, stored routines that use the database defaults must be dropped and recreated so that they use the new defaults. (In a stored routine, variables with character data types use the database defaults if the character set or collation are not specified explicitly.

Upvotes: 3

Related Questions