Форум пользователей MySQL

Доброго дня, коллеги!
Хочу поделиться опытом успешного исправления знаменитой проблемы неправильно настроенных кодировок в БД.

Итак, что было:
У меня есть довольно большая MySQL база суммарным размером ~ 3ГБ. Все таблицы на MyISAM. Проблема заключалась в том, что на текстовых полях  типов Char, VarChar, Text итд с utf8_general_ci не работали функции CHAR_LENGTH, LIKE '%..' и подобные строковые функции. Особенность момента заключалась в том, что скрипты на сайте отображали UTF-8 строки правильно и все параметры из 'SHOW VARIABLES' указывали на использование UTF-8:

character_set_client     utf8
character_set_connection     utf8
character_set_database     utf8
character_set_results     utf8
character_set_server     utf8
character_set_system     utf8
collation_connection     utf8_general_ci
collation_database     utf8_general_ci
collation_server     utf8_general_ci

Оказалось, строки в этих полях хранились дважды кодированными. Основным супер-помошником стала эта инструкция: http://melp.nl/2011/01/about-mysql-utf- … headaches/


Как чинил:

1) Я сделал копию базы:
# mysqldump -u root -p... --opt my_db | mysql -u root -p... my_db_copy
Далее всё далал во вновь созданной.

2) Во всех таблицах все ключи на всех полях с УТФ-8 удалил.

3) Для всех полей UTF-8 в базе я выполнил три запроса:
ALTER TABLE `table` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci; (конвертирует все поля таблицы в latin1 ! Можно выполнить один раз для всей таблицы)
ALTER TABLE `table` MODIFY comment BLOB NOT NULL; (вот по-этому надо заранее удалять ключи в пункте 2)
ALTER TABLE `table` MODIFY comment varchar(256) CHARSET utf8 COLLATE utf8_general_ci  NOT NULL; (varchar(256) - исходный нужный нам тип поля)

Все эти запросы я подготовил заранее в текстовом файлике, чтобы во "время Ч" свести к минимуму неожиданности.

4) Заново пересоздал ключи, которые удалил в п. №2

5) Вставил в скрипты сайта строку mysql_query(SET NAMES UTF-8) и перевел их на работу с новой базой.
Ранее сайт команду "SET NAMES UTF-8" не использовал, без перекодировки само по себе ни "SET NAMES UTF-8" ни "SET CHARSET UTF-8"
не помогали.

В результате:
- Функции CHAR_LENGTH, LIKE '%..' и подобные теперь работают правильно.
- Сам процесс "починки" получился весьма быстрым и минимально сказался на работе сайта.
- Субъективно, "на глаз" - немного уменьшилась нагрузка Load Average и IO сервера.

Спасибо авторам за замечательную инструкцию: http://melp.nl/2011/01/about-mysql-utf- … headaches/