Проблеми с кодировката на MySQL бази данни

Няма уебмастер, който да не се е сблъсквал с този проблем – при преместване на сайт на друг сървър, най-често при промяна на хостинг компания, в браузера се появяват питанки или нечетими символи, добили попупуярност в българската интернет мрежа като 'маймуница'.

Проблемът се състои в следното:

Всеки сървър е настроен да създава новите бази данни в кодировка по подразбиране. Кодировката по подразбиране на всеки MySQL сървър е latin1 с колация *latin1swedishci*.

На сървърите на ICN.bg кодировката по подразбиране е cp1251 с колация cp1251generalci.

Glossary of Unicode Terms дефинира двете основни понятия в тази статия - Character Set (кодировка) и Collation (колация), по следния начин:

Character Set (кодировка) – съвкупност от елементи, които изобразяват тесктова информация (букви, цифри и всички графични символи, които се използват за писане на съответен език).

Collation (колация) – процес на подреждане и сравняване на отделните елементи, представляващи текстова информация, познат още като подреждане по азбучен ред.

На сървърно ниво (тази информация би трябвало да е изключително полезна за клиентите с VPS сървъри) имате възможност да дефинирате кодировката и колацията на базата данни и никъде след това няма да ви се налага да дефинирате системните променливи *charactersetserver и collation_server* отново.

Ако не е посочена кодировка при инсталиране на сървъра, MySQL ползва кодировката и колацията по подразбиране.

Подробна информация може да намерите на този адрес:

http://dev.mysql.com/doc/refman/5.0/en/charset-server.html

На ниво приложения дефинирането на кодировката и колацията се прави за съответната база данни, която ползва приложението. Важното тук е да се знае реда на приемственост при дефинирането, което ще ви спести писането на доста излишен код и възможността от допускане на грешки там, където MySQL има много практично решение.

Реда на приемственост е следният:

сървър > база данни > таблица > колона

Ако няма дефинирана кодировка за нито един елемент от този списък се прилагат настройките по подразбиране на сървъра върху цялата база данни, таблици и колони.

Настройките се предават по наследство от предишното ниво ако за съответният елемент няма въведени други настройки.

Може да изглежда натруфен и ефектен кода с дефиниране на charactersetclient, charactersetresults и charactersetconnection, но инструкцията е много кратка и ясна – mysql клиента изпраща желаната кодировка на сървъра и той изпълнява SET NAMES операция с посочената кодировка без да е необходимо допълнително дефиниране на тези промениливи поотделно.

Силно препоръчам да прегледате материалите по тази тема:

http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

Ще направим няколко елементарни теста като за целта ни трябват две бази данни, които съдържат един и същ текст – едната с кодировка utf8, другата – естествено с кодировка cp1251.

Създаваме и два файла - cp.php и utf.php, които визуализират в браузер съдържанието на всяка база данни.

Във всеки файл, в HEAD секцията на кода, въвеждаме meta таг, съответстващ на кодировката на базата данни и това е единствената разлика между тях:

utf.php - <META http-equiv="content-type" content="text/html; charset=utf-8">

cp.php -  <META http-equiv="content-type" content="text/html; charset=windows-1251">

Ето и съдържанието на файла utf.php:

<?php

$con = mysql_connect ('localhost', 'db-user', 'db-pass');

$db_sel = mysql_select_db ('db-name', $con);

$sql = "SELECT * FROM utf8 WHERE id = 1";

$qry = mysql_query ($sql, $con);

$rs = mysql_fetch_row ($qry);

$testencoding = $rs[1];

mysql_free_result ($qry);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<META http-equiv="content-type" content="text/html; charset=utf-8">

<title>Encoding Test</title>

</head>

<body>

<table style="border:1px solid [#999](https://www.icn.bg/bg/blog/?tag=999){: .tag-link}; background:url(utf.jpg);background-repeat: no-repeat; margin:10px 0 0 140px;" width="350"height="160"border="0" cellpadding="10" >

<tr>

<td>

<?php

echo $testencoding;

echo "<br>";

echo strlen($testencoding);

?>

</td>

</tr>

</table>

</body>

</html>

Вече знаете, че кодировката по подразбиране на MySQL сървърите на ICN.bg e cp1251.
Нека заредим в браузер файла cp.php

коректен текст на кирилица cp1251

Текстът на кирилица се визуализира коректно.

Нека заредим файла utf.php

нечетима кирилица utf-8

Символите на кирилица са нечетими защото mysql сървъра кодира информацията от базата данни в кодировка cp1251, a кодировката на нашата база данни е utf8.

Нека укажем на браузера да кодира информацията в cp1251 като променим meta таг параметъра от utf-8 на windows-1251:

коректна кирилица с utf мета таг

Отново имаме четима кирилица но със същия брой символи както при зареждане на файла cp.php – 169.

Сега нека добавим във файла utf.php следния код вeднага след връзката с базата данни (ред 3):

mysql_query ('SET NAMES utf8');

Натискаме бутона F5 за да рефрешнем браузера и ...

нечетима кирилица meta set names utf8

Отново кирилицата е на маймунки!

Да, защото оставихме meta тага със стойност windows-1251. Нека го променим на utf-8 и да рефрешнем отново браузера:

четима кирилица utf-8

Най-после имаме четима кирилица в utf-8 кодировка – забележете броя на символите на последния ред в изображението:

При кодировка cp1251 броят на символите е 169, докато при utf8 той е 243.

По същият начин, с добавяне на SETNAMES utf8 и meta таг utf-8 можем да имаме четима кирилица и от базата данни cp през файла cp.php в кодировка utf8 с 243 символа:

кирилица ок

Нека обобщим резултатите от тези тестове:

Вашият скрипт играе ролята на посредник между mysql сървъра и браузера.

Когато има въведен параметър за setnames и съответстващ meta таг, текстът на кирилица се визуализира коректно без значение дали базата данни е в кодировка cp1251 или utf8.

SETNAMES казва на сървъра: дай ми текста от тази колона в кодировка Х , meta таг казва на браузера: покажи тази информация като ползваш Х кодировка.

Когато кодировката на двата параметъра е една и съща имаме коректно визуализиране на текста, а когато е различна – получаваме маймунки.

Абсолютно същият резултат ще се получи ако във всяка от двете бази данни импортираме архив с кодировката на другата база – setnames и meta таг ще се погрижат да имаме коректна и четима кирилица.

Кога се появяват питанките?

Нека променим setnames в единия файл – без значение кой, нека бъде utf.php от utf8 на latin1:

mysql_query ('SET NAMES latin1');

Нека да дадем на meta тага стойност windows-cp1252:

<META http-equiv="content-type" content="text/html; charset=windows-cp1252">

Сега да заредим файла utf.php в браузера:

питанки latin1

Всички символи на кирилица сега са заменени с питанки защото кодировката latin1 е само за латиница и символи на шведски език.

Тествете, които направихме дотук бяха на ниво скрипт и не засягаха бинарните файлове на mysql архива.

Това се е случвало много пъти

Нека разгледаме една хипотетична и в същото време много реална ситуация – вие имате хостинг в компания извън България и желаете да се преместите в ICN.bg защото сте чули, че ние предлагаме хостинг на световно ниво на достъпни цени.

Свързвате се с нас, ние ви казваме от какво имаме нужда за да ви съдействаме при преместването на сайта – достъп до файловете и архив на базата данни.

Вие ни давате данните, ние извършваме преместването, зареждаме сайта и ... виждаме безброй питанки.

Какво се е объркало?

MySQL сървъра на хостинг компанията е създал архив в кодировка, която не съдържа кирилица (различна от cp1251 и utf8), и ние сме импортирали повреден архив в базата данни.

Ще ви покажа на практика какво се е получило:

Нека създадем архив в кодировка, която не поддържа символи на кирилица – latin1.

Отваряме през phpmyadmin нашата база данни utf, през бутона Export включваме радио бутона custom:

phpmyadmin архив latin1

Избираме енкодинг cр1252:

phpmyadmin кодировка cp1252

Mаркираме полето Add DROP TABLE statement:

phpmyadmin add drop table

натискаме бутона Go и запазваме архива като .sql файл на локалният компютър.

phpmyadmin Go

Отваряме архивният файл с текстов редактор и какво виждаме?
Няма награда за позналите – питанки вместо кирилица:

архив на базата данни latin1

Сега нека импортираме този явно повреден вече архив в кодировка utf8 в базата данни и да видим дали кирилицата ще се възстанови.

phpmyadmin import

phpmyadmin import approval

Променяме setnames и meta енкодинг тага съответно на utf8 и utf-8 и зареждаме файла utf.php

неуспешно реверсивно конвертиране

Няма реверсивно конвертиране (възстановяване) – веднъж повредени, бинарните файлове не може да се поправят. Просто няма как да кажеш на MySQL коя питанка е била А, и коя – Я.

Единствената ви надежда за спасение на текста на кирилица е колегите да не са изтрили базата данни и на практика само архива, който са ви предоставили, да е непоправимо повреден, но бинарните файлове да са ОК.

Затова трябва да се свържете с колегите от другата хостинг компания и да ги помолите да ви предоставят архив на базата данни в правилната кодировка.

Те естествено пазят архива, предоставят ви го в utf8 или cp1251 кодировка и като във филм на Walt Disney, накрая всички са щастливи :)

Нека обобщим:

За да няма нечетима кирилица в сайта ви, трябва да има съответствие на кодировката на следните ключови места:

  • CHARSET=utf8 COLLATE=utf8unicodeci
  • mysql_query ('SET NAMES utf8');
  • <META http-equiv="content-type" content="text/html; charset=utf-8">

!Важно: в повечето CMS приложения setnames се дефинира в конфигурационните файлове, докато meta таг e в директорията на темплейта на приложението, много вероятно във файл с името header.php (Wordpress).

Ако местите MySQL архив на друг сървър, задължително посочете на колегите в каква кодировка желаете архива или най-добре през phpmyadmin си го създайте сами.

Имайте предвид, че ако кодировката на базата данни е различна от SETNAMES параметъра, всяка информация от базата данни ще се конвертира повторно, което ще доведе до по-бавна работа на сайта и по-голям разход на процесорен ресурс за обработване на скриптовете.

На сървърите на ICN.bg e най-добре да ползвате кодировка cp1251 защото това е кодировката по подразбиране (няма да се налага допълнително конвертиране) и в същото време един и същ текст при нея се изобразява с 30% по-малко символи в сравнение с utf8, което има голямо значение за работата на всеки много натоварен сайт.

P.S. обърнете внимание, че кодировката utf8 се изписва различно в PHP кода utf8 и в meta таговете utf-8.