This page describes how to update the character set and collation of Cloud SQL databases.
Overview
When you create a database in Cloud SQL for MySQL, you can specify the character set and collation configuration for a database. If you don't specify custom values for these settings, then the database uses the default value. In MySQL, you can specify custom values for the character set and collation at different object levels, including databases, tables, and columns. For more information about using character set and collation configurations in MySQL, see Character Sets, Collations, Unicode.
If you want to change the character set or collation of your database after you've already created the database and some objects in the database, then you can do so by following the procedure in this document.
This procedure consists of the following steps:
- Check current character set and collation values.
- Determine what level of update to perform.
- Take a backup of the instance.
- Generate rebuild commands for stored database objects.
- Perform updates to your databases, and if necessary, tables and columns.
- Validate the updates and look for errors.
- Recreate your stored database objects.
- Take another backup of the instance.
Check the current character set and collation values
Check the existing values that are configured for character set and collation for your database and database objects. When you check your database, you also need to check the configuration of its tables, columns, and stored objects. You can use the following statements to check your databases and its database objects.
Check your instance configuration
To check the default global values on the instance (server) level, start a MySQL shell and enter the following command:
mysql> SELECT @@character_set_server,@@collation_server;
Example output:
+------------------------+--------------------+
| @@character_set_server | @@collation_server |
+------------------------+--------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
+------------------------+--------------------+
1 row in set (0.00 sec)
You can check that the default global values are different from the custom values that you'll provide for your database later.
Check your database configuration
To check the existing character set and collation values on the database level, use the following statement. After you run each statement, make a list of all the databases and database objects that you need to update with new character sets or collation values. The list depends on which databases you want to change and how many tables, columns, and database objects require configuration updates.
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE schema_name NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');
For example, you receive the following output:
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def | test1 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test2 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test3 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test4 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
However, you want to change the databases to the following custom character set values:
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def | test1 | ascii | ascii_general_ci | NULL | NO |
| def | test2 | latin1 | latin1_swedish_ci | NULL | NO |
| def | test3 | utf16 | utf16_general_ci | NULL | NO |
| def | test4 | ucs2 | ucs2_general_ci | NULL | NO |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
Then, in this case, for each database that has a value that you want to change, you need to update each object that resides in the database.
Check the configuration of your tables
To check the existing values for the tables, use the following statement:
SELECT T.table_name, T.table_schema, CCSA.character_set_name, CCSA.collation_name FROM information_schema.`tables` T, information_schema.`collation_character_set_applicability` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema NOT IN ('performance_schema', 'mysql', 'sys','information_schema');
Example output:
+------------+--------------+--------------------+-------------------+
| TABLE_NAME | TABLE_SCHEMA | CHARACTER_SET_NAME | COLLATION_NAME |
+------------+--------------+--------------------+-------------------+
| t1 | test1 | ascii | ascii_general_ci |
| t2 | test2 | latin1 | latin1_swedish_ci |
| t3 | test3 | utf16 | utf16_general_ci |
| t4 | test4 | ucs2 | ucs2_general_ci |
+------------+--------------+--------------------+-------------------+
Check the configuration of your table columns
To check the values for columns, use the following statement:
SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql');
Example output:
+--------------+------------+-------------+--------------------+------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME |
+--------------+------------+-------------+--------------------+------------------+
| test1 | t1 | c | ascii | ascii_general_ci |
| test1 | t1 | i | NULL | NULL |
| test2 | t2 | j | NULL | NULL |
| test2 | t2 | v | ascii | ascii_general_ci |
| test3 | t3 | k | NULL | NULL |
| test3 | t3 | summary | utf16 | utf16_general_ci |
| test4 | t4 | col | ucs2 | ucs2_general_ci |
| test4 | t4 | p | NULL | NULL |
+--------------+------------+-------------+--------------------+------------------+
Check the active schema or database
To check the details for the active schema or database, use the following statement.
SELECT TABLE_SCHEMA, TABLE_NAME, CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET, COLUMN_NAME, COLUMN_TYPE, C.CHARACTER_SET_NAME FROM information_schema.TABLES AS T JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME) JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME) WHERE TABLE_SCHEMA=SCHEMA() AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext', 'set' ) ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
Example output:
+------------+
| DATABASE() |
+------------+
| test3 |
+------------+
+--------------+------------+------------------+-------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | DEFAULT_CHAR_SET | COLUMN_NAME | COLUMN_TYPE | CHARACTER_SET_NAME |
+--------------+------------+------------------+-------------+-------------+--------------------+
| test3 | t3 | utf16 | summary | text | utf16 |
+--------------+------------+------------------+-------------+-------------+--------------------+
Check the configuration of your stored procedures
To check the values for stored procedures, use the following statement:
SHOW PROCEDURE STATUS WHERE db = 'DB_NAME';
Replace DB_NAME with the name of the database.
Example output:
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test2 | p1 | PROCEDURE | root@% | 2024-08-09 11:47:05 | 2024-08-09 11:47:05 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | latin1_swedish_ci |
| test2 | p2 | PROCEDURE | root@% | 2024-08-09 11:48:36 | 2024-08-09 11:48:36 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | latin1_swedish_ci |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
To get the definition of a stored procedure, use the following statement:
SHOW CREATE PROCEDURE DB_NAME.SP_NAME;
Replace the following:
- DB_NAME: the name of the MySQL database
- SP_NAME: the name of the stored procedure
If a stored procedure isn't using your intended character set or collation value, then you might need to drop and recreate it with the new character set or collation.
Check database collation mapping
In the previous example output, the stored
procedure is created with latin1_swedish_ci as the default collation and the
corresponding character set is latin1. This means all processing happens using
the latin1 character set (unless specific parameters of the procedure are
tagged with a custom character set.) If you change only the database-level character
set and collation, then the database-level update doesn't update the stored
procedure automatically. The procedure continues to operate with the
latin1 character set and the respective collation.
Dropping and recreating the stored procedure ensures that the procedure starts
to use the new character set and new collation of the database that you just updated.
You can find out the mapping between database collation and character set by
querying the information_schema.COLLATION_CHARACTER_SET_APPLICABILITY table.
Use the following statement as an example:
SELECT CHARACTER_SET_NAME
FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
WHERE COLLATION_NAME = (SELECT database_collation FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = "test2" and ROUTINE_NAME = "p1");
Example output:
+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| latin1 |
+--------------------+
Check the configuration of your triggers
To check the values for triggers, use the following statement as an example.
SHOW TRIGGERS FROM DB_NAME;
Replace DB_NAME with the name of the MySQL database.
To get the definition of a trigger, use the following example statement.
SHOW CREATE trigger DB_NAME.TRIGGER_NAME;
Replace the following:
- DB_NAME: the name of the MySQL database
- TRIGGER_NAME: the name of the trigger
If the trigger isn't using your intended character set or collation value, then you might need to drop and recreate it with the new character set or collation. Check that the trigger is using the correct database collation mapping. For more information about checking database collation, see Check database collation mapping.
Check the configuration of your events
To check the values for events, use the following statement as an example.
SHOW EVENTS FROM DB_NAME;
Replace DB_NAME with the name of the database.
To get the definition of an event, use the following example statement.
SHOW CREATE EVENT DB_NAME.EVENT_NAME;
Replace the following:
- DB_NAME: the name of the database
- EVENT_NAME: the name of the event
If the event isn't using your intended character set or collation value, then you might need to drop and recreate it with the new character set or collation. Check that the event is using the correct database collation mapping. For more information about checking database collation, see Check database collation mapping.
Check the configuration of your functions
To check the values for functions, use the following statement as an example.
SHOW FUNCTION STATUS WHERE db = 'DB_NAME';
Replace DB_NAME with the name of the database.
To get the definition of a function, use the following example statement.
SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;
Replace the following:
- DB_NAME: the name of the MySQL database
- FUNCTION_NAME: the name of the function
If the function isn't using your intended character set or collation value, then you might need to drop and recreate it with the new character set or collation. Check that the function is using the correct database collation mapping. For more information about checking database collation, see Check database collation mapping.
I character set or collation. Check that the function is using the correct database collation mapping. For more information about checking database collation, see Check database collation mapping.
Determine what level of updates to perform
After you have checked the character set and collation configuration of your database, you have a list of databases and possibly a list of database objects that you need to update.
The number of tasks you'll need to perform depend on the type of the database objects that require configuration changes.
- Perform database-level updates. You need to update MySQL metadata only.
- Perform database and table-level updates. You need to update MySQL metadata only.
- Perform database, table, and column-level updates. You must update the metadata and rebuild tables and database objects. These updates can take the most amount of time; however, you can use one of the provided example scripts to update all of the tables and columns in a given database.
Take a backup of the instance
Before you do any of these updates, take a backup of your instance. For more information, see Create an on-demand backup. We recommend that you take the backup without any DDL operations running on the instance.
Generate rebuild commands for the stored database objects in your database
If you need to drop and recreate any stored database objects in your databases with the
new character set and collation, then use the mysqldump tool to generate the
commands now. After you update your database and table columns, you'll use the
generated mysqldump file to rebuild the stored database objects in your
database. This command generates statements for all stored procedures,
functions, triggers, and events.
Run the following command so you can use the output to recreate all the stored procedures, functions, and events of a particular database later (when you recreate stored database objects).
$ mysqldump -uDBAuser -p -h IP_ADDRESS \ -P 3306 DB_NAME --no-data --no-create-db \ --no-create-info --routines --triggers --events \ --set-gtid-purged=OFF --events > dump_objects.sql
Replace the following:
- IP_ADDRESS: IP address of the Cloud SQL for MySQL instance
- DB_NAME: the name of the MySQL database
Perform updates to your databases, and if necessary, tables and columns.
In this step, you update the character set and collation values of your databases. Also, you might need to rebuild your tables and columns.
Perform database-level updates
If you are performing database-level updates only, then run the following statement to update your database:
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Replace the following:
- DB_NAME: the name of the database to update
- NEW_CHARSET: the new character set to apply to the database
- NEW_COLLATION: the new collation to apply to the database
Perform database and table-level updates
If you are performing database and table-level updates, then do the following:
Run the following statement to update your database:
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Replace the following:
- DB_NAME: the name of the database to update
- NEW_CHARSET: the new character set to apply to the database
- NEW_COLLATION: the new collation to apply to the database
For each table that you want to update, run the following statement:
ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Replace the following:
- TABLE_NAME: the name of the table to update
- NEW_CHARSET: the new character set to apply to the table
- NEW_COLLATION: the new collation to apply to the table
Perform database, table, and column-level updates
At this level, you need to re-encode existing data to comply with the new character set and collation settings. If you are performing a database, table, and column-level updates, then do the following:
Before proceeding with updates to the table columns, review the following considerations:
- The
ALTER TABLEcharacter set and collation conversion causes the table to rebuild and locks the table. As a result, any active query that tries to access the table is blocked. - The
ALTER TABLEcharacter set and collation conversion takes time because it fetches the complete table in memory. This operation might increase the latency of any workload running in parallel because the buffer pool pages are being used for the updated table. - If there are queries accessing multiple tables and some of the tables are being updated while others are not yet updated, then the queries might produce inconsistent results during this transaction time.
- The process of running an
ALTER TABLEcharacter set and collation conversion creates a shadow table, so plan disk usage accordingly. - The
ALTER TABLEcharacter set and collation conversion also causes any indexes on the table to rebuild. - You don't need to update replica instances separately.
ALTER,DROP, andCREATEcommands are replicated automatically. However, running DDL commands can cause replica lag time to increase during the operation. - If you restore any of the databases from a backup taken from before you applied these updates, then you'll need to re-apply the updates.
- The
Check your table for any data tuples that might be incompatible with the new character set. If there are any incompatibilities, then you need to fix them before you update the character set. Otherwise, you'll encounter errors during the conversion process. You can use the following
SELECT CONVERTstatement to validate your data with new character set.SELECT COLUMN_NAME,CONVERT(COLUMN_NAME USING NEW_CHARSET) FROM TABLE_NAME WHERE COLUMN_NAME != CONVERT(COLUMN_NAME USING NEW_CHARSET);
SELECT c,CONVERT(c USING 'ascii') FROM t WHERE c != CONVERT(c USING ascii);Example output:
+------+--------------------------+ | c | convert(c using 'ascii') | +------+--------------------------+ | é | ? | | é | ? | | £ | ? | +------+--------------------------+In this example, since the output returns these three tuples, there are three inconsistent values that need to be fixed. Before moving to the next step, examine the output of your query, and fix the data until your query returns zero tuples for all the columns that need conversion. You can also combine multiple columns in the same
SELECTquery.Choose an option for updating the character set and collation for your tables at the column level.
If you want to update the character set for a very specific table column, then follow the steps in the Column-level tab where you only have to re-encode selected columns. Otherwise, follow the steps in the Table-level tab which re-encode the full table.
Table-level
Run the following statement to update your database:
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Replace the following:
- DB_NAME: the name of the database to update
- NEW_CHARSET: the new character set to apply to the database
- NEW_COLLATION: the new collation to apply to the database
For each affected table, run the following statement to update your table:
ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Replace the following:
- TABLE_NAME: the name of the table to update
- NEW_CHARSET: the new character set to apply to the table
- NEW_COLLATION: the new collation to apply to the table
Update at table level so that all columns that are part of the table are updated with the new character set and collation. This option rebuilds the table completely. To use this option, run the following statement:
ALTER TABLE DB_NAME.TABLE_NAME CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Replace the following:
- TABLE_NAME: the name of the table to update
- NEW_CHARSET: the new character set to apply to the table
- NEW_COLLATION: the new collation to apply to the table