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

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

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

Примечания:
1. Вместо запроса SET NAMES следует по возможности применять функцию mysql_set_charset() или аналоги. Подробнеее см. ниже
2. Кодировка UTF-8 называется в mysql utf8, а Windows-1251 - cp1251


Подробные объяснения.
До версии 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 могут не совпадать с общепринятыми. Внимательно смотрите примеры

    Примечания
  • Забавно, но запрос SET NAMES не изменяет кодировку, использующуюся функцией mysql_real_escape_string. А только ради кодировки эта функция и была придумана! Для того, чтобы mysql_real_escape_string работала как задумано, и нужно менять кодировку с помщью mysql_set_charset(), а не запроса SET NAMES.
    Для mysqli соответственно вызываем mysqli_set_charset(), а для PDO кодировку надо передавать в DSN

    Впрочем, для нас это не так уж важно, поскольку для utа8 и всех однобайтных кодировок никаких негативных последствий от неправильной кодировки не будет. Не говоря уже о том, что давно пора отказаться от прослешивания вообще и использовать родные подготовленные выражения.
  • Если не хочется в каждом скрипте задавать кодировку, можно установить кодировку для всего сервера по умолчанию. Для этого в my.ini в секции [mysqld] надо написать:
    init-connect='SET NAMES utf8'
    Таким образом дефолтная кодировка будет изменена с latin1 на указанную.
    Разумеется, в скриптах ее можно будет менять на любую другую.

    ОПС
    Если бы я нашел этот текст чуть раньше, то эта статья не была бы написана. Все очень толково и подробно: 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

    • Не нашли ответ на свой вопрос?

    Задайте его в комментариях!
    (Без регистрации и смс ^_^)

    Новое на сайте:
    Описание констант PDO FETCH.

    Смотри также

    Последние комментарии