Статьи Галерея Форум Чат Файлы HowTo Ссылки Поиск

FAQ PHP MySQL charset

Материал из Linux.by Wiki Pages.
Перейти к: навигация, поиск

Содержание

По результатам борьбы с кодировками в типичной связке php/mysql нарисовался следующий рецепт:

Собственно, этот рецепт не претендует быть панацеей, и проверен толком на связке php4/php5 с MySQL 5m хотя думаю что с MySQL 4.1 тоже должно работать точно так же. Подразумевается, что MySQL и php ставились из дистрибутива, никаких особых опций связаных с кодировками (типа --default-charset) при компиляции MySql не использовалось - ибо так оно и есть в дистрибутивах. Документ не претендует на точное следование теминологии...

Терминология: CHARACTER SET, COLLATION и зачем все это нужно

CHARACTER SET - это некий набор символов aka кодировка. Разные CHARACTER SET включают в себя различные наборы символов. Различные CHARACTER SET могут включать примерно одинаковые наборы символов но в различном порядке (см. например koi8-r и cp1251) MySQL необходимо знать какой CHARACTER SET будет использован для данных в таблице, чтобы корректно проводтиь сортировку и индексацию данных. COLLATION описывает способ, которым следует упорядочивать и сравнивать данные в БД. Для одного и того же CHARACTER SET существует как правило несколько COLLATION - например: cp1251_general_ci - default collation, нечуствительный к регистру. cp1251_bin - то же самое, но с учетом регистра.
MySQL по умолчанию не чуствителен к регистру!

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select binary 'a' = 'A';
+------------------+
| binary 'a' = 'A' |
+------------------+
|                0 |
+------------------+
1 row in set (0.01 sec)

Вывод: учитывайте collation при создании структур данных.

DEFAULT CHARACTER SET и COLLATION можно задать в нескольких местах:

  • Для всего сервера - при запуске mysqld
    mysqld --character-set-server=cp1251 --collation-server=cp1251_bin
    
  • Для всего сервера - при компиляции:
    ./configure --with-charset=cp1251 --with-collation=cp1251_bin
    
  • Для БД, таблицы и столбца - при создании БД и таблицы
  • MySQL: как правильно создавать БД и таблицы

  • Самый простой способ не доспустить ошибки - создать БД с правильными CHARACTER SET и COLLATION

     create database AAA DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin; 

    В этом случае, все таблицы созданные в этой БД, если явно не заданы CHARACTER SET и COLLATION будут иметь DEFAULT CHARACTER SET и COLLATION cp1251 и cp1251_bin соответсвенно. К сожалению, пользователь не всегда может создать БД с требуемыми параметрами т.к. не зачастую имеет прав root на mysql сервере.

  • Если повлиять на параметры БД невозможно, следует корректно создавать таблицы

    :
     create table charset_test (str varchar(20) ) DEFAULT CHARACTER SET  cp1251 COLLATE cp1251_bin;
    

    В таком случае, все "текстовые" столбцы будут иметь CHARACTER SET cp1251 и COLLATION cp1251_bin

    В большинстве случаев, этого достаточно, но...

    Если же необходимо иметь в таблице данные в различных кодировках, либо часть столбцов должно обрабатывать без учета регистра, это следует указать явным образом:
     create table charset_test2 (str varchar(20), str2 varchar(20) CHARACTER SET cp1251 COLLATE cp1251_general_ci ) DEFAULT CHARACTER SET  cp1251 COLLATE cp1251_bin;
    

    Проверим, что получилось:

    mysql> insert into charset_test2 values ('a', 'A');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from charset_test2 where str2 = 'a';
    +------+------+
    | str  | str2 |
    +------+------+
    | a    | A    |
    +------+------+
    1 row in set (0.00 sec)
    
    mysql> select * from charset_test2 where str = 'A';
    Empty set (0.01 sec)
    
    

    Как видим, первый столбец чувствителен к регистру, второй - нет. Что и требовалось доказать.

    PHP/PERL и другие: как правильно обращаться с MySQL

    Любой mysql-клиент при соединении с сервером может установить несколько переменных:

  • character_set_client

    - указывает, в какой кодировке будут поступать данные от клиента
  • character_set_connection

    - указывает, в какую кодировку следует преобразовать данные полученые от клиента перед выполнением запроса
  • collation_connection

    - указывает, каким образом сравнивать между собой строки в запросах. В случае, если в запросе участвуют столбы из таблицы, COLLATION укзаный для столбца имеет приоритет. Т.е. по факту collation_connection влиеят только на запросы типа: SELECT 'a' = 'A' но не на SELECT * from charset_test2 where str = 'A';
  • character_set_results

    - указывает серверу не необходимость перекодировать результаты запроса в определенную кодировку перед выдачей их клиенту
  • Для корректной работы следует следует установть как минимум character_set_client, character_set_connection, character_set_results при помощи оператора SET.

    Если запрос и данные в таблицы находятся в одинаковой кодировке, а перекодировка резултата не требуется, то достаточно выполнить:

    SET NAMES cp1251

    Например, для php с использование "родного" mysql-интерфейса.

    mysql_query("SET NAMES 'cp1251'");
    

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

    Как посмотреть текущие настройки CHARACTER SET и COLLATION ?

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

    SHOW CREATE

    :
    mysql> show create database AAA;
    +----------+-----------------------------------------------------------------------------------+
    | Database | Create Database                                                                   |
    +----------+-----------------------------------------------------------------------------------+
    | AAA      | CREATE DATABASE `AAA` /*!40100 DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin */ |
    +----------+-----------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show create table charset_test2;
    +---------------+------------------------------------------------------------------------------+
    | Table         | Create Table                                                                 |
    +---------------+------------------------------------------------------------------------------+
    | charset_test2 | CREATE TABLE `charset_test2` (
      `str` varchar(20) collate cp1251_bin default NULL,
      `str2` varchar(20) character set cp1251 default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_bin |
    +---------------+------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    

    Обратите внимание на описание столбца str2 - при его создании COLLATION была задана в явном виде как cp1251_general_ci, а опертатор SHOW CREATE TABLE этого не показывает, логично было бы предположить, что COLLATION столбца будет cp1251_bin - т.к. это DEFAULT COLLATION для таблицы, но это не так - collation этого столбца совподает с DEFAULT COLLATION для выбраной кодировки - cp1251 (cp1251_general_ci) и не отображается явно в операторе SHOW CREATE.

    Для того, чтобы узнать значения переменных mysql необходимо выполнить оператор

    SHOW VARIABLES

    Как привести "неправильную" БД к корректному виду и избавиться от проблем с выполнением запросов

  • Узнайте текущие значения переменных mysql
  • Узнайте праметры созданых структур БД
  • Сделайте дамп БД с помощью mysqldump

    Допустим, имеется БД созданая по умолчанию в кодировке latin1 и содержащая данные в кодировке cp1251 - в этом случае используйте для получения бампа команду: mysqldump -u username -pPASSWORD DB_NAME --allow-keywords --create-options --complete-insert --default-character-set=latin1 >db_dump.sql
    Рзеберемся с параметрами mysqldump:

  • -u username -pPASSWORD DB_NAME Задаем имя пользовтеля, пароль и имя БД для дампа
  • --allow-keywords параметр позволяет создание столбцов, чьи имена совпадают со служебными словами MySQL.
  • --create-options добавляет в дамп некие специфиные для MySQL опции создания структур данных. Практической пользы от этого параметра я до сих пор не видел, но и помешать не должно ;)
  • --complete-insert использовать один оператор INSERT для одной строки данных. Повышает читабельность дампа. По умолчанию использется минимально-возможное количство операторов для таблицы путем применения оператора INSERT ..... VALUES ((...),...,(....))
  • --default-character-set=latin1 указываем что данные-то у нас в latin1 ;) Распространненая ошибка использование в таком случае --default-character-set=cp1251 - на выходе будет мусор. Кроме того, default-character-set уже можне быть прописан в my.cnf, поэтому задаем этот параметр явным образом.
  • НЕ РЕКОМЕНДУЕТСЯ параметр --disable-keys т.к. вас могу ожидать неожиданности в части нарушения ограничений уникальности. Подробнее см. ниже.
  • Просмотрите файл с дампом - убедитесь, что в файле нормальные данные в кодировке cp1251 а не мусор.

    Для этого либо скчайте дамп на локальную систему либо просмотрите его прямо на сервере. Если кодировка вашего терминала отличается от cp1251 используйте для перекодировки iconv либо screen либо другой инструмент которы позволит вам увидеть ваши данные в читаемом виде. Если вы не уверены в кодировки целостности ваших данных в дампе - далее лучше не продолжать до обретения полной уверенности.
  • Целый и проверенный дамп скопируйте в сторону. Далеко в сторону.
  • В файле дампа поправьте операторы CREATE DATABASE и/или CREATE TABLE для создания таблиц в правильной кодировке.

    Особого внимания требует поля которые используются в разного рода ключах - уникальных, первичных и т.п. Проблема в том, что в кодировке latin1 вторая половина содержащая в данном случае кириллические символы алфавитных символов не содержит. По этому, если некое поле, по которому был создан например primary key было создано в CHARSET latin1 и COLLATION latin1_genereal_ci (нечуствительный к регистру COLLATION ) и в него вставлялись данные в cp1251 то нарушений ограничения уникальности не возникало даже если вставлялись одни и те же кириллические строки, но в различных регистрах. В случае, если вы определите это поле как CHARSET cp1251 COLLATE cp1251_general_ci (явным обаразом либо, через DATABASE/TABLE DEFAULT HCARCTER SET) то вполне вероятна ситуаци нарушения уникальности.
  • Вот вобщем-то и все...

    Исправления по существу, а так же моего "особенностей" моего правописания приветсвуются. Раздел о использовании unicode БД для работы с несколькими языками желателен, но врядли у меня до него скоро дойдут руки. Обсудить данный FAQ можно на http://forum.linux.by

    Ссылки

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


    Ах, ну да... Copyright... ;)

    (C) Bogdan Rudas 2006 Условия распространения и модификации - GNU FDL - http://ru.wikipedia.org/wiki/GNU_FDL

    И этот вот последний раздел у нас - второстепенный и неизменяемый ;)
    Личные инструменты

    Powered by MediaWiki
    GNU Free Documentation License 1.2

    Rambler's Top100
    Новости | Статьи | Галерея | Форум | Чат | Файлы | HowTo | Ссылки | Поиск | О нас | Главная
    Материалы портала распространяются под GNU GPL. При использовании любых материалов портала ссылка на Linux.by обязательна.
    [ Техподдержка ] [ Hosted by DataHata | MyCloud.by ] [ Powered by phpBB® Forum Software © phpBB Group ]