To put it simply, there are two ways you can alter the table to use a new character set.
1. ALTER TABLE tablename DEFAULT CHARACTER SET utf8;
This will alter the table to use the new character set as the default, but as a safety mechanism, it will only change the table definition for the default character set. That is, existing character fields will have the old character set per column. For example:
mysql> create table mybig5 (id int not null auto_increment primary key,
-> subject varchar(100) ) engine=innodb default charset big5;
Query OK, 0 rows affected (0.81 sec)
mysql> show create table mybig5; +--------+--------------------------------+ | Table | Create Table | +--------+--------------------------------+ | mybig5 | CREATE TABLE `mybig5` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=big5 | +--------+--------------------------------+ 1 row in set (0.00 sec)
mysql> alter table mybig5 default charset utf8; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0
Inserting a multi-byte string that worked in big5 character set, such as:
mysql> INSERT INTO mybig5 VALUES (NULL, UNHEX('E7BB8FE79086'));
01:08:19 [INSERT - 0 row(s), 0.000 secs] [Error Code: 1366, SQL State: HY000] Incorrect string value: '\xE7\xBB\x8F\xE7\x90\x86' for column 'SUBJECT' at row 1 ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
mysql> show create table mybig5; +--------+------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------+ | mybig5 | CREATE TABLE `mybig5` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(100) CHARACTER SET big5 DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+------------------------------------------------+ 1 row in set (0.00 sec)
Notice that the ‘subject’ column has the original character set definition and when data is inserted, can result in the error above if the character sets do not match.
2. ALTER TABLE tablename CONVERT TO CHARACTER SET utf8;
This will change all the columns to the new character set and change the table as well. So you will end up with the required definition:
mysql> show create table mybig5; +--------+--------------------------------+ | Table | Create Table | +--------+--------------------------------+ | mybig5 | CREATE TABLE `mybig5` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+--------------------------------+ 1 row in set (0.00 sec)
So if you see the incorrect string error on a table, check that the columns are not under a different character set to the default. Look at using the CONVERT clause to avoid the issue, but also be aware that certain tables may actually require different character sets for different columns.