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

Дата публикации: 12/12/2008

Это будет достаточно специфическая заметка, но кому-нибудь она, возможно, пригодится. С этой проблемой я разбирался буквально вчера. Все началось с того, что на одном сайте в результате каких-то действий на сервере, что-то сбилось и часть текста превратилась в вопросики. После изучения проблемы выяснилось, что сбились кодировки в некоторых таблицах базы данных MySQL.

Вот так выглядит страничка сайта со сбитой кодировкой в базе данных

Как известно, внутри себя MySQL хранит данные в кодировке UTF-8, а перекодирование в нужную клиенту кодировку происходит непосредственно при чтении данных. В какую именно кодировку следует перекодировать результат запроса определяется настройками таблицы при ее создании. А если кодировка в таблице явно не указывается, то берутся значения по умолчанию, установленные для всей базы данных.

В случае, если кодировка, указанная для таблицы, не будет совпадать с кодировкой собственно данных, то в результатах мы получим далеко не то, что ожидаем. Внешне это может выглядеть и как мешанина из букв и как сплошные вопросики. Если затем зайти в базу данных посредством, например, "phpMyAdmin" и посмотреть структуру базы, то можно увидеть в некоторых текстовых полях в колонке "Сравнение" неправильную кодировку. Но не торопитесь менять кодировку таблицы, редактируя параметры поля, т.к. вы можете все испортить безвозвратно перекодировав все данные и тогда их уже будет не восстановить.

Далее я буду по шагам объяснять как правильно восстановить кодировку такой базы данных. Я буду исходить из того, что в моем случае все данные в базе хранятся и обрабатываются в кодировке "Win-1251". Показывать буду на примере таблицы, хранящей новости сайта со структурой представленной на картинке.

Первым делом, чтобы убедиться, что проблема именно в том, что MySQL выдает данные в неверной кодировке, сделайте следующий запрос к базе данных:

 SELECT 
    CONVERT(CONVERT(title USING binary) USING cp1251),
    CONVERT(CONVERT(text USING binary) USING cp1251)
 FROM 
    news

Данный запрос делает явную перекодировку при получении результатов из базы данных над теми полями в которых у нас возникла проблема. При этом перекодировка происходит двойная - в первом случае мы переводим из текущей кодировки в бинарную, т.е. по сути получаем исходные данные без изменений, а во втором случае заставляем трактовать эти данные как текст в кодировке "Win-1251". Если в результате выполнения этого запроса, вы увидите русский текст, значит это ваш случай и данные можно будет восстановить.

Теперь для подстраховки, и чтобы случайно не испортить данные в исходной таблице, создадим ее копию, но не полную а только структуру, потому что при копировании данных они будут неправильно перекодированы. Самый простой способ скопировать структуру это вначале экспортировать в виде SQL-запроса. В моем случае, SQL-запрос на создание таблицы выглядел вот так:

 CREATE TABLE `news` (
    `id` int(10) NOT NULL auto_increment,
    `category_id` int(10) NOT NULL default '0',
    `user_id` int(10) NOT NULL default '0',
    `title` varchar(100) NOT NULL default '',
    `text` text NOT NULL,
    `date` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (`id`),
    KEY `category_id` (`category_id`),
    KEY `user_id` (`user_id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 AUTO_INCREMENT=12 ;

В этом запросе необходимо подправить название таблицы и кодировку, чтобы она изначально была корректной:

 CREATE TABLE `new_news` (
    ....
 ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 PACK_KEYS=1 AUTO_INCREMENT=12 ;

После выполнения скорректированного запроса в базе данных появится новая таблица - проверьте, что в ней указана корректная кодировка во всех текстовых полях.

Теперь осталось самое сложное - нам нужно будет перенести данные из старой таблицы в новую, сконвертировав их по пути в необходимую нам кодировку. Мы это сделаем путем создания сложного запроса вставки, использующего вышеупомянутую функцию CONVERT(). Для моего случая данный запрос будет выглядеть следующим образом:

 INSERT INTO 
    new_news
 SELECT 
    id,
    category_id,
    user_id,
    CONVERT(CONVERT(title USING binary)USING cp1251),
    CONVERT(CONVERT(text USING binary)USING cp1251),
    date
 FROM 
    news

Заметьте, что в выборке необходимо явно указать все поля из исходной таблицы, а для полей с неправильной кодировкой прописать функцию двойного конвертирования, которая была описана раньше. После выполнения этой команды, проверьте средствами "phpMyAdmin", что русский текст показывается корректно. Теперь можно все проверить и на рабочем сайте, но чтобы защититься от сбоев, давайте сначала переименуем исходную таблицу в "old_news" и уже затем перезапишем ее вновь созданной таблицей:

 ALTER TABLE `news` RENAME `old_news`
 ALTER TABLE `new_news` RENAME `news`

Если после этих операций на сайте все отображается корректно и работает как надо, то теперь можно и удалить старую таблицу:

 DROP TABLE `old_news`

И, самое лучшее, что вы теперь можете сделать это полный бэкап всей текущей базы данных. Ведь если бы он у Вас был, то и эта статья бы была не нужна, правда?

В процессе поиска решения проблемы, очень полезная информация была найдена вот на этом форуме, поэтому если у вас что-то не получилось, то имеет смысл сходить туда.

Теги: MySQL, базы данных, программирование, решение проблем

Cherry-Notes

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

Смотри также