Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

Статьи

Нюансы использования MySQL 5

СУБД MySQL чрезвычайно динамично развивающаяся база данных. Это вообще характерно для всех свободно распространяемых продуктов. С одной стороны это хорошо — нововведения внедряются очень быстро. С другой стороны, это немного раздражает консервативных пользователей. Вчера ещё эта функциональность работала, а сегодня она признана устаревшей и не рекомендуется или вообще исключена. Опять всё переучивай заново!

Тем не менее, воспользоваться нововведениями MySQL хочется, тем более они действительно захватывающие и очень полезны. Здесь мы рассмотрим подводные камни, которые могут ожидать пользователей MySQL версий 3.23.x, 4.0.x при переходе на старшие версии 4.1, 5.0 и 5.1.

Работа с русскими кодировками

Одной из главных сложностей, которая подстерегает на этом пути — это работа с кодировками. Поддержка локализации настолько развита в MySQL, что на сегодняшний день превосходит по гибкости и возможностям флагманы коммерческих СУБД — Oracle и MS SQL.

Кодировка — это соответствие символов их кодам (компьютеры не могут оперировать буквами 'A' и 'Б' — они оперируют их числовыми кодами). Кодировка обозначается ключевым словом CHARACTER SET, например, для того, чтобы сообщить, что информация в столбце таблицы имеет определённую кодировку следует поступить следующим образом

Листинг № 1. Оператор CREATE TABLE для создания таблицы test

CREATE TABLE test (
  dos_name TEXT CHARACTER SET cp866 COMMENT `Кодировка DOS`,
  win_name TEXT CHARACTER SET cp1251 COMMENT `Кодировка Windows`,
  koi8r_name TEXT CHARACTER SET koi8r COMMENT `Кодировка KOI8-R`,
  utf8_name TEXT CHARACTER SET utf8 COMMENT `Юникод`
);

Замечание

Ключевое слово COMMENT также является нововведением последних версий СУБД MySQL.

Как видно из листинга 1 таблица test состоит из четырех полей типа TEXT:

  • win_name — текст в кодировке cp866 (русская кодировка DOS);
  • win_name — текст в кодировке cp1251 (русская кодировка Windows);
  • koi8r_name — текст в кодировке KOI8-R (кодировка, распространенная в Интернете);
  • utf8_name — текст в кодировке UTF-8 (Юникод).

В полях таблицы test теперь можно хранить текст в соответствующих кодировках, и он будет сортироваться правильно. Если же в поле с кодировкой cp866 поместить текст в кодировке cp1251, то о правильно сортировке записей при помощи конструкции ORDER BY не может быть и речи.

Замечание

Названия всех доступных кодировок можно получить при помощи запроса SHOW CHARACTER SET.

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

Листинг № 2. Использование параметра CHARACTER SET

CREATE TABLE collection (
  coll_koi8r TINYTEXT CHARACTER SET koi8r,
  name TINYTEXT,
  description TINYTEXT
) CHARACTER SET cp1251;

В таблице collection из листинга 2 столбец coll_koi8r будет сортировать текст в предположении, что он создан при помощи кодировки KOI8-R, значения всех остальных столбцов (name и description) будут сортироваться в предположении, что они созданы в кодировке Windows-1251 (cp1251).

Однако просто установить соответствие символов их кодам не достаточно, нужно определить сортировку, чтобы последовательности строк на данном языке правильно сортировались. Одна кодировка может представлять несколько языков (например, в случае latin1 поддерживается несколько европейских языков). Т.е. компьютеру следует сообщить, что символ 'а' меньше символа 'б', а 'б' в свою очередь меньше символа 'в' и вообще справедливо соотношение

A = a <= B = b <= С = с <= ...

Такое соответствие задаётся при помощи сортировок, просмотреть все доступные сортировки можно при помощи оператора SHOW COLLATION. По умолчанию оператор выводит большое количество информации, поэтому уменьшим его при помощи ключевого слова LIKE, ограничив вывод только кодировкой cp1251, которая соответствует русской Windows-кодировке.

Листинг № 3. Сортировки, применяемые совместно с кодировкой latin1

mysql> SHOW COLLATION LIKE `cp1251%`;
+---------------------+---------+----+---------+----------+
| Collation           | Charset | Id | Default | Compiled |
+---------------------+---------+----+---------+----------+
| cp1251_bulgarian_ci | cp1251  | 14 |         |          |
| cp1251_ukrainian_ci | cp1251  | 23 |         |          |
| cp1251_bin          | cp1251  | 50 |         |          |
| cp1251_general_ci   | cp1251  | 51 | Yes     |          |
| cp1251_general_cs   | cp1251  | 52 |         |          |
+---------------------+---------+----+---------+----------+

Как видно из листинга 3, кодировка cp1251 имеет 5 сортировок, которые имеют следующие значения:

  • cp1251_bulgarian_ci — болгарский язык (регистронезависимый);
  • cp1251_ukrainian_ci — украинский язык (регистронезависимый);
  • cp1251_bin — бинарная сортировка, сравнение по ASCII-кодам символов;
  • cp1251_general_ci — многоязыковая сортировка (регистронезависимая);
  • cp1251_general_cs — многоязыковая сортировка (зависимая от регистра);

Замечание

При выборе сортировки следует помнить, что суффикс ci означает не зависимость от регистра, слова "Привет" и "ПРИВЕТ" будут считаться одним и тем же словом, суффикс cs — означает зависимость сортировки от регистра.

Последние две сортировки можно использовать для корректной работы с русским языком. В четвертом столбце результирующей таблицы оператора SHOW COLLATION, отметкой Yes указывается сортировка по умолчанию (листинг 3). Так если указывается только одна кодировка cp1251, без уточнения сортировки, то будет выбрана сортировка cp1251_general_ci. Это означает, что если в таблице (базе данных, столбце) планируется хранить русский текст в кодировке cp1251, то вполне можно использовать оператор CREATE TABLE, представленный в листинге 4.

Листинг № 4. Создание таблицы, для хранения русского текста в кодировке cp1251

CREATE TABLE catalogs (
  id_catalog INT(11) NOT NULL,
  name TINYTEXT NOT NULL
) ENGINE=MyISAM CHARACTER SET cp1251;

Однако, если в таблице планируется хранить текст на украинском языке, потребуется явно указать сортировку, как показано в листинге 5, так как в противном случае будет выставлена кодировка по умолчанию и все операции сортировки текста будут осуществляться не корректно.

Листинг № 5. Создание таблицы для хранения украинского текста в кодировке cp1251

CREATE TABLE catalogs (
  id_catalog INT(11) NOT NULL,
  name TINYTEXT NOT NULL
) ENGINE=MyISAM CHARACTER SET cp1251 
         COLLATION cp1251_ukrainian_ci;

При взаимодействии с СУБД MySQL из PHP следует также настраивать кодировку соединения, для чего используется оператор SET NAMES. Типичный скрипт соединения с СУБД MySQL из PHP может выглядеть так, как это представлено в листинге 6.

Листинг № 6. Соединение с СУБД MySQL из PHP

<?php
  
// Адерс сервера MySQL
  
$dblocation "localhost";
  
// Имя пользователя базы данных
  
$dbuser "root";
  
// и его пароль
  
$dbpasswd "";

  
$dbcnx = @mysql_connect($dblocation$dbuser$dbpasswd);
  if (!
$dbcnx
  {
    exit (
"<p>В настоящий момент сервер базы данных 
           не доступен, поэтому корректное отображение 
           страницы невозможно.</p>"
);
  }
  else
  {
    echo 
"<P>Соединение установлено.</P>";
  }
  @
mysql_query ("SET NAMES 'cp1251'"); 
?>

Если не использовать оператор SET NAMES 'cp1251', то русский текст будет подвергаться искажениям и превращаться в знаки вопроса ????????????????.

Изменение формата типа TIMESTAMP

Формат типа TIMESTAMP совпадает с DATETIME, но во внутреннем представлении дата хранится в виде секунд, прошедших с полуночи 1 января 1970 года.

Замечание

Время в формате TIMESTAMP хранится как число секунд, прошедшее с полуночи 1 января 1970 года. Такое исчисление принято в операционной системе UNIX, а дата 01.01.1970 часто называется "Началом эпохи UNIX" и считается днем рождения данной операционной системы.

Кроме того, поле TIMESTAMP может автоматически получать значения текущей даты и времени суток при создании новой записи и изменении уже существующей оператором UPDATE.

В версиях MySQL 4.0 и более ранних формат типа TIMESTAMP представлял собой число YYYYMMDDhhmmss. Начиная с версии 4.1, формат представления был изменен на YYYY-MM-DD hh:mm:ss.

Если в таблице несколько столбцов TIMESTAMP, при модификации записи текущее время будет записываться только в один из столбцов (по умолчанию первый). Можно также указать явно столбец, которому необходимо назначать текущую дату при создании новой записи или изменении старой. Для того чтобы поля столбца принимали текущую дату при создании новой записи, следует после определения столбца добавить запись DEFAULT CURRENT_TIMESTAMP. Если требуется, чтобы текущее время выставлялось при модификации уже существующей записи, при использовании оператора UPDATE следует добавить конструкцию ON UPDATE CURRENT_TIMESTAMP. Так, три оператора CREATE TABLE, представленные в листинге 7 абсолютно идентичны.

Листинг № 7. Задание временных параметров при создании таблицы

CREATE TABLE tbl(puttime TIMESTAMP);
CREATE TABLE tbl(puttime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE tbl(puttime TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                                   DEFAULT CURRENT_TIMESTAMP);

Для столбца TIMESTAMP можно указать только одно из ключевых слов: либо только DEFAULT CURRENT_TIMESTAMP, либо только ON UPDATE CURRENT_TIMESTAMP.

Листинг № 8. Задание текущего времени

CREATE TABLE tbl(
  puttime1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  puttime2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

В листинге 8 поле puttime1 получает текущее время при создании новой записи, а поле puttime2 при ее модификации.

Источник: http://softtime.ru