Rechercher dans le manuel MySQL
10.7 Column Character Set Conversion
To convert a binary or nonbinary string column to use a particular
character set, use ALTER TABLE
. For
successful conversion to occur, one of the following conditions
must apply:
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 t
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:
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()
function:
Suppose that table t
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:
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 different. 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:
The next step is to convert the column to a nonbinary data type with the proper character set:
This procedure requires that the table not have been modified
already with statements such as
INSERT
or
UPDATE
after an upgrade to MySQL
4.1 or higher. 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.
To convert all character columns in a table, the ALTER
TABLE ... CONVERT TO CHARACTER SET
statement may be
useful. See Section 13.1.9, “ALTER TABLE Syntax”.
charset
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-charset-conversion.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.