MySQL и база данни с кирилица

Вероятно няма уебмастър в България, койото поне веднъж да не е успял да превърне съдържанието на база данни във въпросителни знаци при експорт / импорт на бакъп дъмп файл с phpmyadmin или през shell. Ето няколко насоки за mysql енкодинг (информацията е базирана на mysql сървър 4.1+ и PhpMyAdmin 2.6.4+).

Термини
Енкодинг (encoding, character encoding), чарсет (charset, character set) - Тези понятия описван начина по който се съхраняват символите във вид на някакъв цифров код. Примери за енкодинги са windows-1251 (в mysql е cp1251), utf-8 (в mysql е utf8), ISO-8859-1 кодировка и т.н.
Колация (collation) - Това е начина по който се интерпретира енкодинга. За всеки енкодинг може да има няколко колации и ако сортирате текстовите полета от някоя mysql таблица с различни колации, вероятно ще се подредят по различен начин. Една колация може да работи с един енкодинг само. В mysql не е нужно да задавате изрично колацията. Когато зададете енкодинг cp1251 примерно, mysql ще използва по подразбиране cp1251_general_ci collation (това важи за таблици, конекции и т.н.).

Първоначално създаване на база данни
При създаване на база данни, в която ще има текст на кирилица, най-добре е да се използва енкодинг (и колация) cp1251 или utf8 (unicode). Същото важи и за таблиците и полетата вътре.


Ако не бъде изрично зададен character set, ще се използва този, който е по подразбиране в съответния mysql сървър. В момента нашите cpanel хостинг сървъри са настроени за чарсет по подразбиране cp1251, но вероятно в бъдеще ще ползваме utf8 encoding, тъй като това е стандартната кодировка, за която са настроени повечето готови cms, е-магазин, форум и др. приложения с отворен код.
Енкодинга (чарсета) на базата данни в действителност не служи за нищо друго освен да зададе какъв енкодинг да се ползва за при създаването на таблиците. Table character set, table collation, както и енкодинга и колациите за самите текстови полета определят в какъв вид ще се съхранява текста. Mysql 4.1 и по-новите версии поддържат възможността да се зададе различен енкодинг и колация за всяка таблица или поле. Тези настройки можете лесно да променяте с PhpMyAdmin. Ако сменяте енкодинга на таблица или поле, трябва да се внимава да не се преминава към енкодинг, който липсват някои от символите в оригиналния енкодинг. Например няма проблем да смените от cp1251 към utf8, koi8r или binary. Ако в базата има само латиница и кирилица, можете да смените от utf8 към cp1251. Ще загубите всичката кирилица обаче, ако смените от utf8 или cp1251 към latin1 или latin2. Добро правило е винаги да правите бекъп преди да се заигравате базата си данни. Друго добро правило е, при смяна на кодировката, да преминавате през binary (utf8 → binary → cp1251). Дори когато правите бекъп, има опасност дъмпа да е неизползваем ако примерно базата е latin1, а данните вътре са на кирилица (по-долу е обяснено какво да правите в такъв случай), затова най-добрия бекъп е да спрете mysql и да копирате самите бази в data директорията.

Изпълнение на заявки към база данни с кирилица
Правилно е да изберете един енкодинг за всички компоненти на вашия сайт. Например, ако изберете windows-1251 (cp1251), трябва или в http хедъра или с мета таг да укажете това за вашите php, cgi, aspx или други файлове. Освен това самия файл е ноебходимо да се запази със съответната кодировка. Например, опцията на Visual Studio (Express Edition е безплатен от сайта на Майкрософт) 'Save File As' има малка стрелкичка до бутона Save, където можете да изберете с какъв енкодинг да е файла. Вероятно вашият любим текстов редактор също поддържа такава опция. Проверете и настройките на вашия сървър скрипт. Примерно за PHP - default_charset в php.ini е най-добре да се остави коментирано, но може и default_charset = "windows-1251". Следващата стъпка е да настроите вашето приложение и mysql да обменят данни в един и същ енкодинг. Обикновено причината за въпросителни знаци по вашия сайт е именно заради факта, че двете си говорят на различни езици. Във конфигурационния файл на mysql може да бъде зададен енкодинг по подразбиране посредством character-set-server = cp1251 (default-character-set = cp1251) или init_connect = 'SET NAMES cp1251'. Вероятно обаче няма да имате възможност да редактирате конфигурацията на mysql сървъра ако ползвате услугите на хостинг компания. Какво ще стане ако на сървъра енкодинг по подразбиране е cp1251, а вашата база данни е с utf8 и вашите php файлове са съответно utf-8? На пръв поглед нищо няма да стане и ще работи (за съжаление)! Самите данни в базата ще се съхраняват в напълно грешен вид и проблема ще се появи едва когато се опитате да преместите базата данни на друг хостинг сървър или се опитате да я възстановите от backup. Някои от символите няма да могат вече да се четат ('ш', 'И', ...). Ако сте я докарали до тук, имайте търпение - в края на тази статия ще намерите разрешение. А за да избегенте подобни проблеми, винаги изрично уведомявайте mysql какъв енкодинг да използва по време на всяка конекция (connection):


По този начин, дори на сървъра character set по подразбиране да е cp1251, данните към и от сървъра за вашето php приложение ще се обменят в utf8 encoding.

Експорт и импорт на база данни
За експортиране на база данни можете да ползвате phpmyadmin или mysqldump, които ще генерират тескотов файл (dump), със структурата и данните на вашата база. При големи бази данни е препоръчително да ползвате mysqldump (необходим ви е ssh достъп до сървъра). По подразбиране и двете програми ще генерират дъмп в utf-8 формат независимо с какъв чарсет е базата. Това е нормално и не е проблем. Енкодинга на файла е utf-8, но ако в базата таблиците са били с cp1251, това ще бъде отбелязано в sql кода за създаване на таблицата в самия дъмп файл.


Това е дъмп създаден от mysqldump. Файлът е в unicode формат и това е отбелязано в самото начало. Така при импортиране на този файл след това mysql ще интерпретира символите правилно. Когато mysql види, че трябва да създаде таблицата в cp1251 чарсет и колация, той автоматично ще преведе чарсета от utf към cp1251. PhpMyAdmin ще направи подобен dump, но за съжаление ще пропусне 'SET NAMES' указанието. Това може да доведе до проблем, при импорт и е добре да го добавите. Ако не сте сигурни какъв точно е файловия енкодинг на вашия дъмп, можете да проверите по следния начин - дъмпа се отваря в Firefox (или друг браузър) и от View → Character Encoding се сменя докато кирилицата стане читаема. Запомня се при какъв чарсет се чете и това се записва в реда /*!40101 SET NAMES utf8 */;. Не забравяйте, че когато в браузъра е utf-8, трябва в дъмп файла да го запишете utf8, а windows-1251 като cp1251. Също така може да се ползва опцията за чарсет при ъплоуд в phpmyadmin.

Възстановяване на данни, въведени в mysql с неправилен енкодинг
Когато базата данни е с един енкодинг, а сайта ви работи с друг енкодинг, на пръв поглед всичко работи ок, но в действителност данните в базата са объркани и ако решите да направите дъмп има голяма вероятност да загубите кирилицата. Алгоритъма за оправяне на кашата е следния (приложимо за mysql 4.1 и нагоре - при по-ниски версии бекъп-нете самите файлове на базите, направете ъпгрейд):
Приемаме, че в момента базата данни ползва character set x, а сайта character set y. Също така може да изпълните командата "mysqladmin variables" за да разберете и какви са стойностите по подразбиране за character_set_client и character_set_connection. Ако са различни от тези за базата ви, може да пробвате за x техните стойности.
1. Правим дъмп като задаваме на mysqldump следния параметър: --default-character-set=x. На този етап може да се провери в браузър да се отвори дъмп-а и да се види с какъв енкодинг се чете кирилицата. Ако го четете примерно с utf8, значи в следващата стъпка за начален encoding ще зададете utf8 (iconv -f utf8 -t utf8 ...)
2. С помощта на iconv (под linux) променяме енкодинг-а на файла, като за начален енкодинг задаваме y, а за краен задаваме utf8 (при засечка пробвайте опцията -c).
3. Редактираме новополучения файл и заменяме в дефинициите на таблиците DEFAULT CHARSET=x със DEFAULT CHARSET=y. Също заменяме в началото на дъмпа SET NAMES x със SET NAMES utf8.
4. Импортираме дъмпа обратно в mysql. Ако все още не виждаме кирилица на сайта си, редактираме PHP кода и добавяме едно query, което да изъплни 'SET NAMES y' веднага след конект-ването към базата.

Ако и вие като мен не можете да разберете нищо без да видите конкретни примери, ето:
Ще разгледаме случая когато базата данни и default connection (default-character-set = latin1) са с кодировка latin1, а самия сайт ползва windows-1251.


Ако получите грешка: iconv: illegal input sequence at position ..., добавете опцията -c




Ако нямате linux под ръка за стъпка 2, по-долу е описан метод с помощта на Visual Studio (при голяма база данни ще е много бавно).
Отваряте дъмпа в Firefox (ако е по-голям, чакате да спре да зарежда и дано имате повече рам). От View → Character Encoding сменяте на windows-1251 и би трябвало да вдите кирилицата нормално. Не скролвайте по възможност много надолу, тъй като дългите редове убиват всеки текстoв редактор. С Ctrl + A селектвате всичко, копирате го и го paste-вате в Visual Studio. Тъй като сайта ви е настроен за windows-1251, ще приемем, че желаете да уеднаквите всичко с него. Това означава, че базата данни и конекцията към нея трябва да станат cp1251. Заменяте всички latin1 с cp1251, като използвате Replace All, а не едно по-едно за да не чакате дълго. Заменяте в началото на файла SET NAMES latin1 със SET NAMES utf8. Давате му Save As, и в появилия се прозорец кликате на стрелкичката до бутона Save и изберате Unicode (UTF-8 without signature).
Не забравяйте да добавите в php кода след всяка конекция към базата:



Друг пример - когато базата данни и default connection (default-character-set = cp1251) са с кодировка cp1251, а самия сайт ползва utf8.


Ако получите грешка: iconv: illegal input sequence at position ..., добавете опцията -c




happy coding :)

------
Информацията в този tutorial е валидна само за версии на MySQL Database Server 4.1 +. Ако не сте намерили разрешение на вашия проблем в статията или имате предложения за подобрения, моля пишете ни и ще се постараем да я обогатим.
При публикуване на извадки от текста, моля поставете линк към тази статия.