Описание проблемы
Допустим, у Вас есть база данных MySQL с кодировкой latin1. В ней не работает сортировка, русский текст не видно в phpMyAdmin и так далее. Вы хотите перевести её в правильную кодировку — cp1251, в которой фактически и находятся данные в базе. Однако простого способа это сделать нет.
[править] Решение
* Делаем полный бэкап базы.
* С помощью скрипта fixmyenc, текст которого приводится ниже, выполняем следующее:
perl fixmyenc HOST DATABASE USER PASSWORD >fixdb.sql
Если не происходит никаких ошибок, создаётся файл fixdb.sql — скрипт на языке sql, которым мы и будем чинить базу.
* (необязательно) Просматриваем созданный файл.
* Выполняем содержащиеся в нём SQL-запросы:
mysql --host=HOST --database=DATABASE --user=USER --password=PASSWORD <fixdb.sql
* Если и на этом этапе нет никаких ошибок — значит наша база успешно перекодирована. Если ошибки были — восстанавливаем базу из бэкапа, созданного перед началом работы, и сообщаем об ошибке (с её полным текстом) например на страницу обсуждения.
Важно: Скрипт fixmyenc написан так, что при малейшей проблеме останавливает свою работу с ошибкой и не создаёт sql-скрипта. Это сделано специально (и не составляет проблемы, поскольку в самой базе при этом ничего не меняется), однако о каждой такой проблеме тоже очень желательно сообщить. После сообщения об ошибке скрипт будет исправлен и новый текст выложен здесь же.
[править] Принцип работы
* Все полнотекстовые индексы (FULLTEXT KEY) удаляются и в конце пересоздаются.
* CHARACTER SET и COLLATION самой базы, всех таблиц и всех текстовых полей в таблицах меняются сначала с latin1 на binary, а затем с binary на cp1251_general_ci (если бы мы попытались изменить кодировку напрямую из latin1 в cp1251, то MySQL не нашёл бы символов, соответствующих верхней части latin1, в cp1251, и просто заменил бы их знаками вопроса, в результате чего все русские буквы были бы безвозвратно потеряны).
Текст скрипта fixmyenc
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
unless( 4 == @ARGV ) {
print STDERR "Usage: $0 <host> <database> <user> <password>\n";
exit 1;
}
my ($host, $database, $user, $password) = @ARGV;
die "bad host/db/user" unless $host =~ /^[\w\-\.]+$/ and $database =~ /^[\w_]+$/ and $user =~ /^[\w_]+$/;
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host", $user, $password, {'RaiseError' => 1});
my (@pass0, @pass1, @pass2, @pass3) = ();
my $sth = $dbh->prepare("SHOW CREATE DATABASE $database");
$sth->execute;
my @arr = $sth->fetchrow_array;
$arr[1] =~ /^CREATE DATABASE `$database` \/\*!\d+ DEFAULT CHARACTER SET latin1( COLLATE latin1_\w+)? \*\/$/
or die "bad database spec: $arr[1]";
push @pass1, "ALTER DATABASE `$database` DEFAULT CHARACTER SET binary";
push @pass2, "ALTER DATABASE `$database` DEFAULT CHARACTER SET cp1251 COLLATE cp1251_general_ci";
$sth = $dbh->prepare("SHOW TABLES");
$sth->execute;
my @tables = ();
while( my @row = $sth->fetchrow_array ) {
die "bad table name: $row[0]" unless $row[0] =~ /^[\w_]+$/;
push @tables, $row[0];
}
foreach my $table (@tables) {
$sth = $dbh->prepare("SHOW CREATE TABLE `$table`");
$sth->execute;
my @row = $sth->fetchrow_array;
$row[1] =~ s/ COMMENT='[^'\\]*'$//s;
$row[1] =~ /^CREATE TABLE `$table` \(.*\)([^)]+)$/s
or die "bad table spec: $row[1]";
my $tail = $1;
unless( $tail =~ /\bDEFAULT CHARSET=latin1\b/ ) {
die "bad table $table spec tail: $tail";
}
push @pass1, "ALTER TABLE `$table` DEFAULT CHARSET binary";
push @pass2, "ALTER TABLE `$table` DEFAULT CHARSET cp1251 COLLATE cp1251_general_ci";
my (%fulltext, %column_in_fulltext_indexes, %remove_fulltext_indexes) = ();
$sth = $dbh->prepare("SHOW INDEX FROM `$table`");
$sth->execute;
while( my $row = $sth->fetchrow_hashref ) {
next unless $row->{Index_type} eq "FULLTEXT";
$fulltext{$row->{Key_name}} ||= [];
$fulltext{$row->{Key_name}}[$row->{Seq_in_index}-1] = $row->{Column_name};
$column_in_fulltext_indexes{$row->{Column_name}} ||= [];
push @{$column_in_fulltext_indexes{$row->{Column_name}}}, $row->{Key_name};
}
$sth = $dbh->prepare("SHOW FULL COLUMNS FROM `$table`");
$sth->execute;
while( my @row = $sth->fetchrow_array ) {
my ($column, $type, $collation) = @row[0,1,2];
if( defined $collation and "NULL" ne $collation ) {
die "bad collation in column $column of table $table: $collation" unless $collation =~ /^latin1_\w+$/;
push @pass1, "ALTER TABLE `$table` MODIFY COLUMN `$column` $type CHARACTER SET binary";
push @pass2, "ALTER TABLE `$table` MODIFY COLUMN `$column` $type CHARACTER SET cp1251 COLLATE cp1251_general_ci";
foreach my $index (@{$column_in_fulltext_indexes{$column}}) {
$remove_fulltext_indexes{$index} = 1;
}
}
}
foreach my $index (keys %remove_fulltext_indexes) {
push @pass0, "ALTER TABLE `$table` DROP INDEX `$index`";
push @pass3, "ALTER TABLE `$table` ADD FULLTEXT INDEX `$index` (" . join( ",", map { "`$_`" } @{$fulltext{$index}} ) . ")";
}
}
print join ";\n", "USE `$database`", @pass0, @pass1, @pass2, @pass3, "";
