Continuing on from preparation in our MySQL latin1 to utf8 migration let us first understand where MySQL uses character sets. MySQL defines the character set at 4 different levels for the structure of data.
- Instance
- Schema
- Table
- Column
In MySQL 5.1, the default character set is latin1. If not specified, this is what you will get. For example.
mysql> create table test1(c1 varchar(10) not null);
mysql> show create table test1\G
Create Table: CREATE TABLE `test1` (
`c1` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
If you want all tables in your instance to always be a default of utf8, you can changed the server variable character_set_server. This can be set dynamically.
mysql> set global character_set_server=utf8;
mysql> set session character_set_server=utf8;
mysql> create table test2(c1 varchar(10) not null);
mysql> show create table test2\G
Create Table: CREATE TABLE `test2` (
`c1` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
If you change this dynamically be sure to include the option in your my.cnf to ensure this option is persisted for a mysqld restart.
You can define the default character set for all new tables in a given schema. You specify this when you create the schema.
mysql> set global character_set_server=latin1;
mysql> set session character_set_server=latin1;
mysql> create schema test_ucs2 default character set ucs2;
mysql> use test_ucs2;
mysql> create table test3(c1 varchar(10) not null);
mysql> show create table test3\G
Create Table: CREATE TABLE `test3` (
`c1` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ucs2
Even though we have a schema default, you can always specify the default character set for a given table which overrides any defaults.
mysql> use test_ucs2;
mysql> create table test4_utf8 (c varchar(10) not null) default charset utf8;
mysql> show create table test4_utf8\G
Create Table: CREATE TABLE `test4_utf8` (
`c` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
And finally, if you really wanted to be specific you can define the character set on a per column level.
mysql> create table test4_utf8_latin1 (c varchar(10) not null, c2 varchar(20) charset latin1) default charset utf8;
mysql> show create table test4_utf8_latin1\G
*************************** 1. row ***************************
Table: test4_utf8_latin1
Create Table: CREATE TABLE `test4_utf8_latin1` (
`c` varchar(10) NOT NULL,
`c2` varchar(20) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
With great flexibility comes great responsibility. You should have a defined standard for your application that is simple and easy to understand. I am not a proponent of using utf8 for everything, the primary reason why is memory. As part of my consulting I spend a lot of time with clients that have limited resources, e.g. database servers with 2GB or 4GB of RAM. MySQL stores utf8 efficiently on disk, but when this data is stored in memory for internal usage, it automatically uses 3 bytes, when on disk it may only be 1 byte. You can test this by creating a MEMORY table with latin1 and utf8 examples and comparing the difference in size. Is this a serious problem? Well that depends on many factors such as the number of database connections, persistent or not persistent connections, the size of the results etc. While it’s difficult in MySQL to instrument the memory precisely on a per connection basis, prudence should be a consideration for any physical resources, especially RAM.
Now that we understand what’s possible, how can we change our existing latin1 tables in our preparation example?
We could try a simple ALTER TABLE command.
mysql> alter table test_latin1 default charset utf8;
mysql> show create table test_latin1\G
Create Table: CREATE TABLE `test_latin1` (
`c` varchar(100) CHARACTER SET latin1 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
This does not work, because we are only changing the default storage engine of the table. The underlying columns remain the same. If we were to add a new column, it would default to utf8. We can however achieve what we expected with the CONVERT option.
mysql> alter table test_latin1 convert to character set utf8;
mysql> show create table test_latin1\G
Create Table: CREATE TABLE `test_latin1` (
`c` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
We look at our data, and it looks great? Are we done with our conversion?
mysql> select * from test_latin1;
+---------------+
| c |
+---------------+
| a |
| abc |
| ☺ |
| abc ☺☹☻ |
+---------------+
The answer is no. While it may look like the data is correct, MySQL also manages character sets for the communication channel. In this case, we are still communicating in latin1. To ensure moving forward in the future we must always communicate in utf8 to ensure we correctly pass utf8 to the database. We can test this with the mysql client, and as you will see our data is still corrupt.
mysql> set names utf8;
mysql> select * from test_latin1;
+------------------------+
| c |
+------------------------+
| a |
| abc |
| ☺ |
| abc ☺☹☻ |
+------------------------+
mysql> show session variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| 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 |
+--------------------------+----------------------------------------------------------------+
While you can see how we could migrate the schema definition, this does not complete our migration. In my next post, I will discuss the various different ways to correctly perform a data migration between latin1 and utf8.
