Announcement

Collapse
No announcement yet.

Convert DB charset to utf8mb4

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert DB charset to utf8mb4

    Hi there.
    I have a big forum with more than 2,000,000 posts, I start this forum from the era of vb1.1.3 then upgrade it to all generation tell we reach vb2.2.3.

    As you know the default charset was latin1, and my forum primary language is Arabic.

    I read here and there for years about the importance of changing DB charest to utf8, and now a days I read about utf8mb4, so what are the advantages and disadvantages of this conversion? And what are the steps that I must take care of to migrate my DB to utf8mb4?

    Note: I'm using vBSuite (CMS & Blogs).

    Thanks in advance.
    One of highly customized vBCMS used for news site.
    www.manshor.net

  • #2
    Any tips?
    One of highly customized vBCMS used for news site.
    www.manshor.net

    Comment


    • #3
      Well you can convert your Database by a PHP-Script...

      This is a script i used some years ago to convert from latin to utf8. You can modify to mysqli.

      Use at your own risk!! You can loose all your data, so run some tests on a copy or test site!

      PHP Code:
      <?php
      echo "Start\n";
      $DB_HOST 'localhost'// Database Host
      $DB_USER 'Username'// Database Username
      $DB_PASSWORD 'password'// Database Password
      $DB_DATABASE 'database'// Database Name

      $tables = array();
      $tables_with_fields = array();

      $link_id mysql_connect($DB_HOST$DB_USER$DB_PASSWORD) or die('Error establishing a database connection');
      echo 
      "Connected\n";
      mysql_select_db($DB_DATABASE$link_id);
      echo 
      'Selected database' ."\n";
      echo 
      'Getting tables:' ."\n";
      //$resource = mysql_query("SHOW TABLES like 'LB_NEU'", $link_id);
      $resource mysql_query("SHOW TABLES"$link_id);
      while ( 
      $result mysql_fetch_row($resource) ) {
      $tables[] = $result[0];
      echo 
      ' - ' $result[0] ."\n";
      }
      if ( !empty(
      $tables) ) {
      echo 
      'Starting process' ."\n";
      foreach ( (array) 
      $tables as $table ) {
      echo 
      'Working on table "' $table '"';
      $resource mysql_query("EXPLAIN $table"$link_id);
      while ( 
      $result mysql_fetch_assoc($resource) ) {
      if ( 
      preg_match('/(char)|(text)|(enum)|(set)/'$result['Type']) )
      $tables_with_fields[$table][$result['Field']] = $result['Type'] . " CHARACTER SET utf8 " . ( "YES" == $result['Null'] ? "" "NOT " ) . "NULL " . ( !is_null($result['Default']) ? "DEFAULT '"$result['Default'] ."'" "" );
      echo 
      '.';
      }
      echo 
      "\n";
      }

      // Change all text/string fields of the tables to their corresponding binary text/string representations.
      echo 'Altering tables to binary character set';
      foreach ( (array) 
      $tables as $table ) {
      // das hat nix gebracht - also dont do it..
      // mysql_query("ALTER TABLE $table CONVERT TO CHARACTER SET binary", $link_id);
      // echo '.';
      }
      echo 
      "\n";

      // Change database and tables to UTF-8 Character set.
      echo 'Altering tables to utf8 character set';
      mysql_query("ALTER DATABASE " $DB_DATABASE " CHARACTER SET utf8"$link_id);
      foreach ( (array) 
      $tables as $table ) {
      $SQL="ALTER TABLE $table CONVERT TO CHARACTER SET utf8";
      echo 
      $SQL."\n";
      mysql_query($SQL$link_id);
      echo 
      '.';
      }
      echo 
      "\n";
      // Return all binary text/string fields previously changed to their original representations.
      echo 'Altering binary text/string fields to original representation';
      foreach ( (array) 
      $tables_with_fields as $table => $fields ) {
      foreach ( (array) 
      $fields as $field_type => $field_options ) {
      echo 
      "$field_options\n\n";
      $SQL="ALTER TABLE $table MODIFY $field_type $field_options";

      echo 
      "$SQL\n";
      mysql_query($SQL$link_id);
      }
      echo 
      '.';
      }
      echo 
      "\n";

      // Optimize tables and finally close the mysql link.
      echo 'Optimizing tables' "\n";
      foreach ( (array) 
      $tables as $table )
      mysql_query("OPTIMIZE TABLE $table"$link_id);
      mysql_close($link_id);
      echo 
      'DONE';
      } else {
      die(
      'There are no tables?');
      }
      ?>

      Comment

      Related Topics

      Collapse

      Working...
      X