how to change collation connection and character set in simpler way

Question

I am having a database in MariaDB with Latin1 character set and collation connection as Latin1_general_ci. Now I want to change it to utf8mb and utf8mb_general_ci. And my database is of 650GB. So, instead of changing the collation for each column, table and database, is there any other possibility?

Also, it is not allowing me to change the character set and collation with all the foreign keys applied. I have around 2,500 foreign keys, and dropping and recreating them would take approximately 72 hours.

Connection character set has no impact on the character set of the data in the tables. If you need to change the characyer set of data already in the tables, then that's a data conversion operation thay will take a while for 650GB worth of data — Shadow
– Shadow, Commented Jul 14 at 12:14
Note that utf8mb_general_ci quite non-standardized hack from long long ago. There's now UCA standards on collations. If you're serious about getting collations right, suggest a 11.4+ MariaDB version and the system variable @@character_set_collations, In 10.6 utf8mb4_unicode_ci is probably a better choice. — danblack
– danblack, Commented Jul 15 at 23:58

ysth · Accepted Answer · 2025-07-14 16:54:15Z

0

You should change each database (so new tables default correctly):

ALTER DATABASE dbname COLLATE = collation_name;

But for tables, for convenience, you can change the table defaults and all character columns together with just

ALTER TABLE table_name
    CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

Note that it may also change some types, e.g TEXT to MEDIUMTEXT, to ensure the maximum number of characters they store doesn't decrease

edited Jul 14 at 16:54

answered Jul 14 at 13:01

ysth

99.1k6 gold badges126 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

danblack Jul 15 at 23:52

There may be a default collation for the table that you'd want to update too. ... DEFAULT COLLATE colllation_name. Also note some column changes may be unneeded like if they only contain a limited charset won't benefit from a new charset/collation. Always make sure the columns you JOIN are the same charset and collation.

ysth Jul 16 at 2:04

the ALTER TABLE...CONVERT TO CHARACTER SET does set the table default (as well as all the applicable columns), both collation and character set. If you don't specify the collation, it uses the default collation for the charset (even if the table default or column character set isn't changing)

danblack Jul 16 at 3:50

Ah nice. I should update the documentation to reflect that. Thank you.

Collectives™ on Stack Overflow

how to change collation connection and character set in simpler way

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related