Зміна кодування таблиць в mysql, записки самоучки

Отже, все почалося з того, що ви встановили MySQL, створили таблиці, наповнили їх даними, а потім виявили, що запити до таблиць, в яких є пошук або сортування по полях типу CHAR, VARCHAR, TEXT видають непередбачувані результати. Якщо це відбувається, то, швидше за все, у вас некоректно вказані кодування для даних. Спробую пояснити, в чому проблема. Сподіваюся, ви розумієте, що таке кодування. Якщо немає, то прогугіліте питання і отримаєте хоча б первинні знання.

Отже. Простежимо, як вийшло так, що у вас з'явилися проблеми з кодуванням. MySQL при установці за замовчуванням вказує кодування для БД latin1. Більшість клієнтів, при підключенні, налаштовані на кодування latin1. Ви, користуючись клієнтами, вносите дані, переглядаєте результати і т.д. При цьому скрізь використовуєте кодування latin1. Це кодування дозволяє коректно відображати кирилицю. На цьому коректність в роботі з кирилиці в кодуванні latin1 закінчується. Cимвол ви бачите правильні, але коди в таблиці символів в даному кодуванні не відповідають реальним кириличним символам. І виходить, що пошук і сортування видають непередбачувані результати, тому що робота відбувається не з символами, які ви бачите, а з кодами. Може, заплутано пояснив, тоді ставлю перед фактом: кодування latin1 - це некоректна кодування для повноцінної роботи з крілліцей. Для коректно роботи з кирилицею потрібно вибирати кодування, які підтримують кирилицю. Для того, що б дізнатися повний список цих кодувань RTFM по MySQL. Я ж зупинюся на двох поширених з них: utf-8 і cp1251. Почну з cp1251. тому що це рідна кодування для ОС Windows.

Отже, перед нами стоїть завдання: конвертувати дані, з самого початку некоректно заявленої кодування latin1 в коректну кодування cp1251. Йдемо в документацію з мови:

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:
ALTER TABLEtbl_nameCONVERT TO CHARACTER SETcharset_name; (1)

Чи не поспішаємо виконувати запропоноване рішення, а Новомосковськ далі:

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:
ALTER TABLEt1CHANGEc1 c1BLOB; (2)
ALTER TABLEt1CHANGEc1 c1TEXT CHARACTER SETcp1251; (3)

The reason this works is that there is no conversion when you convert to or from BLOB columns.

Просте конвертування даних (1) нам не підходить, тому що у нас некоректні коди для символів, а так як конвертація відбувається згідно з таблицею кодів, то ми отримаємо некоректний результат. Вихід в даній ситуації - це позбутися від прив'язки символів до кодування. Це робиться шляхом перетворення даних і символьного типу в двійкові дані (2). Уявіть, що кодування - це маска, яка накладається на двійкові дані. Тобто сам порядок байтів не змінюється, а змінюється тільки маска, за якою обираються коди для візуального формування символів. Отже, запитом (2) ви позбулися маски, а запитом (3) ви застосували нову маску. Повторюся ще раз (грубо кажучи): фізично ми не змінили жодного байта даних, ми змінили правило формування символів! У підсумку ви отримуєте наступне: самі дані як були коректними для кодування cp1251, так вони і залишилися, але тепер ми правильно вказали кодування cp1251.

Після таких маніпуляцій життя вашої БД і вибірок повинна налагодитися, бо тепер все сходиться і все на своїх місцях. Тепер, якщо ви захочете змінити кодування таблиці і полів, то можете сміливо користуватися запитом (1). До речі, я зараз думаю переходити з cp1251 на utf-8, але поки що не можу чітко озвучити, навіщо мені це треба: деякі передумови є, але ясного розуміння поки що немає.

  1. Якщо ви конвертуєте дані, з самого початку некоректно заявленої кодування latin1 в коректну кодування cp1251. Не можна просто взяти і змінити кодування для шпальти, тобто не виконувати запит (2), а відразу виконати запит (3). Тому що виконавши відразу запит (3) ми фізично не перешикуємо маску, а просто змінимо поточне значення маски. Якщо у вас немає проблем з невідповідністю кодувань, і вам потрібно просто поміняти кодування таблиці, то вам потрібно всього-на-всього виконати запит (1).
  2. Якщо ви конвертуєте дані, з самого початку некоректно заявленої кодування latin1 в коректну кодування cp1251. При цьому ви змінюєте кодування у поля, у якого є індекси, то перебудуйте індекси (видаліть і створіть заново).
  3. Якщо ви вирішите переходити на кодування utf-8 - перевірте, що б візуальні клієнти підтримували unicode. Наприклад, так гаряче мною улюблений SQLyog 5.2 курить бамбук при відображенні вмісту таблиць, зберігають дані в utf-8.
  4. MySQL почав підтримувати unicode тільки з версії 4.1.x, тобто до цього підтримки юникода не було. Це означає, якщо ви захочете перенсті таблиці з юнікодом на версію молодше 4.1.x, то у вас нічого не вийде. Виходів два: або конвертувати дані з юнікода в щось менш мультибайтних, наприклад, cp1251; або оновлювати СУБД до версії не молодше 4.1.x. Оновлення СУБД предпочітетльней, тому що у MySQL вже 3ка по-моєму не підтримується і 4ка теж скоро перестане підтримуватися, при цьому активно 5кА в двох гілках розробляється. Та й весь світ в юникоде активно в мережі працює.