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.