Skip navigation links

Additional languages
9.1.12 Character Sets and Collations That MySQL Supports »
Section Navigation      [Toggle]

9.1.11. Column Character Set Conversion

To convert a binary or non-binary 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 non-binary data type (CHAR, VARCHAR, TEXT), its contents should be encoded in the column's 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 non-binary column with the desired character set.

Suppose that a table t has a binary column named col1 defined as BINARY(50). Assuming that the information in the column is encoded using a single character set, you can convert it to a non-binary 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 CHAR(50) CHARACTER SET greek;

Suppose that table t has a non-binary 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 MySQL 4.0 or earlier where a non-binary 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 BINARY(50);

The next step is to convert the column to a non-binary 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 or 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.


User Comments

Posted by shimon doodkin on February 1 2005 4:16pm[Delete] [Edit]

<?php
/* $Id: mysqlupgrade.php,v 1.3 2005/01/31 22:04:02 shimon Exp $ */
// upgrade CHARACTER SET for MySQL 4.1.0 +
// 
// Did you export all databases including mysql database before runing this file ?
//
// known bug of this program it dont know to treat FULLTEXT index
//
//by Shimon Doodkin shimon_d@hotmail.com

$conn = mysql_connect("localhost", "mashovim.co.il", "***");
$printonly=true; //change this to false to alter on the fly
$charset="hebrew";
$collate="hebrew_general_ci";
$altertablecharset=true;
$alterdatabasecharser=true;

function PMA_getDbCollation($db)
{
 $sq='SHOW CREATE DATABASE `'.$db.'`;';
 $res = mysql_query($sq);
 if(!$res) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
 if($row = mysql_fetch_assoc($res))
 {
  $tokenized = explode(' ', $row[1]);
  unset($row, $res, $sql_query);
  for ($i = 1; $i + 3 < count($tokenized); $i++)
  {
   if ($tokenized[$i] == 'DEFAULT' && $tokenized[$i + 1] == 'CHARACTER' && $tokenized[$i + 2] == 'SET')
   {
    if (isset($tokenized[$i + 5]) && $tokenized[$i + 4] == 'COLLATE')
    {
     return array($tokenized [$i + 3],$tokenized[$i + 5]); // We found the collation!
    }
    else
    {
     return array($tokenized [$i + 3]);
    }
   }
  } 
 }
 return '';
}

?>
<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=windows-1251<? //remember to change it if needed ?>" />
<xmp>
<?

$rs2 = mysql_query("SHOW DATABASES"); 
if(!$rs2) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
while ($data2 = mysql_fetch_row($rs2))
{
$db=$data2[0];
$db_cha=PMA_getDbCollation($db);
if ( $db!='mysql' /* &&( $db=='mydb_x' || $db=='mydb_y' ) */ ) // limit to database(s) - $db=='mydb_x' || $db=='mydb_y' || $db=='mydb_z'
if ( substr($db_cha[0],0,4)!='utf8' ) // limit to charset
{
 mysql_select_db($db);
$rs = mysql_query("SHOW TABLES"); 
if(!$rs) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
while ($data = mysql_fetch_row($rs))
{
 $rs1 = mysql_query("show FULL columns from $data[0]"); 
 if(!$rs1) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
 while ($data1 = mysql_fetch_assoc($rs1))
 {
  if(in_array(array_shift(split("\\(",$data1['Type'],2)),array(
//'national char',
//'nchar',
//'national varchar',
//'nvarchar',
'char',
'varchar',
'tinytext',
'text',
'mediumtext',
'longtext',
'enum',
'set'
  ))) 
  {
   if(substr($data1['Collation'],0,4)!='utf8') // limit to charset
   {
    $sq="ALTER TABLE `$data[0]` CHANGE `".$data1['Field'].'` `'.$data1['Field'].'` '.$data1['Type'].' CHARACTER SET binary '.($data1['Default']==''?'':($data1['Default']=='NULL'?' DEFAULT NULL':' DEFAULT \''.mysql_escape_string($data1['Default']).'\'')).($data1['Null']=='YES'?' NULL ':' NOT NULL').';';
    if(!$printonly&&!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n"; 
    else
    {
     echo ($sq."\n") ; 
     $sq="ALTER TABLE `$data[0]` CHANGE `".$data1['Field'].'` `'.$data1['Field'].'` '.$data1['Type']." CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate").($data1['Default']==''?'':($data1['Default']=='NULL'?' DEFAULT NULL':' DEFAULT \''.mysql_escape_string($data1['Default']).'\'')).($data1['Null']=='YES'?' NULL ':' NOT NULL').($data1['Comment']==''?'':' COMMENT \''.mysql_escape_string($data1['Comment']).'\'').';';
     if(!$printonly&&!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n"; 
     else echo ($sq."\n") ; 
    }
   }
  }
 }
 if($altertablecharset)
 {
  /*
  $sq='ALTER TABLE `'.$data[0]."` DEFAULT CHARACTER SET binary";
  echo ($sq."\n") ; 
  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
  */
  $sq='ALTER TABLE `'.$data[0]."` DEFAULT CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate");
  echo ($sq."\n") ; 
    if(!$printonly)
  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
 }
}
 if($alterdatabasecharser)
 {
  /*
  $sq='ALTER DATABASE `'.$data2[0]."` DEFAULT CHARACTER SET binary";
  echo ($sq."\n") ; 
  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
  */ 
  $sq='ALTER DATABASE `'.$data2[0]."` DEFAULT CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate");
  echo ($sq."\n") ; 
    if(!$printonly)
  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
 }
}
}
?>
</xmp>

Posted by Mariusz Gniazdowski on February 5 2005 11:27am[Delete] [Edit]

Is this MODIFY query correct? Only way that i was able to get proper sorting on utf8/utf8_polish_ci table is:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 VARCHAR(100) CHARACTER SET utf8;

like said on page:
http://dev.mysql.com/doc/mysql/en/alter-table.html

Posted by Patrick Settle on August 13 2006 3:05pm[Delete] [Edit]

I've a problem with this method, at least going from latin1_swedish_ci to utf8_general_ci, when switching back to varchar after changing the charset I receive errors on unique fields where it thinks Éleanore and Eleanore are the same (note the É ) Not sure if this is a bug (which it looks like) or if I've missed something that isn't covered with this method.

Posted by Basilio Vera on May 11 2007 12:16pm[Delete] [Edit]

About the script posted by Shimon Doodkin.

The FULLTEXT limitation can be avoided with:

ALTER TABLE `file_section` DISABLE/ENABLE KEYS between every table changes.

Add your own comment.