Oracle 5.0 Reference Manual Download

Page: 814 / 3232

Character Sets and Collations That MySQL Supports

794

• If the column has a binary data type (

BINARY

VARBINARY

BLOB

), all the values that it contains

must be encoded using a single character set (the character set you're converting the column to). If
you use a binary column to store information in multiple character sets, MySQL has no way to know
which values use which character set and cannot convert the data properly.

• If the column has a nonbinary data type (

CHAR

VARCHAR

TEXT

), its contents should be encoded

in the column character set, not some other character set. If the contents are encoded in a different
character set, you can convert the column to use a binary data type first, and then to a nonbinary
column with the desired character set.

Suppose that a table

has a binary column named

col1

defined as

VARBINARY(50)

. Assuming that

the information in the column is encoded using a single character set, you can convert it to a nonbinary
column that has that character set. For example, if

col1

contains binary data representing characters

in the

greek

character set, you can convert it as follows:

ALTER TABLE t MODIFY col1 VARCHAR(50) CHARACTER SET greek;

If your original column has a type of

BINARY(50)

, you could convert it to

CHAR(50)

, but the resulting

values will be padded with

0x00

bytes at the end, which may be undesirable. To remove these bytes,

use the

TRIM()

[894]

function:

UPDATE t SET col1 = TRIM(TRAILING 0x00 FROM col1);

Suppose that table

has a nonbinary column named

col1

defined as

CHAR(50) CHARACTER SET

latin1

but you want to convert it to use

utf8

so that you can store values from many languages. The

following statement accomplishes this:

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8;

Conversion may be lossy if the column contains characters that are not in both character sets.

A special case occurs if you have old tables from before MySQL 4.1 where a nonbinary column
contains values that actually are encoded in a character set different from the server's default character
set. For example, an application might have stored

sjis

values in a column, even though MySQL's

default character set was

latin1

. It is possible to convert the column to use the proper character set

but an additional step is required. Suppose that the server's default character set was

latin1

and

col1

is defined as

CHAR(50)

but its contents are

sjis

values. The first step is to convert the column

to a binary data type, which removes the existing character set information without performing any
character conversion:

ALTER TABLE t MODIFY col1 BLOB;

The next step is to convert the column to a nonbinary data type with the proper character set:

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET sjis;

This procedure requires that the table not have been modified already with statements such as

INSERT

UPDATE

after an upgrade to MySQL 4.1 or later. In that case, MySQL would store new values in the

column using

latin1

, and the column will contain a mix of

sjis

and

latin1

values and cannot be

converted properly.

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.

10.1.13. Character Sets and Collations That MySQL Supports

MySQL supports 70+ collations for 30+ character sets. This section indicates which character sets
MySQL supports. There is one subsection for each group of related character sets. For each character
set, the permissible collations are listed.

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands