10.1.13 カラム文字セットの変換

特定の文字セットを使用するようにバイナリ文字列または非バイナリ文字列カラムを変換するには、ALTER TABLE を使用します。正しく変換が行われるには、次の条件のいずれかを適用する必要があります。

  • カラムにバイナリデータ型 (BINARYVARBINARYBLOB) がある場合、含まれるすべての値は、単一の文字セット (カラムの変換後の文字セット) を使用してエンコードされる必要があります。バイナリカラムを使用して複数の文字セットで情報を格納する場合、MySQL はどの値がどの文字セットを使用するかを認識できず、データを正確に変換できません。

  • カラムに非バイナリデータ型 (CHARVARCHARTEXT) がある場合、その内容は、カラムの文字セットでエンコードする必要があり、ほかの文字セットは使用できません。内容が別の文字セットでエンコードされている場合、最初にバイナリデータ型を使用するようにカラムを変換してから、使用する文字セットで非バイナリカラムに変換できます。

VARBINARY(50) として定義された col1 という名前のテーブル t にバイナリカラムがあるとします。カラム内の情報が、単一の文字セットを使用してエンコードされているとすると、このカラムを、その文字セットを含む非バイナリカラムに変換できます。たとえば、col1greek 文字セットの文字を表すバイナリデータが含まれる場合、次のように変換できます。

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

元のカラムに BINARY(50) の型がある場合、これを CHAR(50) に変換できますが、その結果得られる値は、末尾が 0x00 バイトでパディングされ、これが望ましくない場合があります。これらのバイトを削除するには、TRIM() 関数を使用します。

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

テーブル tCHAR(50) CHARACTER SET latin1 として定義された col1 という名前の非バイナリ列があるが、utf8 を使用するようにこれを変換し、多くの言語の値を格納できるようにするとします。次のステートメントでこれを実行できます。

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

両方の文字セットにない文字がカラムに含まれている場合、変換の損失が大きくなることがあります。

MySQL 4.1 より前の古いテーブルがある場合、実際にはサーバーのデフォルトの文字セットと異なる文字セットでエンコードされた値が、非バイナリカラムに含まれるという特殊な状況が起こります。たとえば、MySQL のデフォルト文字セットが latin1 であっても、アプリケーションは sjis 値をカラムに格納します。適切な文字セットを使用するために、カラムを変換することは可能ですが、追加ステップが必要になります。たとえば、サーバーのデフォルト文字セットが latin1 で、col1CHAR(50) と定義されているにもかかわらず、内容は sjis 値であるとします。最初のステップでは、バイナリデータ型にカラムを変換することで、文字変換を実行しないで既存の文字セット情報を取り除きます。

ALTER TABLE t MODIFY col1 BLOB;

次のステップでは、適切な文字セットを使用して、非バイナリデータ型にカラムを変換します。

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

この手順では、MySQL 4.1 以降にアップグレードしたあと、テーブルが INSERTUPDATE などのステートメントで変更されていない必要があります。その場合、MySQL は latin1 を使用してカラムに新しい値を格納し、そのカラムは sjis 値と latin1 値を同時に含んでおり、正確に変換できません。

最初にカラムを作成するときに属性を指定した場合、ALTER TABLE を使用してテーブルを変更しているときにも属性を指定する必要があります。たとえば、NOT NULL と明示的な DEFAULT 値を指定した場合、ALTER TABLE ステートメントでも指定する必要があります。指定しない場合、結果のカラム定義にはこれらの属性が含まれません。

テーブル内のすべての文字カラムを変換するには、ALTER TABLE ... CONVERT TO CHARACTER SET charset ステートメントが役立つことがあります。セクション13.1.7「ALTER TABLE 構文」を参照してください。


User Comments
  Posted by shimon doodkin on February 1, 2005

<?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 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
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
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
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.
  Posted by Vladislav Rastrusny on August 25, 2008
This script also attempts to change collation of MySQL system databases which is not desired in most cases.
  Posted by Vladislav Rastrusny on August 25, 2008
I guess, there is more elegant solution to convert specified databases to specified charset (including conversion of all table text fields to specified charset if that is what you want):


<?php
// Script written by Vladislav "FractalizeR" Rastrusny
// http://www.fractalizer.ru

//MySQL connection settings
$db_server 'localhost';
$db_user="root";
$db_password="";

mysql_connect($db_server$db_user$db_password) or die(mysql_error());

//Put here a list of databases you need to change charset at or leave array empty to change all existing
$dblist=array();

//If changing at all databases, which databases to skip? information_schema is mysql system databse and no need to change charset on it.
$skip_db_list = array('information_schema''mysql');

//Which charset to convert to?
$charset="utf8";

//Which collation to convert to?
$collation="utf8_general_ci";

//Only print queries without execution?
$printonly=true;

//Getting database names if they are not specified
$skip_db_text '"'.implode('", "'$skip_db_list).'"';
if(
count($dblist)<1) {
    
$sql="SELECT GROUP_CONCAT(`SCHEMA_NAME` SEPARATOR ',') AS FRST FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` NOT IN ($skip_db_text)";
    
$result mysql_query($sql) or die(mysql_error());
    
$data mysql_fetch_assoc ($result);
    
$dblist=explode(","$data["FRST"]);
}

//Iterating databases
foreach ($dblist as $dbname) {
    
$sql="SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET $charset COLLATE $collation;') as FRST FROM `information_schema`.`TABLES` t WHERE t.`TABLE_SCHEMA` = '$dbname' ORDER BY 1";

    
$result mysql_query($sql) or die(mysql_error());
    while (
$row mysql_fetch_assoc($result)) {
        echo 
$row["FRST"]."\r\n";
        if(!
$printonly) {
            
mysql_query($row["FRST"]) or die(mysql_error());
        }
    }
}
?>


You can change all databases' charset or only selected. You can select databases to skip. You can also just print queries and execute them via PHPMyAdmin for example.
  Posted by Stephen Balukoff on July 8, 2009
The manual page states that: "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."

This applies to tables which have rows with different character sets. While the above statement is probably true for the sjis and latin1 character sets, it turns out that if you have a mix of latin1 and utf-8 in a single table there's probably a "clean" way to fix this. We had to do this recently for a new customer of ours, and since the process was somewhat of a pain to come up with, I wrote about it here (in the hope that I can save someone else out there some time): http://www.blueboxgrp.com/news/2009/07/mysql_encoding
  Posted by Carlos Smanioto on March 5, 2010
Very easy in SHELL...

In my exemple, I ALTER to UTF8 CHARSET. TO RUN SCRIPT:

./altera_colation.sh NAME_DB

----- HERE ----------
#!/bin/bash
USER='admin';
PASS='admin';
BANCO=$1;

QUERY="SELECT table_name FROM information_schema.TABLES WHERE table_schema = '$BANCO';";
TABELAS=$(mysql -u $USER --password=$PASS $BANCO --execute="$QUERY" | sed 's/|//g' | tail -n +2)
var=0;
for tables in $TABELAS; do
var=`expr $var + 1`
echo "ALTER TABLE $tables ......"
mysql -u $USER --password=$PASS $BANCO -e "ALTER TABLE $tables CONVERT TO CHARSET utf8 COLLATE utf8_unicode_ci"
done

------------ END ------------
Sign Up Login You must be logged in to post a comment.