Когда я только начал осваивать InnoDB и транзакции в MySQL (понадобилось обновить версию с 3.23 до 4.1) столкнулся с проблемой некорректного обмена данными между PHP и MySQL которая проявлялась в том, что сервер вместо символов кириллицы, в запросах генерируемых php-скриптом, вставлял в ячейки таблиц БД знаки вопроса. В процессе «выкуривания» документации, чтения форумов и изучения статей пришло понимание проблемы и нашелся способ ее решения.
Первопричиной проблемы оказалось то, что до версии 4.1 кодировку можно было задать только для всего сервера (определив значение параметра
Немного терминологии
CHARACTER SET — это некий набор символов, называемых кодировкой. Разные CHARACTER SET включают в себя различные наборы символов. Различные CHARACTER SET могут включать примерно одинаковые наборы символов но в различном порядке (см. например koi8ru и cp1251). MySQL необходимо знать какой CHARACTER SET будет использован для данных в таблице, чтобы корректно проводтиь сортировку и индексацию данных.
COLLATION — способ, с помощью которого следует упорядочивать и сравнивать данные в БД.
Для одного и того же CHARACTER SET существует как правило несколько COLLATION. Например: cp1251_general_ci — сравнение не чувствительное к регистру, cp1251_bin — чувствительное к регистру.
Для того чтобы избежать проблем с кодировками нужно иметь представление о том, как для разных уровней иерархии СУБД их можно задать.
Способы задания кодировок
1) Для всего сервера при компиляции, определив параметры
2) Для всего сервера при запуске mysqld, определив параметры
3) При создании БД:
4) При создании таблиц:
5) В определениях столбцов:
Для того чтобы посмотреть к какой кодировке привязана структура данных можно воспользоваться оператором
Работа с клиентскими программами
Любой mysql-клиент при соединении с сервером может установить несколько переменных:
Первопричиной проблемы оказалось то, что до версии 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 достаточно выполнить:mysql_query("SET character_set_client='cp1251'");<br>mysql_query("SET character_set_connection='cp1251'");<br>mysql_query("SET character_set_results='cp1251'");
Для того чтобы посмотреть значения этих переменных установленные по умолчанию можно воспользоваться операторомmysql_query("SET NAMES 'cp1251'");
SHOW VARIABLES
:SHOW VARIABLES LIKE 'character_set%';