Изменение кодировки MySQL-базы с latin1 на cp1251

Материал из 1GbWiki.

Содержание 1 Описание проблемы 2 Решение 3 Принцип работы 4 Текст скрипта fixmyenc

Описание проблемы

Допустим, у Вас есть база данных 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, "";