Recently I was engaged to convert the character set for few MySQL databases from latin1 to UTF8 collation utf8_general_ci. This article describes the approach taken for doing the same.
First I will describe various levels at which we can change the character set and collation and then we will see how to convert existing data in a database to required character set.
Backup your database:
Before even thinking about changing character set for your database, take backup of database using whatever backup method that is tested by you – mysqldump, mysql-enterprise-backup, export etc
In case something goes wrong, we can always have data and recreate requried table/database etc.
Make sure your backups and restore methods are proven, meaning that you have sucecssfully done restore of tables/database etc
Setting Character set at various level:
We can see following parameters for character sets
<pre>root [mysql] >show variables like '%character%set%'; +--------------------------+---------------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.6.16-linux-glibc2.5-x86_64/share/charsets/ | +--------------------------+---------------------------------------------------------------+ 8 rows in set (0.00 sec)
character_set_client – The character set for statements that arrive from the client. If client is not setting any character set while connecting, this character set will be used for statements send by client. Else value set by client during connection will override this value
character_set_connection – The character set used for literals that do not have a character set introducer and for number-to-string conversion.
character_set_database – Character set used by default database. This character set will be used whenever we change database on server and if that database does not have any character set defined.
character_set_filesystem – This character set is used to interpret string literals that refer to file names at file system level, such as in the LOAD DATA INFILE and SELECT … INTO OUTFILE statements.
character_set_results – The character set used for returning query results such as result sets. If client has used character set in its connection, then this value will not be used for returning the result.
character_set_server – Character set defined at the server level. Any new database created will used this character set, unless we are defining character set at database level
character_set_system – The character set used by the server for storing metadata infomration. Example – the return values of the USER(), CURRENT_USER(), SESSION_USER(), SYSTEM_USER(), DATABASE(), VERSION() etc will be retuned in character set assigned to this variable.
character_sets_dir – The directory where all character sets are installed.
We can set character set at following level
Server Level:
We can do this by setting parameter character_set_server in our main my.cnf file. But this needs a bounce. Once MySQL server is bounced it will pick new value of this parameter and new character set will be the one we set for this parameter. But this does not change anything in existing data or objects. Only new database creation will take this effect.
Database Level:
We can alter any database on our MySQL server and change the character set to UTF8. We can use following command:-
ALTER DATABASE <db_name> DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Again, this does not affect existing data or objects. This will only take affect for future objects/tables that we create in this database.
Table Level:
We can use alter table command to set the character set for a table.
ALTER TABLE <table_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;
So, if you have many tables in database, you can use following command to dynamically generate a script which can be used to set character set for all required tables in database
SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_unicode_ci;") AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<db_name>';
But this will not change existing columns/data in the table. This will take effect only for new columns getting added to the table.
Changing Character set of existing data:
Above steps are required for setting the character set at various level so that future objects and data will be created in UTF8
Now, for changing character set for existing data, we need to change character set for every text columns of every table in database where we want to change it
We can use multiple approaches for converting character set for existing data and percona blog has provided a very good reference for these methods along with advantages and disadvantages –
Here, I would like to highlight difference between 2 methods that can be used to convert character sets
Doing at table level:
Following command can be used to convert character set at table level
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
This will take care of converting the character set for all columns in table. problem with this approach is that, if you have TEXT columns (TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT, VARCHAR, ENUM), it can end up changing the data type for these columns. Example of the same is giving in above percona blog where TEXT got converted to MEDIUMTEXT
Best and careful way to convert character set is to do it for each text column separately on each table.
You can use following command to change the character set for column
alter table <table_name> change <column_name> <column_name> CHARACTER SET UTF8;
Example:
alter table P1_TABLE change COL1 COL1 TEXT CHARACTER SET UTF8;
In above command/example, we used column name twice. That is required.
But wait, can above approach convert data correctly to required character set?
It may not. Check this article which describes issues we face when we directly try to convert the character set of the column.
Sometimes directly converting can grabble the data. Best way is to convert to binary equivalent and then convert the data type and character set of the column to required once. Following command can be used
alter table P1_TABLE change COL1 COL1 BLOB; alter table P1_TABLE change COL1 COL1 TEXT CHARACTER SET UTF8;
Since, my column had the text data type, its equivalent is BLOB. Following are the binary equivalent of various text data types
- CHAR –> BINARY
- TEXT –> BLOB
- TINYTEXT –> TINYBLOB
- MEDIUMTEXT –> MEDIUMBLOB
- LONGTEXT –> LONGBLOB
- VARCHAR() –> VARBINARY() (Use same data length)
Automating character set conversion:
You can create simple script with all required commands using following dynamic SQL
Note, that if CHARACTER_SET_NAME is NULL in COLUMNS table for columns, it means that those columns are numbers or binary or of data types which does not need character set conversion
Following dynamic SQL can be used to create automatic script
select concat("alter table ",TABLE_NAME," change ",COLUMN_NAME," ",COLUMN_NAME," BLOB;",
"alter table ",TABLE_NAME," change ",COLUMN_NAME," ",COLUMN_NAME," ",IF(DATA_TYPE in ('varchar','char'),concat(DATA_TYPE,"(",CHARACTER_MAXIMUM_LENGTH,")"),DATA_TYPE)," CHARACTER SET utf8;")
from information_schema.columns
where TABLE_SCHEMA = '<db_name>'
and DATA_TYPE <> 'ENUM'
and CHARACTER_SET_NAME is not null;
ENUM case is different. You need to specify all ENUM value when you convert back to required data type and character set format. Check wordpress codex blog for more details.
Disclaimer: Please do not run above SQLs directly on production without testing. Make sure you write your own SQL as per your need based on database and objects present in your database.
Hope this helps !!
References:
https://codex.wordpress.org/Converting_Database_Character_Sets
http://www.bothernomore.com/2008/12/16/character-encoding-hell/
Persistent Storage Solutions 