"Alter Table" modify multiple columns vs multiple Alter table single column in INNODB?

Question

I am updating collations of my database tables and columns from utf8bin to utf8mb4_bin. Engine is INNODB with file per table enabled.

I used a query to generate identify all utf8 columns from information_schema database and generated corresponding queries for each.

The generated queries contains "ALTER TABLE" queries for each column separately.

Example:

ALTER TABLE mydb.`cwd_group`
MODIFY `active` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
ALTER TABLE mydb.`cwd_group`
MODIFY `local` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

Is it a recommened to join these two as a single statement? What will be the drawbacks?

ALTER TABLE mydb.`cwd_group`
    MODIFY `active` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
   ,MODIFY `local` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
;

I am guessing by joining them as single statement, disk IO is reduced as it not required to write the whole table data multiple times (i.e., to temporary ibd file #sql-*) or does it write only the column data to temporary file? Or how much extra space may it use for the temporary file if I merge the statements?

In the end, I want to run these alter tables in parallel that my server can handle.

Answer 1

You must do it all in a single ALTER TABLE command. Think of it : You are changing the collation of a column, so ALGORITHM=COPY must be done. This requires a full table lock. It is better to execute the collation change of multiple columns in a single ALTER TABLE command than to do it multiple time

There is no drawback in this instance. Why ? Back in May 12, 2011, I wrote about MySQL 5.0's old behavior in executing multiple column modifications (Does MySQL still handle indexes in this way?). Under the hood, MySQL used to execute multiple column changes as individual ALTER TABLE commands. I pointed this out to MySQL back on Oct 10, 2006. They had fixed it since.

Now that ALTER TABLE handles multiple column changes as a single operation, there is no need to change the columns one by one.

Let's say it takes 5 min to change one column. That's 5 minutes the table is locked and no INSERTs, UPDATEs, or DELETEs can happen. You would basically double the time changing two columns to 10 min, triple the time changing three columns to 15 min, and so forth.

Just run the one ALTER TABLE command. MySQL handles it correctly now.

ALTER TABLE mydb.`cwd_group`
    MODIFY `active` char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
   ,MODIFY `local`  char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
;

GIVE IT A TRY !!!

P.S. You need to specify a length for char. That's why I put (25).

If you are not sure of the length, then use VARCHAR(25).

Answer 2

This does all the columns (of one table) at the same time:

ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;