Change collation on 2 columns only

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!

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

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!

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

1 Like