Change collation on 2 columns only

MySQL & MariaDB
1

Hi everyone!

I need to change the collation of only 2 columns in a table (with ~10 columns) in my database (not the table), the table has ~30 million rows so I am keen to use the Percona toolkit for this, but I am struggling to see if this is possible (just changing the collation of certain columns). I can see how to change the whole table, but as I say this is just for two columns. Inciedntally we are going from Latin1 => utf8mb4.

My SQL (which is sound and works on MySQL natively) is like this:

ALTER TABLE tablename

MODIFY COLUMN column1 CHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,

MODIFY COLUMN column2 CHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

Any help would be appreciated, thanks!

2

Hello @mark1,
Out of curiosity, why only change these two columns instead of the entire table? utf8mb4 is the current standard. Do you have other columns that need to be in a different character set?

Yes, pt-online-schema-change can do this for you. The SQL maps near directly to pt-osc:

pt-online-schema-change -d database -t table --alter "MODIFY COLUMN ..., MODIFY COLUMN"

When in doubt, use --dry-run to view the steps and SQL that would be executed.

1 Like
3

Hi matthewb. Thanks for the reply :slight_smile:

The reason for not the whole table is that we are part way through a migration, and we still have some columns in other tables that need to join to this table and they are (for now) still latin1, and when benchmarking we found latin1 => utf8 joins very slow indeed!

We will try your suggestion on the per column basis, thank you!

4

Yes, this is true because of the conversion that must take place. Good job for testing!

1 Like