Когда я только начал осваивать InnoDB и транзакции в MySQL (понадобилось обновить версию с 3.23 до 4.1) столкнулся с проблемой некорректного обмена данными между PHP и MySQL которая проявлялась в том, что сервер вместо символов кириллицы, в запросах генерируемых php-скриптом, вставлял в ячейки таблиц БД знаки вопроса. В процессе «выкуривания» документации, чтения форумов и изучения статей пришло понимание проблемы и нашелся способ ее решения.

Первопричиной проблемы оказалось то, что до версии 4.1 кодировку можно было задать только для всего сервера (определив значение параметра --default-charset), а начиная с версии 4.1 разработчики добавили возможность определения кодировки на разных уровнях иерархии СУБД (для всего сервера, БД, таблиц, столбцов).

Немного терминологии

CHARACTER SET — это некий набор символов, называемых кодировкой. Разные CHARACTER SET включают в себя различные наборы символов. Различные CHARACTER SET могут включать примерно одинаковые наборы символов но в различном порядке (см. например koi8ru и cp1251). MySQL необходимо знать какой CHARACTER SET будет использован для данных в таблице, чтобы корректно проводтиь сортировку и индексацию данных.

COLLATION — способ, с помощью которого следует упорядочивать и сравнивать данные в БД.
Для одного и того же CHARACTER SET существует как правило несколько COLLATION. Например: cp1251_general_ci — сравнение не чувствительное к регистру, cp1251_bin — чувствительное к регистру.

Для того чтобы избежать проблем с кодировками нужно иметь представление о том, как для разных уровней иерархии СУБД их можно задать.

Способы задания кодировок

1) Для всего сервера при компиляции, определив параметры --with-charset и --with-collation: 
./configure --with-charset=cp1251 --with-collation=cp1251_general_ci

2) Для всего сервера при запуске mysqld, определив параметры --character-set-server и --collation-server: 
mysqld --character-set-server=cp1251 --collation-server=cp1251_bin

3) При создании БД: 
CREATE DATABASE dbname DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin;

4) При создании таблиц: 
CREATE TABLE tblname ( col INT ) DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin;

5) В определениях столбцов: 
CREATE TABLE tblname (<br>   column1 varchar(255),<br>   column2 varchar(255) CHARACTER SET cp1251 COLLATE cp1251_general_ci <br>) DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin;

Для того чтобы посмотреть к какой кодировке привязана структура данных можно воспользоваться оператором SHOW CREATE:
mysql > SHOW CREATE TABLE tree_nodes;
| tree_nodes | CREATE TABLE `tree_nodes` (<br>...<br>) ENGINE=InnoDB <b>DEFAULT CHARSET=cp1251 COLLATE=cp1251_bin</b> |

Работа с клиентскими программами

Любой mysql-клиент при соединении с сервером может установить несколько переменных:
  • character_set_client — указывает, в какой кодировке будут поступать данные от клиента;
  • character_set_connection — указывает, в какую кодировку следует преобразовать данные полученые от клиента перед выполнением запроса;
  • collation_connection — указывает, каким образом сравнивать между собой строки в запросах;
  • character_set_results — указывает серверу не необходимость перекодировать результаты запроса в определенную кодировку перед выдачей их клиенту.
Для корректной работы клиента с сервером следует установть как минимум character_set_client, character_set_connection, character_set_results при помощи оператора SET:
mysql_query("SET character_set_client='cp1251'");<br>mysql_query("SET character_set_connection='cp1251'");<br>mysql_query("SET character_set_results='cp1251'");
Если запрос и данные в БД находятся в одинаковой кодировке, а перекодировка результата не требуется, то вместо установки character_set_client, character_set_connection, character_set_results достаточно выполнить:
mysql_query("SET NAMES 'cp1251'");
Для того чтобы посмотреть значения этих переменных установленные по умолчанию можно воспользоваться оператором SHOW VARIABLES:
SHOW VARIABLES LIKE 'character_set%';