СтатьиОбновление сервера MySQL 3.23 и 4.0Автор: Павел Пушкарев Статья рассказывает о сложностях, которые могут возникнуть с кодировками при переходе с серверов 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 Все данные загружаются правильно, предупреждение при создании таблицы возникает из-за того, что в резервной копии написано ключевое слово 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. Сделав обратное преобразование, Вы получили строки в исходной кодировке. Вторая проблема: в описаниях структуры таблиц написана кодировка 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; Вновь загруженные данные будут иметь нужную кодировку и будут правильно преобразовываться сервером в дальнейшем. |
||