# Change character set and collation

**URL:** <https://forums.percona.com/t/change-character-set-and-collation/23325>\
**Category:** Other MySQL® Questions\
**Created:** [July 4, 2023, 12:14am UTC](https://forums.percona.com/t/change-character-set-and-collation/23325 "2023-07-04T00:14:48Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Venkat](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/venkat/32/5328_2.png) [@Venkat](https://forums.percona.com/u/Venkat)\
**Post date:** [July 4, 2023, 12:14am UTC](https://forums.percona.com/t/change-character-set-and-collation/23325/1 "2023-07-04T00:14:48Z")

</div>

Hello,

We are upgrading from MySQL 5.7 to MySQL 8. As part of the process, we would like to change utf8 (utf8mb3) to utt8mb4 collation utf8mb4\_0900\_ai\_ci. There are two ways to do it:

1. ALTER TABLE \<table\_name\> CONVERT TO CHARACTER SET \<charset\_name\> COLLATE \<collation\_name\> ;
2. ALTER TABLE \<table\_name\> CHARACTER SET \<charset\_name\> COLLATE \<collation\_name\>;

Current understanding is that the (1) will physically convert the character sets to fit into the new character number of bytes (utf8mb3 → 3 bytes, utf8mb4 → 4 bytes) and collation while (2) will merely set character bytes usage (character set) and ordering (collation) for new rows created or updated but the existing rows will not be touched.

Is this understanding correct?

Thank you!

---

<div class="post-metadata">

**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\
**Post date:** [July 4, 2023, 6:56am UTC](https://forums.percona.com/t/change-character-set-and-collation/23325/2 "2023-07-04T06:56:40Z")

</div>

Hello @Venkat welcome back,

Thank you for sharing this observation and you’re right. The easy answer is “Yes” but let me write my understanding derived from [mysql documentation](https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-character-set).

`ALTER TABLE <table_name> CONVERT TO CHARACTER SET <charset_name> COLLATE <collation_name>`  
will physically convert the character sets of the table and its columns to fit into the new character set’s number of bytes. It will also change the collation of all character columns.

`ALTER TABLE <table_name> CHARACTER SET <charset_name> COLLATE <collation_name>`  
will only set the character set and collation for new rows that are created or updated in the table. It will not touch the existing rows’ character set or collation. Note that “DEFAULT” is implicit here and that’s why it just affects to new changes.

In addition to that,  
we can also note that if you specify `CONVERT TO CHARACTER SET binary`, the CHAR, VARCHAR, and TEXT columns will be converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). This is important to ensure that the new column is long enough to store as many characters as the original column.  
To avoid data type changes during character set conversion, it is recommended to use the `MODIFY` clause instead of `CONVERT TO CHARACTER SET` for individual columns. This allows you to change the character set and collation without altering the data type.

Thanks,  
K

---

<div class="post-metadata">

**Author:** ![Venkat](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/venkat/32/5328_2.png) [@Venkat](https://forums.percona.com/u/Venkat)\
**Post date:** [July 4, 2023, 8:36pm UTC](https://forums.percona.com/t/change-character-set-and-collation/23325/3 "2023-07-04T20:36:30Z")

</div>

Hello @kedarpercona,

Thank you for confirming and the additional information. I didn’t know of the data type change when using convert. I then tried this on one table. I see the TEXT type columns were changed to MEDIUMTEXT.type columns but then it didn’t change the character set or collation for those types. Is this data type change is what you meant?

However, when I attempted alter table modify character set collate on another table with same structure. I see it did not change the data type of TEXT column but it did change the character set and collation for this column.

One additional think I noticed is that if we did alter table…character set … collate…;, it works and will have new character set and collation for future rows. However the metadata in the information\_schema still holds the original character set and collation for the table columns. This will mislead people looking into information\_schema for table/column definition. If we do the character set and collation at the column level, exactly as you specified, it correctly retains the data type and also reflects in the metadata.

So here is the summary:

1. alter table convert – physically changes data but risk of changing data types, also exceeding the size unless using binary version (String) as you suggested.
2. alter table character set – No change to physical data, but future data will be stored this way. The metadata in the information\_schema does not reflect the character set change. Hence this must be avoided
3. alter table modify column character set – Preferred way. Retains data type, updates the metadata for the character set, collation change, will no change existing physical data. The only caveat is to generate the alter table modify column statements as pretty much all column properties will have to be reconstructed from the metadata (type, size/scale, precision, NULL/NOT NULL, uniqueness etc).

Thanks again!

Regards.  
-Venkat