Конвертирование БД MySQL из кодировки latin1 или cp1251 и всех её данных в базу с данными в UTF-8

Способ 1. Если нет времени вникать, надо сделать быстро!

Пусть, например, наши таблицы в базе имеют кодировку latin1, а данные - cp1251. Делаем слеующее:
- экспортируем БД с кодировкой latin1;
- (*) конвертируем данные (см ниже примеры скриптов iconv) из cp1251 в utf8; (если данные без русских символов, то наверное можно пропустить);
- заменяем в дампе всё, что связано с latin1 на utf8 с помощью поиска и массовой замены по всему дампу БД;
- создаём новую базу в utf8 или удаляем все таблицы из старой базы;
- разворачиваем (импортируем) дамп уже с данными в utf8 в новую базу!!!

* - Следующий скрипт находит все файлы в текущей директории с расширением *.java, перекодирует их в UTF-8 и результат копирует в папку res

mkdir res
find -maxdepth 1 -iname  "*.java"  -type f -exec bash -c "cat {} | iconv -c -f WINDOWS-1251  -t UTF-8> ./res/{}" \;

перекодирование всех файлов в директории с их заменой

for i in *; do iconv -f WINDOWS-1251 -t  UTF-8 "$i" >tmp; mv tmp "$i"; done

или файлы необходимого типа и рекурсивно (в примере txt файлы):

find . -name '*.txt' | while read i; do iconv -f  WINDOWS-1251 -t UTF-8 "$i" >tmp; mv tmp "$i"; done

или так (рекурсивное перекодирование всех файлов html):

#!/bin/sh
for i in `find . -name '*.html' -type f`
    do
    echo $i
    iconv -f WINDOWS-1251 -t UTF-8 "$i" > tmp
    mv -f tmp "$i"
    done

Способ 2. Хочется разобраться что к чему!

Кодировки БД MySQL – бывают правильные – те которые нам подходят и неправильные – которые нам не надо. Если у вас сайт на русском языке, а для вашей базы данных выставлена кодировка latin1, значить это второй вариант – нас эта кодировка не устраивает. То есть нам нужна другая кодировка – cp1251 (родная кодировка виндовса) или utf-8 (в настоящее время быстро набирающая оборотов в использовании). Почему в большинстве случаев используется latin1 – а причина наверное в том, что MySQL при установке по умолчанию указывает кодировку latin1 для вновь созданных БД и скорей всего потому, что разработчики живут и кодят в Швеции. И если при установке и/или настройке не удосужились изменить, то такой она и останется по умолчанию (кстати можно наверное взять на заметку – если ваш хостер создает вам БД с кодировкой по умолчанию в latin1 то можно предположить что он не изменял умолчания, а это в свою очередь может свидетельствовать о невысоком уровне квалификации или ему просто лень, что кстати тоже есть очень плохо…). Но дело не в умолчании и хостинге, а в возможных проблемах. Да сайт будет работать и текст будет показываться нормально – но например при поиске будет выдавать совсем не то что просишь.

Я столкнулся с проблемой неправильной кодировки при настройке joomla, также при переносе с одного сервера на другой БД торрент трекера, ну и собственно везде где она изначально неправильно настроена.

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

If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

Но если почитать дальше

Warning: The preceding operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like cp1251). In this case, you have to do the following for each such column:

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

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

1. Узнаем в какой кодировке лежат наши данные в БД
Открываем phpmyadmin, выбираем нашу БД и смотрим на столбец Сравнение для таблиц – в моем случае это – latin1_swedish_ci.
То есть у меня стоит latin1_swedish_ci (Шведский, нечувствительный к регистру) – спрашивается зачем мне для русскоязычного сайта хранить данные в шведской кодировке?

2. Сохранение дампа (бэкапа) в кодировке latin1
Это надо провернуть так что бы не получилось не читаемое мясо. Делается в консоли сервера

mysqldump -uuser -ppassword bdname --allow-keywords --create-options --complete-insert --default-character-set=latin1 --add-drop-table > dump_bdname.sql

user – имя пользователя для доступа к БД
password – пароль этого юзера
bdname – имя базы для которой мы делаем дамп
–default-character-set=latin1 к этому ключу особое внимание – после знака равно надо писать кодировку вашей БД.

3. Перекодировка дампа в нужную кодировку и с нужными параметрами

iconv -f ISO-8859-1 -t UTF-8 dump_bdname.sql > dump_bdname_utf8.sql

Назначение ключей следующее:
-f ISO-8859 – конвертировать из кодировки ISO-8859 (вы можете спросить почему iso-8859-1 а не latin1, потому что это одно и то же, да и учтите что у вас может быть другая кодировка)
-t UTF-8 – в кодировку UTF-8
dump_bdname.sql – файл который мы будем конвертировать
dump_bdname_utf8.sql – результаты запишутся в этот файл
Этой командой мы все что есть в latin1 переделываем в utf8.
Также в случае проблем можно запустить с ключем -c (пропускать на выводе недопустимые знаки).

iconv -c -f ISO-8859-1 -t UTF-8 dump_bdname.sql > dump_bdname_utf8.sql

(вариант для виндовса - можно открыть например через Notepad++ нажать выделить все (Ctrl+A), вырезать (Ctrl+X), потом выбрать Кодировки - convert to UTF-8 и вставить (Ctrl+V). )

4. Расстановка правильного DEFAULT CHARSET
Итак у нас есть дамп, причем в нужной нам кодировке. Но в нем так же есть записанные директивы SET NAMES codepage; и DEFAULT CHARSET codepage;
Запуск следующей команды заменяет в дампе все упоминания latin1 на utf8

cat dump_bdname_utf8.sql | replace "latin1" "utf8" > dump_bdname_utf8_replace.sql

(вариант для виндовса - опять таки в Notepad++ через поиск найти и заменить все latin1 на utf8)

5. Восстановление базы данных из сконвертированного дампа
Этой командой мы заливаем такой как нам надо дамп в нашу новую таблицу (которую предварительно надо создать)

mysql -uuser -ppassword newbdname --default-character-set=utf8 < dump_bdname_utf8_replace.sql

После этого можно спокойно пользоваться нашей базой, которая теперь в правильной кодировке.

PS: Это все осуществимо если у вас есть доступ к shell сервера (ssh).

в статье использовался материал с сайта http://rizloff.com