API@RTK0.NET

update_convert_table_utf8

• Drupal 4.7
• Drupal 5

1. drupal
   1. 4.7
   2. 5

Convert a single MySQL table to UTF-8.

We change all text columns to their corresponding binary type, then back to text, but with a UTF-8 character set. See: http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html

Code

./update.php, line 616

<?php
function update_convert_table_utf8($table) {
  $ret = array();
  $types = array(
    'char' => 'binary', 
    'varchar' => 'varbinary', 
    'tinytext' => 'tinyblob', 
    'text' => 'blob', 
    'mediumtext' => 'mediumblob', 
    'longtext' => 'longblob',
  );

  // Get next table in list
  $convert_to_binary = array();
  $convert_to_utf8 = array();

  // Set table default charset
  $ret[] = update_sql('ALTER TABLE {' . $table . '} DEFAULT CHARACTER SET utf8');

  // Find out which columns need converting and build SQL statements
  $result = db_query('SHOW FULL COLUMNS FROM {' . $table . '}');
  while ($column = db_fetch_array($result)) {
    list($type) = explode('(', $column['Type']);
    if (isset($types[$type])) {
      $names = 'CHANGE `' . $column['Field'] . '` `' . $column['Field'] . '` ';
      $attributes = ' DEFAULT ' . ($column['Default'] == 'NULL' ? 'NULL ' :
                     "'" . db_escape_string($column['Default']) . "' ") .
                    ($column['Null'] == 'YES' ? 'NULL' : 'NOT NULL');

      $convert_to_binary[] = $names . preg_replace('/' . $type . '/i', $types[$type], $column['Type']) . $attributes;
      $convert_to_utf8[] = $names . $column['Type'] . ' CHARACTER SET utf8' . $attributes;
    }
  }

  if (count($convert_to_binary)) {
    // Convert text columns to binary
    $ret[] = update_sql('ALTER TABLE {' . $table . '} ' . implode(', ', $convert_to_binary));
    // Convert binary columns to UTF-8
    $ret[] = update_sql('ALTER TABLE {' . $table . '} ' . implode(', ', $convert_to_utf8));
  }
  return $ret;
}
?>