Обновление сервера MySQL версии 3.23 и 4.0 на версии 4.1 или 5.0, решение проблемы с кодировкой символов в MySQL

Статья рассказывает о сложностях, которые могут возникнуть с кодировками при переходе с серверов MySQL версий 3.23 и 4.0 на серверы версий 4.1 и 5.х, и о методах их преодоления.

Проблема кодировок при обновлении сервера

Серверы MySQL до версии 4.1 не имели встроенной поддержки кодировок строк. Это не означает, что в них невозможно было сохранять русскоязычные (или вообще произвольные) данные. Это лишь означает, что сервер сам не имел никакого представления о том, какие данные содержатся в строковом столбце.

Начиная с версии 4.1, сервер не только понимает, какие данные содержатся в строковом столбце, но и умеет корректно их обрабатывать. Например, он может хранить строки в кодировке KOI8-R, а выдавать их клиенту в кодировке CP1251, при этом производя преобразование строк автоматически.

Нововведение очень удобно для русскоязычных пользователей (ведь мы пользуемся активно по крайней мере тремя кодировками — KOI8-R, CP1251, UTF-8 — но есть еще и те, кто предпочитает работать в CP866 или MacCyrillic), однако оно создает определенную сложность, когда Вы пытаетесь обновить сервер с версии, не поддерживающей кодировки, на сервер, поддерживающий кодировки.

Суть проблемы состоит в следующем: непосредственно после обновления сервера, Вы обнаружите, что все Ваши строки находятся в кодировке latin1 (кодировке сервера по-умолчанию). Принципиальный минус этой кодировки состоит в том, что она семибитная. Это означает, что все символы с кодом более 127 (а, значит, все русские буквы) признаются недействительными с точки зрения этой кодировки.

Проблема «на пальцах»

Для того, чтобы разобраться, что же все-таки происходит со строками, давайте попробуем перенести маленькую тестовую таблицу с сервера 3.23 на сервер 5.0. Итак, изначальная таблица на сервере 3.23:

3.23 > SELECT * FROM mytable;
+----+------------+
| id | data |
+----+------------+
| 1 | Данные раз |
| 2 | Данные два |
| 3 | Данные три |
+----+------------+
3 rows in set (0.00 sec)

После обновления сервера, мы выполняем тот же самый запрос — и получаем те же самые данные. Так о чем же мы тогда здесь говорим? Проблема появится тогда, когда мы попытаемся эти данные каким-либо образом преобразовать. Действительно, если мы посмотрим на код таблицы

5.0 > SHOW CREATE TABLE mytable\G
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

то мы обнаружим, что строка хранится в кодировке latin1. Если запросить те же строки не из latin1-клиента, а, например, из клиента KOI8-R, то сервер не сможет правильно выполнить наш запрос:

5.0 > SET NAMES koi8r;
Query OK, 0 rows affected (0.00 sec)

5.0 > SELECT * FROM mytable;
+----+------------+
| id | data |
+----+------------+
| 1 | ?????? ??? |
| 2 | ?????? ??? |
| 3 | ?????? ??? |
+----+------------+
3 rows in set (0.00 sec)

Дело в том, что когда сервер пытается преобразовать строку из latin1 в KOI8-R, он обнаруживает, что хранимые символы не принадлежат набору latin1 и выводит вместо них вопросительные знаки.

Решение проблемы: метод резервных копий

Проблема решается достаточно просто, если у Вас есть резервная копия нужных данных. MySQL AB рекомендует всегда делать резервную копию перед обновлением сервера (хотя многие игнорируют этот шаг). Итак, у нас есть резервная копия, сделанная с помощью mysqldump сервера 3.23 (строки комментариев убраны):

shell> mysqldump mydb > mydb.sql
shell> cat mydb.sql
CREATE TABLE mytable (
id int(11) NOT NULL auto_increment,
data char(10) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;

INSERT INTO mytable VALUES (1,'Данные раз');
INSERT INTO mytable VALUES (2,'Данные два');
INSERT INTO mytable VALUES (3,'Данные три');

Как видно, все данные в этой резервной копии записаны в правильной кодировке (кодировке терминала, в которой создавалась копия, т.е. в данном случае — KOI8-R). При импорте данных требуется сообщить серверу, что мы собираемся импортировать данные именно в этой кодировке, а не в latin1. Для этого Вы можете либо выполнить необходимые команды вручную, либо добавить их в начало файла резервной копии с помощью Вашего любимого редактора.

5.0 > SET NAMES koi8r;
Query OK, 0 rows affected (0.00 sec)

5.0 > SET character_set_server=koi8r;
Query OK, 0 rows affected (0.00 sec)

Первая команда сообщает серверу, что данные будут приходить в кодировке KOI8-R. Вторая команда заставит сервер при создании таблиц использовать кодировку KOI8-R по-умолчанию для всех строковых типов данных.

Необходимы обе эти команды, так как если Вы выполните только первую, то данные будут прочитаны, преобразованы в latin1 перед записью (ведь столбец будет иметь кодировку по-умолчанию), то есть все русские буквы будут заменены вопросительными знаками, и уже вопросительные знаки будут записаны в таблицу. Если же Вы выполните только вторую команду, то данные будут прочитаны в кодировке по-умолчанию, русские буквы опять не попадут в нужный диапазон, и Вы опять получите вопросительные знаки в таблице.

После выполнения подготовительных действий, Вы можете смело загрузить данные из резервной копии:

5.0 > SOURCE mydb.sql
Query OK, 0 rows affected, 1 warning (0.02 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

Все данные загружаются правильно, предупреждение при создании таблицы возникает из-за того, что в резервной копии написано ключевое слово TYPE вместо ENGINE.

Решение проблемы: метод сервера

Что делать, если у Вас нет резервной копии, или Вы уже обновили сервер и у Вас нет возможности ее создать? Что ж, безвыходных положений нет, но Вам прийдется менять строковые столбцы всех таблиц (а их может оказаться много). Впрочем, Вы всегда можете автоматизировать процесс.

Итак, в нашем случае у нас есть только один столбец со строкой, поэтому здесь все будет чрезвычайно просто. Исходить будем из того, что у нас есть таблица с данными в кодировке latin1, но данные содержат реально 8-битные строки в кодировке KOI8-R (пример таблицы можно посмотреть в разделе «Проблема на пальцах»).

Очевидно, что если просто заставить сервер записать строки в нужной кодировке, он применит преобразование кодировок и в базе останутся лишь вопросительные знаки. Решение проблемы состоит в том, чтобы преобразовать строки в промежуточный тип данных, который не подвержен преобразованию кодировок. В MySQL для каждого строкового типа данных существует аналогичный бинарный тип данных (например, для CHAR — BINARY, для VARCHAR — VARBINARY, для TEXT — BLOB). Если строку преобразовать к новому типу, а потом к старому в другой кодировке, то преобразования между кодировками не произойдет (ведь в промежуточном типе кодировки нету вообще):

5.0 > ALTER TABLE mytable MODIFY data BINARY(10);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0

5.0 > ALTER TABLE mytable MODIFY data CHAR(10) CHARSET koi8r;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

Новые данные уже будут находиться в правильной кодировке KOI8-R, и сервер сможет правильно преобразовывать их при работе с любым клиентом.

Решение проблемы: правка резервной копии

Предыдущий способ хорош всем за исключением, пожалуй, необходимости вводить большое количество команд. Если у Вас есть всего 2-3 таблицы с 1-2 столбцами строк, то проблем, конечно, нет. Если же у Вас есть несколько десятков (а иногда и сотен) строковых столбцов, то хочется каким-то образом автоматизировать процесс изменения данных. Более того, предположим, что Вы не сделали резервную копию в старой версии MySQL.

Итак, начнем с того, что создадим резервную копию базы уже на новом сервере:

shell> mysqldump mydb > mydb.sql

Открыв вновь созданный файл в текстовом редакторе, Вы сможете обнаружить некоторые интересные особенности:

Преодолевать указанные особенности, разумеется, следует последовательно. Итак, первая проблема — строки записаны в UTF-8. Как Вы понимаете, изначально строки были записаны в latin1, поэтому, чтобы правильно с ними работать, необходимо преобразовать их назад в исходный вид:

shell> recode utf8..latin1 mydb.sql

Открыв преобразованный файл в текстовом редакторе, Вы обнаружите, что строки в нем сохранены в «правильной» кодировке. В данном конкретном случае — KOI8-R. Почему так произошло? Все очень просто. Изначально (в старой версии сервера) строки сохранялись именно в этой кодировке. При переходе на новую версию, сервер назначил этим строкам неверную кодировку (latin1). При создании резервной копии, сервер преобразовал latin1 в UTF-8. Сделав обратное преобразование, Вы получили строки в исходной кодировке.

Обратите внимание, что метод неприменим, если у Вас есть таблицы с бинарными полями (например, в таблице хранятся рисунки или файлы). В таком случае команда recode некорректно преобразует содержимое файлов. Вам следует перенести таблицы, содержащие файлы отдельно, не выполняя команду recode.

Вторая проблема: в описаниях структуры таблиц написана кодировка latin1. Здесь Вы можете или непосредственно в текстовом редакторе заменить все слова latin1 на нужную Вам кодировку или использовать какую-нибудь другую программу для этих целей:

shell> sed s/latin1/koi8r/ mydb.sql > mydb1.sql

Обратите внимание, что этот код заменит все строки latin1 на koi8r. Если Вам нужно где-то использовать строки latin1, Вам прийдется придумать другой способ.

Наконец, осталось решить третью маленькую проблему: при чтении файла резервной копии, сервер ожидает получать данные в UTF-8, а не в KOI8-R. Для того, чтобы сообщить ему, что данные находятся в KOI8-R, нужно изменить всего лишь одну команду в начале файла (SET NAMES utf8). Как всегда, Вы можете воспользоваться текстовым редактором или сделать это любым другим способом:

shell> sed "s/NAMES utf8/NAMES koi8r/" mydb1.sql > mydb2.sql

Получившийся в конце всех действий файл можно загрузить в сервер MySQL, пересоздав (при необходимости) все затрагиваемые в файле базы:

5.0> DROP DATABASE mydb;
Query OK, 1 row affected (0.49 sec)

5.0> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)

5.0> USE mydb;
Database changed
5.0> SOURCE mydb2.sql;

Вновь загруженные данные будут иметь нужную кодировку и будут правильно преобразовываться сервером в дальнейшем.

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Обновление сервера 3.23 и 4.0

Проблема кодировок при обновлении сервера

Проблема «на пальцах»

Решение проблемы: метод резервных копий

Решение проблемы: метод сервера

Решение проблемы: правка резервной копии