(mysql.info.gz) Charset-conversion
Info Catalog
(mysql.info.gz) Charset-map
(mysql.info.gz) Charset-upgrading
10.10.2 Converting 4.0 Character Columns to 4.1 Format
------------------------------------------------------
Normally, the server runs using the `latin1' character set by default.
If you have been storing column data that actually is in some other
character set that the 4.1 server supports directly, you can convert the
column. However, you should avoid trying to convert directly from
`latin1' to the "real" character set. This may result in data loss.
Instead, convert the column to a binary column type, and then from the
binary type to a non-binary type with the desired character set.
Conversion to and from binary involves no attempt at character value
conversion and preserves your data intact. For example, suppose that
you have a 4.0 table with three columns that are used to store values
represented in `latin1', `latin2', and `utf8':
CREATE TABLE t
(
latin1_col CHAR(50),
latin2_col CHAR(100),
utf8_col CHAR(150)
);
After upgrading to MySQL 4.1, you want to convert this table to leave
`latin1_col' alone but change the `latin2_col' and `utf8_col' columns
to have character sets of `latin2' and `utf8'. First, back up your
table, then convert the columns as follows:
ALTER TABLE t MODIFY latin2_col BINARY(100);
ALTER TABLE t MODIFY utf8_col BINARY(150);
ALTER TABLE t MODIFY latin2_col CHAR(100) CHARACTER SET latin2;
ALTER TABLE t MODIFY utf8_col CHAR(150) CHARACTER SET utf8;
The first two statements "remove" the character set information from the
`latin2_col' and `utf8_col' columns. The second two statements assign
the proper character sets to the two columns.
If you like, you can combine the to-binary conversions and from-binary
conversions into single statements:
ALTER TABLE t
MODIFY latin2_col BINARY(100),
MODIFY utf8_col BINARY(150);
ALTER TABLE t
MODIFY latin2_col CHAR(100) CHARACTER SET latin2,
MODIFY utf8_col CHAR(150) CHARACTER SET utf8;
If you specified attributes when creating a column initially, you should
also specify them when altering the table with `ALTER TABLE'. For
example, if you specified `NOT NULL' and an explicit `DEFAULT' value,
you should also provide them in the `ALTER TABLE' statement.
Otherwise, the resulting column definition will not include those
attributes.
Info Catalog
(mysql.info.gz) Charset-map
(mysql.info.gz) Charset-upgrading
automatically generated byinfo2html