mysql的charset转化指南

admin126com

Converting Character Sets

Posted by Ryan Lowe |
Vote on
Planet MySQL

The web is going the way of utf8.  Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8.  Googling for "mysql convert charset to utf8" results in a plethora of sites, each with a slightly different approach, and each broken in some respect.  I'll outline those approaches here and show why they don't work, and then present a script that can generically be used to convert a database (or set of tables) to a target character set and collation.
Approach #1:
PLAIN TEXT
SQL:
ALTER TABLE `t1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Take the following table as an example why this approach will not work:
PLAIN TEXT
SQL:
mysql> CREATE TABLE `t1` (
->   `c1` text NOT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE `t1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`c1` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row IN SET (0.01 sec)
Notice the implicit conversion of c1 from text to mediumtext. This approach can result in modified data types and silent data truncation, which makes it unacceptable for our purposes.
Approach #2 (outlined
here
):
This approach avoids the issue of implicit conversions by changing each data type to it's binary counterpart before conversion. Due to implementation limitations, however, it also converts any pre-existing binary columns to their text counterpart. Additionally, this approach will fail because a binary column cannot be part of a FULLTEXT index. Even if these limitations are overcome, this process is inherently unsuitable for large databases because it requires multiple alter statements to be run on each table:
1) Drop FULLTEXT indexes
2) Convert target columns to their binary counterparts
3) Convert the table to the target character set
4) Convert target columns to their original data types
5) Add FULLTEXT indexes back
For those of us routinely waiting hours, if not days, for a single alter statement to finish, this is unacceptable.
Approach #3:
Dumping the entire database and re-importing it with the appropriate server & client character sets.
This is a three-step process, where one must first dump only the schema and then edit it by hand to have the appropriate character sets and the dump the data separately. After which, the schema must be re-created and data imported. If you're using replication, this usually isn't even an option because you'll have a ridiculous amount of binary logs and force a reload of data on every server in the replication chain (very time/bandwidth/disk space consuming).
Except for Approach #1, these approaches are much more difficult than they need to be. Consider the following ALTER statement against the table in Approach #1:
PLAIN TEXT
SQL:
ALTER TABLE `t1`
DEFAULT CHARSET=utf8,
MODIFY COLUMN `c1` text CHARACTER SET utf8;
This approach will both change the default character set for the table and target column, while leaving in place any FULLTEXT indexes. It also requires only a single ALTER statement for a given table. A perl script has been put together to parallel-ize the ALTER statements and is available at:
PLAIN TEXT
CODE:
%> wget http://www.pablowe.net/convert_charset
It will be added to
Percona Tools
on Launchpad (or perhaps
maatkit
, if it proves useful enough) once it is feature complete. Outstanding issues include:
- Proper handling of string foreign keys (currently fails, but you probably shouldn't be using strings as foreign keys anyway ...)
- Allow throttling of the number of threads created (currently creates one per table)
PLAIN TEXT
CODE:
Usage:
convert_charset --database=database [options]

Options:
--askpass        Prompt for a MySQL password
--charset        The target character set to convert to
--collate        The target collation to convert to
--database|d     The target database
--help|?         Display this help and exit
--host|h         The target host
--ignore-columns Columns to ignore, useful if you want to
keep the existing charset for a target column
Comma-separated.  NO SPACES.
table.column
--ignore-tables  A comma-separated list of tables to ignore
--password|p     The MySQL password to use
--port           The target port
--tables         A comma-separated list of tables to convert.
All non-named tables will be ignored
--test           Print the ALTER statements that would be executed
without executing them.
--user|u         The MySQL user
--version|V      Display version information and exit

defaults are:

ATTRIBUTE                  VALUE
-------------------------- ------------------
askpass                    FALSE
charset                    utf8
collate                    No Default Value
database                   No Default Value
help                       FALSE
host                       localhost
ignore-columns             No Default Value
ignore-tables              No Default Value
password                   No Default Value
port                       3306
tables                     No Default Value
test                       FALSE
user                       Current User
version                    FALSE

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/86974/showart_2145969.html