PHPFAQ  
Начинающим   Технологии   MySQL   Ошибки   Ссылки   Юмор   О сайте   Форум   PHP Club  

Проблемы с кодировкой в MySQL версий 4.1+

Быстрые рекомендации
Подробные объяснения.
Перекодировка
Исправление БД, в таблицах которой неверно указана кодировка
Если все равно ничего не получается
Примечания
ОПС
Комментарии

Быстрые рекомендации
Если из базы выводятся вопросики, то после соединения с сервером выполняем волшебный запрос
set names кодировка
Параметр "кодировка" должен соответствовать кодировке, в которой выводятся страницы на сайте.
например:
set names utf8
Если это не помогло, и всё равно идут вопросики или крокозябры - значит, криво настроена кодировка таблиц. В этом случае см. рис.1 п. "Исправление БД" ниже.
Если выводится нормально, но сортировка хромает - см. туда же.

Подробные объяснения.
До версии 4.1 кодировку данных в MySQL можно было задать только одну на всех. Теоретически, ничто не мешало в одной таблице хранить данные в юникоде, а в другой - в KOI-8. Так все и поступали: в конце концов, для БД любые данные - это всего лишь набор цифр. Что в неё положил - то она тебе и вернёт. Но правильный поиск и сортировка работали только для данных, которые были в кодировке, совпадавшей с настройками MySQL.

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

Во-первых, для каждого поля в таблице были введены два параметра: кодировка (CHARACTER SET) и правила сравнения (COLLATION).
Кодировка - это понятно. Говорим базе, в какой кодировке лежат наши данные.
Правила сравнения задают порядок сортировки и сравнения данных при поиске.
COLLATION жестко привязан к CHARACTER SET-у и может быть задан только из поддерживаемых кодировкой. Проще говоря, начало названия COLLATION должно совпадать с CHARACTER SET. К примеру, для кодировки utf8 можно задать правила сравнения utf8_bin, но нельзя cp1251_bin.
Обычно у каждой кодировки есть, как минимум, два набора правил сравнения - имякодировки_bin и имякодировки_general_ci. Первый сравнивает в лоб по кодам символов, а второй - регистронезависимо, учитывая совпадающие символы. COLLATION имякодировки_general_cs сравнивает регистрозависимо, отличаясь от _bin тем, что учитывает совпадающие символы ("е" и "ё" в русском языке), а также, при сортировке, ставит на место те символы, которые идут в кодировке не по порядку (например "ё" в 1251).
Если для поля не указан COLLATION, то он берется по умолчанию. К примеру, для utf8 - utf8_general_ci. В большинстве случаев COLLATION по умолчанию устраивает пользователя, а это значит, что его задавать не нужно. То есть, достаточно указать только кодировку.
Кодировка может быть задана для поля, таблицы, database и для всего сервера. Установки имеют характер умолчаний, и могут быть изменены на любом уровне:
Кодировку (и сортировку) можно указывать для каждого отдельного поля. Если они не указаны, то при создании таблицы берутся из кодировки, указанной для этой таблицы. Если при создании таблицы кодировка не указывается, то она берется из параметров database. Так же и при создании database - либо задаются явно, либо берутся из параметров сервера.

Во-вторых, появилась необходимость говорить базе данных, в какой кодировке мы записываем или хотим получить свои данные. То есть, появилось такое понятие, как кодировка клиента. Здесь и кроется ответ на вопрос - "откуда берутся "вопросики"? Они появляются, если кодировка таблицы не совпадает c кодировкой клиента.
Соответственно, в MySQL появились две новые команды
set character_set_client
и
set character_set_results
Первая указывает, в какой кодировке приходят данные в базу, а вторая - в какой их выдавать. Поскольку чаще всего эти кодировки совпадают, то можно писать короче - один запрос "SET NAMES кодировка", который и устанавливает оба эти параметра.

Из приведенных объяснений должно быть ясно, что для беспроблемной работы нам надо сделать всего две вещи:
1. Указывать правильную кодировку клиента.
Это можно сделать либо в настройках сервера в my.ini, либо тем самым запросом SET NAMES.
2. Создавая таблицы, не забывать указывать правильную кодировку для них.
Это можно сделать несколькими способами.
Самое простое - это указывать кодировку и правила сравнения прямо в коде CREATE TABLE. Пример:

CREATE TABLE `chartest` (
  `name` varchar(10) default NULL
) ENGINE=MyISAM CHARACTER SET=utf8

Но что, если у нас огромный дамп на сотни таблиц, сделанный в прошлой версии MySQL? Дописывать к каждой таблице вручную? Возможно, это и придется делать. Но сначала надо попробовать установить параметры по умолчанию.
Как мы помним, при создании таблиц, если для них не указывается collation и charset, эти параметры берутся из настроек database.
Следовательно, надо попытаться изменить эти настройки.
сначала смотрим, какие они сейчас: заходим в консоль и пишем

use `mydb`
show variables like "character_set_database";
Этот запрос выведет кодировку базы mydb по умолчанию.
Если она нас не устраивает, то пытаемся переопределить настройки самостоятельно

alter database `mydb` character set utf8;

Если запрос прошел успешно, то проверяем ещё раз, и, если все нормально, то начинаем создавать таблицы или заливать дамп.
Если таким образом сделать не удалось (не хватает прав), то варианта только два - или обращаться к провайдеру, чтобы он сам поменял настройки, или дописывать COLLATION И CHARACTER SET ко всем создаваемым таблицам вручную.

Перекодировка
Как следует предыдущих объяснений, кодировка клиента должна соответствовать реальной кодировке поступающих данных. В этом случае, даже если данные лежат в другой, то всё равно никаких проблем не будет - MySQL автоматом перекодрует туда и обратно.
Проведем эксперимент. Для него нам потребуется MySQL, установленная под Windows. Для тех, у кого другая ОС, я думаю, поменять кодировки в терминале проблемы не составит.
Для демонстрации возможностей перекодировки воспользуемся тем фактом, что по умолчанию консоль windows настроена на старую кодировку DOS - 866. То есть, сначала мы создадим таблицу в этой кодировке и запишем в неё данные, а потом попробуем общаться с базой в другой кодировке.

Сначала запустим командный интерпретатор cmd.exe и установим в свойствах окна шрифт Lucida Console.
затем вызываем консоль mysql:
C:\MySQL\bin\mysql.exe -uroot test
В консоли пишем:

set names cp866;
CREATE TABLE ct (`name` varchar(10) default NULL)CHARACTER SET=cp866;
insert into ct values ('Вова');
select * from ct;

Если мы все сделали правильно, то вывод будет таким:
+------+
| name |
+------+
| Вова |
+------+

дальше пишем exit, выходим из консоли, и пишем команду
chcp 1251
которая сменит кодировку окна консоли windows на 1251
затем снова запускаем консоль mysql и пишем:

set names cp1251;
select * from ct;
insert into ct values ('Вова');
select * from ct;

То же самое можно повторить и для кодировки utf8 (chcp 65001).
В результате мы видим, что даже тогда, когда данные поступают не в той кодировке, в которой они хранятся в базе, работа с ними происходит совершенно корректно. При этом они продолжают лежать в базе в той же самой кодировке, в которой они были с самого начала - 866.

Возможности перекодировки ограничиваются, разумеется, одним и тем же языком. То есть, из 1251 можно перекодировать в 866, в koi8r, в UTF8. В latin1 из 1251 перекодировать нельзя - появятся вопросики.

Исправление БД, в таблицах которой неверно указана кодировка
Или что делать, если буквы нормальные, а поиск и сортировка работают странно.

Итак, у нас есть проблемы, которые не решаются запросом SET NAMES. Это значит, что в таблицах лежат данные в одной кодировке, а указана для этих таблиц - другая. В принципе, быстрое решение этой проблемы можно вывести из предыдущих объяснений - сделать запрос SET NAMES с кодировкой, которая указана в таблице. Посмотреть её можно запросом show create table `table`.
Если там в последней строчке написано DEFAULT CHARSET=latin1, то выполняем запрос SET NAMES latin1
В таблице не будет работать толком сортировка и поиск, но хотя бы сами данные будут отдаваться и записываться нормально (если кодировка html страницы соответствует фактической кодировке лежащих в базе данных). Но это, конечно, ненормальная ситуация, тем более, что исправить её совсем несложно.
Что мы сейчас и проделаем.

Для исправления существует два способа, которые описаны по ссылкам внизу.
Я воспользуюсь вариантом из FAQ сайта linux.by, приведя только его содержательную часть. Но настоятельно рекомендую прочитать подробный вариант, с объяснением опций и исправлением возможных ошибок.

  • Узнаём кодировку таблиц (show create table `table`).
  • Делаем дамп БД с помощью mysqldump
    Допустим, мы выяснили, что таблицы были созданы по умолчанию в кодировке latin1, а фактически в них содержатся данные в utf8. В этом случае используем команду:
    mysqldump -uUSERNAME -pPASSWORD DB_NAME --allow-keywords --create-options --complete-insert --default-character-set=latin1 --add-drop-table > dump.sql
    Распространненая ошибка в таких случаях - когда в --default-character-set указывают фактическую кодировку данных, в данном случае - utf8. В дампе будет мусор. Указывать надо ту, которая установлена в таблицах. В результате MySQL не будет пытаться данные перекодировать, и отдаст как есть.
  • Просматриваем файл с дампом, чтобы убедиться, что в файле нормальные данные в кодировке utf8, а не мусор.
  • Целый и проверенный дамп копируем в сторону. Далеко в сторону.
  • В файле дампа поправляем операторы CREATE DATABASE и/или CREATE TABLE для создания таблиц в правильной кодировке. Либо меняем настройки database, как это было описано выше.
  • заливаем дамп обратно:
    mysql -uUSERNAME -pPASSWORD DB_NAME --default-character-set=utf8 < dump.sql
  • в код сайта, после функций mysql_connect и mysql_select_db добавляем строчку
    mysql_query("SET NAMES utf8");
  • Всё, можно работать!

    Если все равно ничего не получается
    Большое количество вопросов по кодировкам, не имеющих отношения к БД, побудило меня составить небольшое сводное руководство. Итак, кодировка нашего сайта складывается из 4 пунктов:
    1. Кодировка базы данных.
    • Задается при создании таблиц.
    • Может быть любая. Должна отражать реальную кодировку данных в таблице.
    • Например, если данные у нас будут лежать в кодировке Windows-1251, то создавая таблицу, пишем CREATE TABLE chartest (string text) DEFAULT CHARSET=cp1251;
    • Проверить текущую кодировку таблицы можно запросом SHOW CREATE TABLE tablename
    2. Кодировка клиента БД (клиентом в данном случае является скрипт, работающий с БД).
    • Задается сразу после соединения с БД, запросом SET NAMES кодировка
    • Должна совпадать с кодировкой выводимой HTML страницы.
    • Например, если страница у нас в utf-8, то в PHP пишем mysql_query("SET NAMES utf8");
    • Проверить можно запросом show variables like '%char%'; (переменные character_set_client, character_set_connection и character_set_results должны иметь установленное нами значение)
    3. Кодировка сайта.
    • Задается HTTP заголовком Content-type.
    • Должна соответствовать кодировке данных на странице.
    • Например, если страница у нас в utf-8, то в PHP напишем header("Content-Type: text/html; charset=UTF-8");
    • Проверить можно, выбрав в браузере пункт меню Вид - кодировка. Так же полезно посмотреть, какую кодировку шлет сервер в заголовке.
    4. Кодировка данных на странице.
    • Задается в редакторе.
    • Должна соответствовать той кодировке, которую мы хотим.
    • Особых рекомендаций здесь дать невозможно, но хотя бы минимальная компьютерная грамотность, чтобы выбрать в редакторе желаемую кодировку при сохранении, автору сайта необходима.

    N.B.: Обозначения кодировок в mysql могут не совпадать с общепринятыми. Внимательно смотрите примеры

    Примечания
  • Решая проблемы с кодировкой БД, не забывайте указывать правильную кодировку САЙТА!
    Во-первых, убедитесь, что сервер отдает правильную кодировку в HTTP заголовке и в meta теге.
    Во-вторых, если в коде присутствуют перекодировки, то убедитесь, что они работают, и работают правильно. А ещё лучше - вообще уберите их, и пользуйтесь средствами БД.
    И вообще - старайтесь отличать проблемы браузера от проблем с БД.

    ОПС
    Если бы я нашел этот текст чуть раньше, то эта статья не была бы написана. Все очень толково и подробно: http://www.linux.by/wiki/index.php/FAQ_PHP_MySQL_charset
    Еще один неплохой текст по кодировкам: http://mysqlfaq.wikispaces.com/Encoding
    Официальная документация, разумеется. Я, конечно, знаю, что ни один из читателей этого фака не пойдет читать документацию на английском... Но сам я брал информацию именно оттуда. http://dev.mysql.com/doc/refman/5.0/en/charset.html

    Другие материалы раздела:
    \"Кавычки \". Cоставление запросов, слеши, SQL Injection
    Как разбить вывод из mysql постранично
    Пример кода, работающего с MySQL
    Базовые понятия MySQL и отличия от текстовых файлов.


    • Комментарии

    Кирилл 20.07.10 12:24
    Спасибо)) а для тех, кто юзает mysqli, используйте $_DB->query("SET NAMES 'utf8'"); где $_DB - имя вашего соединения, utf8 - используемая кодировка. Кстати, очень всем советую (mysqli имеется ввиду). Работает реально быстрее, и код красивей. А ещё, как утверждают разработчики практически неуязвим;)
    Дима 30.06.10 18:39
    мудохался мудохался и оказываеться в поле - "сравнение" никакую кодировку мне не ненадо было вставлять. она автоматом выставляется какая надо. и вопросики исчезают. Зато статьи интересные почитал и русифицировал заодно. Спасибо
    Samu 21.05.10 16:53
    спасибо тебе большое за подробную информацию.. ковырялся день не мог решить прблему - натолкнулся на твою статью и сразу все исправил Спасибо!
    СТАС 16.05.10 12:13
    Спасибо большое за статья mysql_query("SET NAMES utf8"); мне помогло,поставил сразу же после запроса на соединения с базой данных.
    Жан 09.05.10 15:25
    Спасибо
    mysql_query("SET NAMES utf8"); Помогло
    iPlayer 02.05.10 14:03
    Спасибо! Я уж думал, что никогда не найду ответа!
    Теперь в случае чего сразу на этот сайт заходить буду.
    Макс 20.04.10 01:08
    Разобрался в utf8 после долгих и безнадежных боев с глюком кодировки в БД
    Спасибо!
    m0uz 31.03.10 23:57
    Пожалуйста помогите мне, вот проблемка с запросом.
    после обновления странички теста все в ?? чем мне можно помочь! зарания спс

    CREATE TABLE IF NOT EXISTS `source_stat` (
    `src` varchar(8) NOT NULL default '0',
    `date` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
    `count` int(9) NOT NULL default '1',
    `type` tinyint(1) NOT NULL default '0' COMMENT 'Код страницы продажника',
    `test` tinyint(1) NOT NULL default '0' COMMENT '',
    PRIMARY KEY (`src`),
    KEY `count` (`count`)
    ) ENGINE=MyISAM DEFAULT CHARSET=cp1251
    Jean 18.02.10 21:22
    Статья очень помогла, два дня лазил по форумам и только тут узнал толком про сравнение.
    В PHP, MySQL5 и на страницах - cp1251 однако всеравно браузер выдовал вопросы. После того как изменил COLLATION cp1251_general_ci на cp1251_bin проблема была решина. Огромное спасибо автору, =) респект и уважуха =).
    Иналь 10.02.10 14:40
    Спасибо за интересную статью

    Написать комментарий
    Пожалуйста, воздержитесь от посылки спама.
    Сообщения, содержащие гиперссылки, проходят премодерацию.
    Представьтесь:
    Вы робот?
    Сообщение:

    © phpfaq.ru, 2010
    Rambler's Top100 0.022 sec.