Изменение кодировки MySQL-базы с latin1 на cp1251
Материал из 1GbWiki.
Версия 17:16, 15 августа 2008 (править) Teak (Обсуждение | вклад) (→Принцип работы) ← К предыдущему изменению |
Текущая версия (14:48, 24 сентября 2009) (править) (отменить) Teak (Обсуждение | вклад) (→Текст скрипта fixmyenc - collate в спецификации create database необязательно) |
||
(5 промежуточных версий не показаны.) | |||
Строка 1: | Строка 1: | ||
== Описание проблемы == | == Описание проблемы == | ||
- | Допустим, у Вас есть база данных MySQL с кодировкой latin1. В ней не работает сортировка, русский текст не видно в phpMyAdmin и так далее. Вы хотите перевести её в правильную кодировку | + | Допустим, у Вас есть база данных MySQL с кодировкой latin1. В ней не работает сортировка, русский текст не видно в phpMyAdmin и так далее. Вы хотите перевести её в правильную кодировку — cp1251, в которой фактически и находятся данные в базе. Однако простого способа это сделать нет. |
== Решение == | == Решение == | ||
Строка 9: | Строка 9: | ||
* С помощью скрипта fixmyenc, текст которого приводится ниже, выполняем следующее: | * С помощью скрипта fixmyenc, текст которого приводится ниже, выполняем следующее: | ||
<pre>perl fixmyenc HOST DATABASE USER PASSWORD >fixdb.sql</pre> | <pre>perl fixmyenc HOST DATABASE USER PASSWORD >fixdb.sql</pre> | ||
- | Если не происходит никаких ошибок, создаётся файл fixdb.sql | + | Если не происходит никаких ошибок, создаётся файл fixdb.sql — скрипт на языке sql, которым мы и будем чинить базу. |
* <i>(необязательно)</i> Просматриваем созданный файл. | * <i>(необязательно)</i> Просматриваем созданный файл. | ||
Строка 16: | Строка 16: | ||
<pre>mysql --host=HOST --database=DATABASE --user=USER --password=PASSWORD <fixdb.sql</pre> | <pre>mysql --host=HOST --database=DATABASE --user=USER --password=PASSWORD <fixdb.sql</pre> | ||
- | * Если и на этом этапе нет никаких ошибок | + | * Если и на этом этапе нет никаких ошибок — значит наша база успешно перекодирована. Если ошибки были — восстанавливаем базу из бэкапа, созданного перед началом работы, и сообщаем об ошибке (с её полным текстом) например на страницу обсуждения. |
<b>Важно:</b> Скрипт fixmyenc написан так, что при малейшей проблеме останавливает свою работу с ошибкой и не создаёт sql-скрипта. Это сделано специально (и не составляет проблемы, поскольку в самой базе при этом ничего не меняется), однако о каждой такой проблеме тоже очень желательно сообщить. После сообщения об ошибке скрипт будет исправлен и новый текст выложен здесь же. | <b>Важно:</b> Скрипт fixmyenc написан так, что при малейшей проблеме останавливает свою работу с ошибкой и не создаёт sql-скрипта. Это сделано специально (и не составляет проблемы, поскольку в самой базе при этом ничего не меняется), однако о каждой такой проблеме тоже очень желательно сообщить. После сообщения об ошибке скрипт будет исправлен и новый текст выложен здесь же. | ||
Строка 22: | Строка 22: | ||
== Принцип работы == | == Принцип работы == | ||
- | * Все | + | * Все полнотекстовые индексы (FULLTEXT KEY) удаляются и в конце пересоздаются. |
* CHARACTER SET и COLLATION самой базы, всех таблиц и всех текстовых полей в таблицах меняются сначала с latin1 на binary, а затем с binary на cp1251_general_ci (если бы мы попытались изменить кодировку напрямую из latin1 в cp1251, то MySQL не нашёл бы символов, соответствующих верхней части latin1, в cp1251, и просто заменил бы их знаками вопроса, в результате чего все русские буквы были бы безвозвратно потеряны). | * CHARACTER SET и COLLATION самой базы, всех таблиц и всех текстовых полей в таблицах меняются сначала с latin1 на binary, а затем с binary на cp1251_general_ci (если бы мы попытались изменить кодировку напрямую из latin1 в cp1251, то MySQL не нашёл бы символов, соответствующих верхней части latin1, в cp1251, и просто заменил бы их знаками вопроса, в результате чего все русские буквы были бы безвозвратно потеряны). | ||
Строка 38: | Строка 38: | ||
die "bad host/db/user" unless $host =~ /^[\w\-\.]+$/ and $database =~ /^[\w_]+$/ and $user =~ /^[\w_]+$/; | 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 $dbh = DBI->connect("DBI:mysql:database=$database;host=$host", $user, $password, {'RaiseError' => 1}); | ||
- | sub showquery($) { | ||
- | my $q = shift; | ||
- | print "$q\n"; | ||
- | #$dbh->do($q); | ||
- | } | ||
my (@pass0, @pass1, @pass2, @pass3) = (); | my (@pass0, @pass1, @pass2, @pass3) = (); | ||
my $sth = $dbh->prepare("SHOW CREATE DATABASE $database"); | my $sth = $dbh->prepare("SHOW CREATE DATABASE $database"); | ||
$sth->execute; | $sth->execute; | ||
my @arr = $sth->fetchrow_array; | my @arr = $sth->fetchrow_array; | ||
- | $arr[1] =~ /^CREATE DATABASE `$database` \/\*!\d+ DEFAULT CHARACTER SET latin1 COLLATE latin1_\w+ \*\/$/ | + | $arr[1] =~ /^CREATE DATABASE `$database` \/\*!\d+ DEFAULT CHARACTER SET latin1( COLLATE latin1_\w+)? \*\/$/ |
or die "bad database spec: $arr[1]"; | or die "bad database spec: $arr[1]"; | ||
push @pass1, "ALTER DATABASE `$database` DEFAULT CHARACTER SET binary"; | push @pass1, "ALTER DATABASE `$database` DEFAULT CHARACTER SET binary"; | ||
Строка 103: | Строка 98: | ||
print join ";\n", "USE `$database`", @pass0, @pass1, @pass2, @pass3, "";</pre> | print join ";\n", "USE `$database`", @pass0, @pass1, @pass2, @pass3, "";</pre> | ||
+ | |||
+ | [[Категория:MySQL]] |
Текущая версия
Содержание |
[править] Описание проблемы
Допустим, у Вас есть база данных 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, "";