I'm confused about two examples from the MySQL docs:
"Specifying a character set":
ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;
and "Converting a character set": ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;
Ignoring the algorithm and lock values, what's the different between the two? It seems like they both change the character set of an existing table?
The first example only changes the table's default character set.
This is a metadata-only change, since it doesn't actually change any data, it only changes the table's default.
The default only applies when you add string columns to the table later, without specifying a character set.
Changing the table's default character set does not convert any of the current string columns in the table.
They will remain stored in the former character set.
You can convert columns to the new character set one by one:
ALTER TABLE tbl_name MODIFY COLUMN column1 VARCHAR(50) CHARACTER SET utf8mb4;
Or you can convert all string columns in one alter:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4;
Either of these conversion steps needs to perform a table-copy to rewrite the data. If you have several string columns and you want to convert them all, you might as well use CONVERT TO CHARACTER SET so you only have to do the table-copy once.