Cambiare la Collation di tutte le Tabelle e Colonne di un Database

From RVM Wiki
Jump to navigation Jump to search

Da shell bash

<?php
global $db_url;
$database = parse_url($db_url);
$result = db_query('show tables');
while($tables = db_fetch_array($result)) {
  foreach ($tables as $key => $value) {
    db_queryd("ALTER TABLE %s COLLATE utf8_general_ci",$value);
  }
}
?>

http://lists.drupal.org/pipermail/support/2006-June/002893.html

Usare lo script (versione modificata di http://www.sveit.com/forum/viewtopic.php?t=1282) 


<html>
<head>
<title>Convert MySQL Database to UTF-8</title>
</head>
<body>

<?php
// Fill in your configuration below
$db_server      = 'localhost';
$db_user      = 'root';
$db_password   = 'thepass';
$db_name      = 'wikidb';

// Do not change anything below this
set_time_limit(0);

$connection = mysql_connect($db_server, $db_user, $db_password) or die( mysql_error() );
$db = mysql_select_db($db_name) or die( mysql_error() );

$sql = 'SHOW TABLES';
print "QUERY: $sql<br>";

if ( !($result = mysql_query($sql)) )
{
   print '<span style="color: red;">SQL Error: <br>' . mysql_error() . "</span>\n";
}

// Loop through all tables in this database
while ( $row = mysql_fetch_row($result) )
{
   $table = mysql_real_escape_string($row[0]);
   $sql2 = "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
   print "QUERY: $sql2<br>";
   
   if ( !($result2 = mysql_query($sql2)) )
   {
      print '<span style="color: red;">SQL Error: <br>' . mysql_error() . "</span>\n";
      
      break;
   }
   
   print "$table changed to UTF-8 successfully.<br>\n";

   // Now loop through all the fields within this table
   $sql3 = "SHOW COLUMNS FROM $table";
   print "QUERY: $sql3<br>";

   if ( !($result3 = mysql_query($sql3)) )
   {
      print '<span style="color: red;">SQL Error: <br>' . mysql_error() . "</span>\n";
      
      break;
   }

   while ( $row3 = mysql_fetch_row($result3) )
   {
      $field_name = $row3[0];
      $field_type = $row3[1];
      
      // Change text based fields
      $skipped_field_types = array('char', 'text', 'enum', 'set' );
      
      foreach ( $skipped_field_types as $type )
      {
         if ( strpos($field_type, $type) !== false )
         {
            $sql4 = "ALTER TABLE $table CHANGE `$field_name` `$field_name` $field_type CHARACTER SET utf8 COLLATE utf8_bin";
            print "QUERY: $sql4<br>";
            if ( !($result4 = mysql_query($sql4)) )
            {
               print '<span style="color: red;">SQL Error: <br>' . mysql_error() . "</span>\n";
               
               break 3;
            }
            print "---- FIELD $field_name changed to UTF-8 successfully.<br>\n";
         }
      }
   }
   print "<hr>\n";
}

mysql_close($connection);
?>

</body>
</html>
Retrieved from "https://kb.rvmgroup.it/index.php?title=Cambiare_la_Collation_di_tutte_le_Tabelle_e_Colonne_di_un_Database&oldid=5278"