Глава 6. Справочник по языку MySQL

Содержание

6.1. Структура языка

6.1.1. Литералы: представление строк и чисел

6.1.2. Имена баз данных, таблиц, столбцов, индексы псевдонимы

6.1.3. Чувствительность имен к регистру

6.1.4. Переменные пользователя

6.1.5. Системные переменные

6.1.6. Синтаксис комментариев

6.1.7. ``Придирчив'' ли MySQL к зарезервированным словам?

6.2. Типы данных столбцов

6.2.1. Числовые типы данных

6.2.2. Типы данных даты и времени

6.2.3. Символьные типы данных

6.2.4. Выбор правильного типа данных в столбце

6.2.5. Использование типов столбцов из других баз данных

6.2.6. Требования к памяти для различных типов столбцов

6.3. Функции, используемые в операторах SELECT и WHERE

6.3.1. Операторы и функции общего назначения

6.3.2. Строковые функции

6.3.3. Числовые функции

6.3.4. Функции даты и времени

6.3.5. Функции приведения типов

6.3.6. Другие функции

6.3.7. Функции, используемые в операторах GROUP BY

6.4. Обработка данных: SELECT, INSERT, UPDATE, DELETE

6.4.1. Синтаксис оператора SELECT

6.4.2. Синтаксис оператора HANDLER

6.4.3. Синтаксис оператора INSERT

6.4.4. Синтаксис оператора INSERT DELAYED

6.4.5. Синтаксис оператора UPDATE

6.4.6. Синтаксис оператора DELETE

6.4.7. Синтаксис оператора TRUNCATE

6.4.8. Синтаксис оператора REPLACE

6.4.9. Синтаксис оператора LOAD DATA INFILE

6.4.10. Синтаксис оператора DO

6.5. Определение данных: CREATE, DROP, ALTER

6.5.1. Синтаксис оператора CREATE DATABASE

6.5.2. Синтаксис оператора DROP DATABASE

6.5.3. Синтаксис оператора CREATE TABLE

6.5.4. Синтаксис оператора ALTER TABLE

6.5.5. Синтаксис оператора RENAME TABLE

6.5.6. Синтаксис оператора DROP TABLE

6.5.7. Синтаксис оператора CREATE INDEX

6.5.8. Синтаксис оператора DROP INDEX

6.6. Основные команды пользовательских программ MySQL

6.6.1. Синтаксис команды USE

6.6.2. Синтаксис команды DESCRIBE (Получение информации о столбцах)

6.7. Команды управления транзакциями и блокировками в MySQL

6.7.1. Синтаксис команд BEGIN/COMMIT/ROLLBACK

6.7.2. Синтаксис команд LOCK TABLES/UNLOCK TABLES

6.7.3. Синтаксис команды SET TRANSACTION

6.8. Полнотекстовый поиск в MySQL

6.8.1. Ограничения для полнотекстового поиска

6.8.2. Тонкая настройка полнотекстового поиска в MySQL

6.8.3. Предстоящие доработки по полнотекстовому поиску

6.9. Кэш запросов в MySQL

6.9.1. Как работает кэширование запросов

6.9.2. Конфигурация кэша запросов

6.9.3. Параметры кэша запросов в запросе SELECT

6.9.4. Статус и поддержка кэша запросов

MySQL имеет очень сложный, но интуитивно понятный и легкий в изучении интерфейс, основанный на SQL (структурированный язык запросов). В данном разделе описываются различные команды, типы данных и функции этого языка, которые необходимо знать, чтобы использовать MySQL рационально и эффективно. Данная глава служит также справочником по всем функциональным возможностям, реализованным в MySQL. Читатель найдет в этой главе ссылки на различные источники информации, которые, возможно, будут полезными для ее эффективного использования.

6.1. Структура языка

6.1.1. Литералы: представление строк и чисел

6.1.2. Имена баз данных, таблиц, столбцов, индексы псевдонимы

6.1.3. Чувствительность имен к регистру

6.1.4. Переменные пользователя

6.1.5. Системные переменные

6.1.6. Синтаксис комментариев

6.1.7. ``Придирчив'' ли MySQL к зарезервированным словам?

6.1.1. Литералы: представление строк и чисел

6.1.1.1. Cтроки

6.1.1.2. Числа

6.1.1.3. Шестнадцатеричные величины

6.1.1.4. Значения NULL

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

6.1.1.1. Cтроки

Строка представляет собой последовательность символов, заключенных либо в одинарные кавычки (‘'’) - апострофы, либо в двойные кавычки (‘"’). При использовании диалекта ANSI SQL допустимы только одинарные кавычки. Например:

'a string'
"another string"

Внутри строки некоторые последовательности символов имеют специальное назначение. Каждая из этих последовательностей начинается обратным слешем (‘\’), известным как escape-символ или символ перехода. MySQL распознает следующие escape-последовательности:

• \0

  Символ 0 (NUL) в ASCII коде.

• \'

  Символ одиночной кавычки (‘'’).

• \"

  Символ двойной кавычки (‘"’).

• \b

  Возврат на один символ.

• \n

  Символ новой строки (перевода строки).

• \r

  Символ перевода каретки.

• \t

  Символ табуляции.

• \z

  Символ (Control-Z) таблицы ASCII(26). Данный символ можно закодировать, чтобы обойти проблему, заключающуюся в том, что под Windows ASCII(26) означает конец файла (проблемы возникают при использовании ASCII(26) в выражении mysql database < filename).

• \\

  Символ обратного слеша.

• \%

  Символ процентов ‘%’. Используется для поиска копий литерала ‘%’ в контекстах, где выражение ‘%’ в противном случае интерпретировалось бы как групповой символ (see Раздел 6.3.2.1, «Функции сравнения строк»).

• \'_'

  Символ подчеркивания ‘_’. Используется для поиска копий литерала ‘_’ в контекстах, где выражение ‘_’ в противном случае интерпретировалось бы как групповой символ (see Раздел 6.3.2.1, «Функции сравнения строк»).

Обратите внимание на то, что при использовании '\%' или '\_' в контекстах некоторых строк будут возвращаться значения строк '\%' и '\_', а не ‘%’ и ‘_’.

Существует несколько способов включить кавычки в строку:

• Одиночная кавычка (апостроф) ‘'’ внутри строки, заключенной в кавычки ‘'’, может быть записана как ''''.

• Двойная кавычка ‘"’ внутри строки, заключенной в двойные кавычки ‘"’, может быть записана как '""'.

• Можно предварить символ кавычки символом экранирования (‘\’).

• Для символа ‘'’ внутри строки, заключенной в двойные кавычки, не требуется специальной обработки; его также не требуется дублировать или предварять обратным слешем. Точно так же не требует специальной обработки двойная кавычка ‘"’ внутри строки, заключенной в одиночные кавычки ‘'’.

Ниже показаны возможные варианты применения кавычек и escape-символа на примерах выполнения команды SELECT:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "This\nIs\nFour\nlines";
+--------------------+
| This
Is
Four
lines |
+--------------------+

Если необходимо вставить в строку двоичные данные (такие как BLOB), следующие символы должны быть представлены как escape-последовательности:

• NUL

  ASCII 0. Необходимо представлять в виде '\0' (обратный слеш и символ ASCII ‘0’).

• \

  ASCII 92, обратный слеш. Представляется как '\\'.

• '

  ASCII 39, единичная кавычка. Представляется как '\''.

• "

  ASCII 34, двойная кавычка. Представляется как '\"'.

При написании программы на языке C для добавления символов экранирования в команде INSERT можно использовать функцию mysql_real_escape_string() из C API (see Раздел 8.4.2, «Обзор функций интерфейса C»). При программировании на Perl можно использовать метод quote из пакета DBI для превращения специальных символов в соответствующие escape-последовательности (see Раздел 8.2.2, «Интерфейс DBI»).

Не следует забывать, что указанное свойство escape-символа должно использоваться во всех строках, которые могут содержать любые специальные символы, перечисленные выше.

В качестве альтернативы многие интерфейсы прикладного программирования (API) для MySQL предоставляют определенную возможность использования символов-заменителей, что позволяет вносить специальные маркеры в строку запроса и затем связывать с ними значения данных при выдаче результатов запроса.

6.1.1.2. Числа

Целые числа представляются в виде последовательности цифр. Для чисел с плавающей точкой в качестве разделителя десятичных знаков используется символ ‘.’. Числа обоих типов могут предваряться символом ‘-’, обозначающим отрицательную величину.

Примеры допустимых целых чисел:

1221
0
-32

Примеры допустимых чисел с плавающей запятой:

294.42
-32032.6809e+10
148.00

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

6.1.1.3. Шестнадцатеричные величины

MySQL поддерживает шестнадцатеричные величины. В числовом контексте такое число обладает всеми свойствами целого числа с 64-разрядной точностью. В строковом контексте шестнадцатеричная величина представляет собой двоичную строку, в которой каждая пара шестнадцатеричных разрядов конвертируется в символ:

mysql> SELECT x'4D7953514C';
         -> MySQL
mysql> SELECT 0xa+0;
         -> 10
mysql> SELECT 0x5061756c;
         -> Paul

Синтаксис выражений вида x'hexstring' (новшество в версии 4.0) базируется на ANSI SQL, а для обозначений вида 0x используется синтаксис ODBC. Шестнадцатеричные строки часто применяются в ODBC для представления двоичных типов данных вида BLOB. Для конвертирования строки или числа в шестнадцатеричный вид можно применять функцию HEX().

6.1.1.4. Значения NULL

Значение NULL означает ``отсутствие данных''. Они является отличным от значения 0 для числовых типов данных или пустой строки для строковых типов (see Раздел A.5.3, «Проблемы со значением NULL»).

При использовании форматов импорта или экспорта текстовых файлов (LOAD DATA INFILE, SELECT ... INTO OUTFILE) NULL можно представить как \N (see Раздел 6.4.9, «Синтаксис оператора LOAD DATA INFILE»).

6.1.2. Имена баз данных, таблиц, столбцов, индексы псевдонимы

Для всех имен баз данных, таблиц, столбцов, индексов и псевдонимов в MySQL приняты одни и те же правила.

Следует отметить, что эти правила были изменены, начиная с версии MySQL 3.23.6, когда было разрешено брать в одиночные скобки ‘`’ идентификаторы (имена баз данных, таблиц и столбцов). Двойные скобки ‘"’ тоже допустимы - при работе в режиме ANSI SQL (see Раздел 1.9.2, «Запуск MySQL в режиме ANSI»).

Необходимо также учитывать, что не следует использовать символы ASCII(0), ASCII(255) или кавычки в самом идентификаторе.

Кроме того, если имя идентификатора относится к служебным словам или содержит специальные символы, необходимо всегда заключать его в обратные кавычки ` при использовании в выражениях:

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

See Раздел 6.1.7, «``Придирчив'' ли MySQL к зарезервированным словам?».

В предыдущих версиях MySQL (до 3.23.6) для имен существовали следующие правила:

• Имя может состоять из буквенно-цифровых символов установленного в данное время алфавита и символов ‘_’ and ‘$’. Тип кодировки по умолчанию - ISO-8859-1 Latin1, он может быть изменен указанием иного типа в аргументе параметра --default-character-set mysqld (see Раздел 4.6.1, «Набор символов, применяющийся для записи данных и сортировки»).

• Имя может начинаться с любого допустимого символа, в частности, с цифры (в этом состоит отличие от правил, принятых во многих других базах данных). Однако имя не может состоять только из цифр.

• Не допускается использование в именах символа ‘.’, так как он применяется для расширения формата имени (посредством чего можно ссылаться на столбцы - см. в этом же разделе ниже).

Не рекомендуется использовать имена, подобные 1e, так как выражение вида 1e+1 является неоднозначным. Оно может интерпретироваться и как выражение 1e + 1, и как число 1e+1.

В MySQL разрешается делать ссылки на столбец, используя любую из следующих форм:

Нет необходимости указывать префикс tbl_name или db_name.tbl_name в ссылке на столбец в каком-либо утверждении, если эта ссылка не будет неоднозначной. Например, предположим, что каждая из таблиц t1 и t2 содержит столбец c, по которому производится выборка командой SELECT, использующей обе таблицы - и t1, и t2. В этом случае имя столбца c является неоднозначным, так как оно не уникально для таблиц, указанных в команде, поэтому необходимо уточнить, какая именно таблица имеется в виду, конкретизировав - t1.c или t2.c. Аналогично, при выборке данных из таблицы t в базе данных db1 и из таблицы t в базе данных db2 необходимо ссылаться на столбцы в этих таблицах как на db1.t.col_name и db2.t.col_name.

Выражение .tbl_name означает таблицу tbl_name в текущей базе данных. Данный синтаксис принят для совместимости с ODBC, так как некоторые программы ODBC ставят в начале имен таблиц в качестве префикса символ ‘.’.

6.1.3. Чувствительность имен к регистру

В MySQL имена баз данных и таблиц соответствуют директориям и файлам внутри директорий. Следовательно, чувствительность к регистру операционной системы, под которой работает MySQL, определяет чувствительность к регистру имен баз данных и таблиц. Это означает, что имена баз данных и таблиц нечувствительны к регистру под Windows, а под большинством версий Unix проявляют чувствительность к регистру. Одно большое исключение здесь это Mac OS X, когда файловая система по умолчанию HFS+ используется. Однако Mac OS X также поддерживает тома UFS, которые чувствительны к регистру под Mac OS X также как и на Unix. See Раздел 1.9.3, «Расширения MySQL к ANSI SQL92».

Примечание: хотя имена баз данных и таблиц нечувствительны к регистру под Windows, не следует ссылаться на конкретную базу данных или таблицу, используя различные регистры символов внутри одного и того же запроса. Приведенный ниже запрос не будет выполнен, поскольку в нем одна и та же таблица указана и как my_table, и как MY_TABLE:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Имена и псевдонимы столбцов нечувствительны к регистру во всех случаях.

Псевдонимы таблиц обладают чувствительностью к регистру. Приведенный ниже запрос не будет выполнен, поскольку он ссылается на псевдоним и как на a, и как на A:

mysql> SELECT col_name FROM tbl_name AS a
    -> WHERE a.col_name = 1 OR A.col_name = 2;

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

Одним из путей устранения этой проблемы является запуск демона mysqld с параметром -O lower_case_table_names=1. По умолчанию этот параметр имеет значение 1 для Windows и 0 для Unix.

Если значение параметра lower_case_table_names равно 1, MySQL при сохранении и поиске будет преобразовывать все имена таблиц к нижнему регистру. С версии 4.0.2 это также касается и имен баз данных. Обратите внимание на то, что при изменении этого параметра перед запуском mysqld необходимо прежде всего преобразовать имена всех старых таблиц к нижнему регистру.

При переносе MyISAM-файлов с Windows на диск в Unix в некоторых случаях будет полезна утилита mysql_fix_extensions для приведения в соответствие регистров расширений файлов в каждой указанной директории базы данных (нижний регистр .frm, верхний регистр .MYI и .MYD). Утилиту mysql_fix_extensions можно найти в подкаталоге scripts.

6.1.4. Переменные пользователя

Для конкретного процесса пользователь может определить локальные переменные, которые в MySQL обозначаются как @variablename. Имя локальной переменной может состоять из буквенно-цифровых символов установленного в данное время алфавита и символов ‘_’, ‘$’, and ‘.’. Тип кодировки по умолчанию - ISO-8859-1 Latin1, он может быть изменен указанием иного типа в аргументе параметра --default-character-set mysqld (see Раздел 4.6.1, «Набор символов, применяющийся для записи данных и сортировки»).

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

Локальную переменную можно объявить, используя синтаксис команды SET:

SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].

Можно также определить значение переменной иным способом, без команды SET. Однако в этом случае в качестве оператора присвоения более предпочтительно использовать оператор ':=', чем оператор ‘=’, так как последний зарезервирован для сравнения выражений, не связанных с установкой переменных:

mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

Введенные пользователем переменные могут применяться только в составе выражений и там, где выражения допустимы. Заметим, что в область их применения в данное время не включается контекст, в котором явно требуется число, например, условие LIMIT в команде SELECT или выражение IGNORE number LINES в команде LOAD DATA.

Примечание: в команде SELECT каждое выражение оценивается только при отправлении клиенту. Это означает, что в условиях HAVING, GROUP BY, or ORDER BY не следует ссылаться на выражение, содержащее переменные, которые введены в части SELECT этой команды. Например, следующая команда НЕ будет выполняться так, как ожидалось:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;

Причина в том, что переменная @aa не будет содержать значения текущей строки, в то время как значение id в предыдущем выражении является строкой.

Действует правило никогда не создавать и не использовать одну и ту же переменную в одном и том же выражении SQL.

6.1.5. Системные переменные

Начиная с MySQL 4.0.3 мы предоставляем лучший доступ к большинству системных переменных и переменных, относящихся к соединению. Можно менять теперь большую часть переменных без необходимости останавливать сервер.

Есть два типа системных переменных: специфичные для потока (или для соединения; мы их в дальнейшем называем потоковыми переменными для краткости), которые уникальны для данного соединения и глобальные перемененные, которые предназначаются для управления глобальными событиями. Глобальные переменные также используются для того, чтобы установить значения по умолчанию для соответствующих потоковых переменных для новых соединений.

Когда mysqld запускается, все глобальные переменные инициализируются из командной строки и файлов опций. Вы можете изменять значения с помощью SET GLOBAL. Когда новый поток создается, потоковые переменные инициализируются из глобальных и они не меняются даже если вы даете новую команду SET GLOBAL.

Для установки глобальной переменной, используйте один из таких синтаксисов: (Здесь используется sort_buffer_size в качестве примера)

SET GLOBAL sort_buffer_size=value;
SET @@global.sort_buffer_size=value;

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

SET SESSION sort_buffer_size=value;
SET @@session.sort_buffer_size=value;
SET sort_buffer_size=value;

Если вы не указываете режим, то тогда подразумевается SESSION. See Раздел 5.5.6, «Синтаксис команды SET».

LOCAL - синоним для SESSION.

Для получения значения глобальной переменной используйте одну из этих команд:

SELECT @@global.sort_buffer_size;
SHOW GLOBAL VARIABLES like 'sort_buffer_size';

Для получения значения потоковой переменной используйте одну из этих команд:

SELECT @@session.sort_buffer_size;
SHOW SESSION VARIABLES like 'sort_buffer_size';

Когда вы запрашиваете значение переменной с помощью синтаксиса @@variable_name и не укзываете GLOBAL или SESSION, то тогда MySQL вернет потоковое значение этой переменное, если таковое существует. Если нет, то MySQL вернет глобальное значение.

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

Далее идет полный список всех переменных которые вы можете изменять и значения которых можете получать, а также информация о том, можете ли вы использовать SESSION или GLOBAL с ними.

Переменные, помеченные как число могут иметь числовое значение. Переменные, помеченные как булевое могут быть установлены в 0, 1, ON или OFF. Переменные типа enum должны в общем случае быть установлены в одно из возможных значений для переменной, но также могут быть установлены в значение числа, соответствующего значению выбора enum. Первый элемент списка enum - номер 0.

Вот описание некоторых переменных:

Описания других переменных можно найти в описании переменных запуска mysql, в описании команды SHOW VARIABLES и в разделе SET. See Раздел 4.1.1, «Параметры командной строки mysqld». See Раздел 4.5.6.4, «SHOW VARIABLES». See Раздел 5.5.6, «Синтаксис команды SET».

6.1.6. Синтаксис комментариев

Сервер MySQL поддерживает следующие способы задания комментариев: с помощью символа ‘#’, за которым следует текст комментария до конца строки; с помощью двух символов --, за которыми идет текст комментария до конца строки; и (для многострочных комментариев) с помощью символов /* (начало комментария) и */ (конец комментария):

mysql> SELECT 1+1;     # Этот комментарий продолжается до конца строки
mysql> SELECT 1+1;     -- Этот комментарий продолжается до конца строки
mysql> SELECT 1 /* Это комментарий в строке */ + 1;
mysql> SELECT 1+
/*
Это многострочный
комментарий
*/
1;

Обратите внимание: при использовании для комментирования способа с -- (двойное тире) требуется наличие хотя бы одного пробела после второго тире!

Хотя сервер ``понимает'' все описанные выше варианты комментирования, существует ряд ограничений на способ синтаксического анализа комментариев вида /* ... */ клиентом mysql:

• Символы одинарной и двойной кавычек, даже внутри комментария, считаются началом заключенной в кавычки строки. Если внутри комментария не встречается вторая такая же кавычка, синтаксический анализатор не считает комментарий законченным. При работе с mysql в интерактивном режиме эта ошибка проявится в том, что окно запроса изменит свое состояние с mysql> на '> или ">.

• Точка с запятой используется для обозначения окончания данной SQL-команды и что-либо, следующее за этим символом, указывает на начало следующего выражения.

Эти ограничения относятся как к интерактивному режиму работы mysql (из командной строки), так и к вызову команд из файла, читаемого с ввода командой mysql < some-file.

MySQL поддерживает принятый в ANSI SQL способ комментирования с помощью двойного тире '--' только в том случае, если после второго тире следует пробел (see Раздел 1.9.4.7, «Символы `--' как начало комментария»).

6.1.7. ``Придирчив'' ли MySQL к зарезервированным словам?

Это общая проблема, возникающая при попытке создать таблицу с именами столбцов, использующих принятые в MySQL названия типов данных или функций, такие как TIMESTAMP или GROUP. Иногда это возможно (например, ABS является разрешенным именем для столбца), но не допускается пробел между именем функции и сразу же следующей за ним скобкой ‘(’ при использовании имен функций, совпадающих с именами столбцов.

Следующие слова являются зарезервированными в MySQL. Большинство из них не допускаются в ANSI SQL92 как имена столбцов и/или таблиц (например GROUP). Некоторые зарезервированы для нужд MySQL и используются (в настоящее время) синтаксическим анализатором yacc:

Следующие слова являются новыми зарезервированными словами в MySQL 4.0:

Следующие символы (из приведенной выше таблицы таблицы) не разрешены в ANSI SQL, но допускаются в MySQL как имена столбцов/таблиц. Это объясняется тем, что некоторые из этих имен являются словами естественного языка и уже использованы многими потребителями.

• ACTION

• BIT

• DATE

• ENUM

• NO

• TEXT

• TIME

• TIMESTAMP

6.2. Типы данных столбцов

6.2.1. Числовые типы данных

6.2.2. Типы данных даты и времени

6.2.3. Символьные типы данных

6.2.4. Выбор правильного типа данных в столбце

6.2.5. Использование типов столбцов из других баз данных

6.2.6. Требования к памяти для различных типов столбцов

MySQL поддерживает несколько типов столбцов, которые можно разделить на три категории: числовые типы данных, типы данных для хранения даты и времени и символьные (строковые) типы данных. В данном разделе вначале дается обзор всех возможных типов и приводятся требования по хранению для каждого типа столбца, затем свойства типов описываются более подробно по каждой категории. Мы намеренно сделали обзор кратким, поскольку более детальные описания требуют дополнительной информации о конкретных типах столбцов, например, о допустимых форматах представления величин.

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

• M

  Указывает максимальный размер вывода. Максимально допустимый размер вывода составляет 255 символов.

• D

  Употребляется для типов данных с плавающей точкой и указывает количество разрядов, следующих за десятичной точкой. Максимально возможная величина составляет 30 разрядов, но не может быть больше, чем M-2.

Квадратные скобки (‘[’ и ‘]’) указывают для типа данных группы необязательных признаков.

Заметьте, что если для столбца указать параметр ZEROFILL, то MySQL будет автоматически добавлять в этот столбец атрибут UNSIGNED.

Предупреждение: следует помнить, что при выполнении вычитания между числовыми величинами, одна из которых относится к типу UNSIGNED, результат будет беззнаковым! See Раздел 6.3.5, «Функции приведения типов».

• TINYINT[(M)] [UNSIGNED] [ZEROFILL]

  Очень малое целое число. Диапазон со знаком от -128 до 127. Диапазон без знака от 0 до 255.

• BIT, BOOL

  Являются синонимами для TINYINT(1).

• SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

  Малое целое число. Диапазон со знаком от -32768 до 32767. Диапазон без знака от 0 до 65535.

• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

  Целое число среднего размера. Диапазон со знаком от -8388608 до 8388607. Диапазон без знака от 0 до 16777215.

• INT[(M)] [UNSIGNED] [ZEROFILL]

  Целое число нормального размера. Диапазон со знаком от -2147483648 до 2147483647. Диапазон без знака от 0 до 4294967295.

• INTEGER[(M)] [UNSIGNED] [ZEROFILL]

  Синоним для INT.

• BIGINT[(M)] [UNSIGNED] [ZEROFILL]

  Большое целое число. Диапазон со знаком от -9223372036854775808 до 9223372036854775807. Диапазон без знака от 0 до 18446744073709551615. Для столбцов типа BIGINT необходимо учитывать некоторые особенности:

  • Все арифметические операции выполняются с использованием значений BIGINT или DOUBLE со знаком, так что не следует использовать беззнаковые целые числа больше чем 9223372036854775807 (63 бита), кроме операций, выполняемых логическими функциями. В противном случае несколько последних разрядов результата могут оказаться ошибочными из-за ошибок округления при преобразовании BIGINT в DOUBLE. MySQL 4.0 может обрабатывать данные типа BIGINT в следующих случаях:

    • Использование целых чисел для хранения больших беззнаковых величин в столбце с типом BIGINT.

    • В случаях MIN(big_int_column) и MAX(big_int_column).

    • При использовании операторов (‘+’, ‘-’, ‘*’ и т.д.), когда оба операнда являются целыми числами.

  • Точное значение целого числа всегда можно хранить в столбце с типом BIGINT в виде строки. В этом случае MySQL выполнит преобразование строки в число без промежуточного преобразования.

  • Если оба аргумента являются целочисленными величинами, то при выполнении над ними операций ‘-’, ‘+’, и ‘*’ будут использоваться правила BIGINT-арифметики. Это означает, что при умножении двух больших целых чисел (или результатов вычислений функций, возвращающих целые числа) результат операции может оказаться непредсказуемым, если он превосходит значение 9223372036854775807.

• FLOAT(точность) [UNSIGNED] [ZEROFILL]

  Число с плавающей точкой. Атрибут точности может иметь значение <=24 для числа с плавающей точкой обычной (одинарной) точности и между 25 и 53 - для числа с плавающей точкой удвоенной точности. Эти типы данных сходны с типами FLOAT и DOUBLE, описанными ниже. FLOAT(X) относится к тому же интервалу, что и соответствующие типы FLOAT и DOUBLE, но диапазон значений и количество десятичных знаков не определены.

  В версии MySQL 3.23 это истинная величина числа с плавающей точкой. В более ранних версиях MySQL тип данных FLOAT(точность) всегда имеет два десятичных знака.

  Следует отметить, что использование типа данных FLOAT может привести к неожиданным проблемам, так как все вычисления в MySQL выполняются с удвоенной точностью. See Раздел A.5.6, «Решение проблем с отсутствием строк, удовлетворяющих условиям поиска». Данный синтаксис обеспечивает совместимость с ODBC.

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

  Малое число с плавающей точкой обычной точности. Допустимые значения: от -3,402823466E+38 до -1,175494351E-38, 0, и от 1,175494351E-38 до 3,402823466E+38. Если указан атрибут UNSIGNED, отрицательные значения недопустимы. Атрибут M указывает количество выводимых пользователю знаков, а атрибут D - количество разрядов, следующих за десятичной точкой. Обозначение FLOAT без указания аргументов или запись вида FLOAT(X), где X <=24 справедливы для числа с плавающей точкой обычной точности.

• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

  Число с плавающей точкой удвоенной точности нормального размера. Допустимые значения: от -1,7976931348623157E+308 до -2,2250738585072014E-308, 0, и от 2,2250738585072014E-308 до 1,7976931348623157E+308. Если указан атрибут UNSIGNED, отрицательные значения недопустимы. Атрибут M указывает количество выводимых пользователю знаков, а атрибут D - количество разрядов, следующих за десятичной точкой. Обозначение DOUBLE без указания аргументов или запись вида FLOAT(X), где 25 <= X <= 53 справедливы для числа с плавающей точкой двойной точности.

• DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

  Данные обозначения являются синонимами для DOUBLE.

• DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

  ``Неупакованное'' число с плавающей точкой. Ведет себя подобно столбцу CHAR, содержащему цифровое значение. Термин ``неупакованное'' означает, что число хранится в виде строки и при этом для каждого десятичного знака используется один символ. Разделительный знак десятичных разрядов, а также знак ‘-’ для отрицательных чисел не учитываются в M (но место для них зарезервировано). Если атрибут D равен 0, величины будут представлены без десятичного знака, т.е. без дробной части. Максимальный интервал значений типа DECIMAL тот же, что и для типа DOUBLE, но действительный интервал для конкретного столбца DECIMAL может быть ограничен выбором значений атрибутов M и D. Если указан атрибут UNSIGNED, отрицательные значения недопустимы. Если атрибут D не указан, его значение по умолчанию равно 0. Если не указан M, его значение по умолчанию равно 10. В более ранних, чем MySQL 3.23, версиях аргумент M должен содержать в себе место для знака числа и десятичного знака.

• DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]

  Данные обозначения являются синонимами для DECIMAL.

• DATE

  Дата. Поддерживается интервал от '1000-01-01' до '9999-12-31'. MySQL выводит значения DATE в формате 'YYYY-MM-DD', но можно установить значения в столбец DATE, используя как строки, так и числа. See Раздел 6.2.2.2, «Типы данных DATETIME, DATE и TIMESTAMP».

• DATETIME

  Комбинация даты и времени. Поддерживается интервал от '1000-01-01 00:00:00' до '9999-12-31 23:59:59'. MySQL выводит значения DATETIME в формате 'YYYY-MM-DD HH:MM:SS', но можно устанавливать значения в столбце DATETIME, используя как строки, так и числа. See Раздел 6.2.2.2, «Типы данных DATETIME, DATE и TIMESTAMP».

• TIMESTAMP[(M)]

  Временная метка. Интервал от '1970-01-01 00:00:00' до некоторого значения времени в 2037 году. MySQL выводит значения TIMESTAMP в форматах YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD или YYMMDD в зависимости от значений M: 14 (или отсутствующее), 12, 8, или 6; но можно также устанавливать значения в столбце TIMESTAMP, используя как строки, так и числа. Столбец TIMESTAMP полезен для записи даты и времени при выполнении операций INSERT или UPDATE, так как при этом автоматически вносятся значения даты и времени самой последней операции, если эти величины не введены программой. Можно также устанавливать текущее значение даты и времени, задавая значение NULL. See Раздел 6.2.2, «Типы данных даты и времени». Аргумент M влияет только на способ вывода столбца TIMESTAMP; для хранения его значений всегда используется 4 байта. Следует учитывать, что столбцы TIMESTAMP(M), где M равно 8 или 14, представляют собой числа, в то время, как столбцы TIMESTAMP(M) с иным значением аргумента M являются строками. Это убеждает, что можно надежно сделать дамп и восстановить таблицу с этими типами столбцов! See Раздел 6.2.2.2, «Типы данных DATETIME, DATE и TIMESTAMP».

• TIME

  Время. Интервал от '-838:59:59' до '838:59:59'. MySQL выводит значения TIME в формате 'HH:MM:SS', но можно устанавливать значения в столбце TIME, используя как строки, так и числа. See Раздел 6.2.2.3, «Тип данных TIME».

• YEAR[(2|4)]

  Год в двухзначном или четырехзначном форматах (по умолчанию формат четырехзначный). Допустимы следующие значения: с 1901 по 2155, 0000 для четырехзначного формата года и 1970-2069 при использовании двухзначного формата (70-69). MySQL выводит значения YEAR в формате YYYY, но можно задавать значения в столбце YEAR, используя как строки, так и числа (тип данных YEAR недоступен в версиях, предшествующих MySQL 3.22). See Раздел 6.2.2.4, «Тип данных YEAR».

• [NATIONAL] CHAR(M) [BINARY]

  Строка фиксированной длины, при хранении всегда дополняется пробелами в конце строки до заданного размера. Диапазон аргумента M составляет от 0 до 255 символов (от 1 до 255 в версиях, предшествующих MySQL 3.23). Концевые пробелы удаляются при выводе значения. Если не задан атрибут чувствительности к регистру BINARY, то величины CHAR сортируются и сравниваются как независимые от регистра в соответствии с установленным по умолчанию алфавитом.

  Атрибут NATIONAL CHAR (или его эквивалентная краткая форма NCHAR) представляет собой принятый в ANSI SQL способ указания, что в столбце CHAR должен использоваться установленный по умолчанию набор символов (CHARACTER). В MySQL это принято по умолчанию. CHAR является сокращением от CHARACTER. MySQL позволяет создавать столбец типа CHAR(0).

  В основном это полезно, если необходимо обеспечить совместимость с некоторыми старыми приложениями, которые зависят от наличия столбца, но реально эту величину не используют. Кроме того, такая возможность может очень пригодиться в случае, если необходим столбец, который может содержать только 2 значения, а именно CHAR(0) (т.е. столбец, который не определен как NOT NULL, занимает только один бит и принимает только 2 значения: NULL или ""). See Раздел 6.2.3.1, «Типы данных CHAR и VARCHAR».

• CHAR

  Это синоним для CHAR(1).

• [NATIONAL] VARCHAR(M) [BINARY]

  Строка переменной длины. Примечание: концевые пробелы удаляются при сохранении значения (в этом заключается отличие от спецификации ANSI SQL). Диапазон аргумента M составляет от 0 до 255 символов (от 1 до 255 в версиях, предшествующих MySQL Version 4.0.2). Если не задан атрибут чувствительности к регистру BINARY, то величины VARCHAR сортируются и сравниваются как независимые от регистра. See Раздел 6.5.3.1, «Молчаливые изменения определений столбцов». Термин VARCHAR является сокращением от CHARACTER VARYING. See Раздел 6.2.3.1, «Типы данных CHAR и VARCHAR».

• TINYBLOB, TINYTEXT

  Столбец типа BLOB или TEXT с максимальной длиной 255 (2^8 - 1) символов. See Раздел 6.5.3.1, «Молчаливые изменения определений столбцов». See Раздел 6.2.3.2, «Типы данных BLOB и TEXT».

• BLOB, TEXT

  Столбец типа BLOB или TEXT с максимальной длиной 65535 (2^16 - 1) символов. See Раздел 6.5.3.1, «Молчаливые изменения определений столбцов». See Раздел 6.2.3.2, «Типы данных BLOB и TEXT».

• MEDIUMBLOB, MEDIUMTEXT

  Столбец типа BLOB или TEXT с максимальной длиной 16777215 (2^24 - 1) символов. See Раздел 6.5.3.1, «Молчаливые изменения определений столбцов». See Раздел 6.2.3.2, «Типы данных BLOB и TEXT».

• LONGBLOB, LONGTEXT

  Столбец типа BLOB или TEXT с максимальной длиной 4294967295 (2^32 - 1) символов. See Раздел 6.5.3.1, «Молчаливые изменения определений столбцов». Следует учитывать, что в настоящее время протокол передачи данных сервер/клиент и таблицы MyISAM имеют ограничение 16 Мб на передаваемый пакет/строку таблицы, поэтому пока нельзя использовать этот тип данных в его полном диапазоне. See Раздел 6.2.3.2, «Типы данных BLOB и TEXT».

• ENUM('значение1','значение2',...)

  Перечисление. Перечисляемый тип данных. Объект строки может иметь только одно значение, выбранное из заданного списка величин 'значение1', 'значение2', ..., NULL или специальная величина ошибки "". Список ENUM может содержать максимум 65535 различных величин. See Раздел 6.2.3.3, «Тип перечисления ENUM ».

• SET('значение1','значение2',...)

  Набор. Объект строки может иметь ноль или более значений, каждое из которых должно быть выбрано из заданного списка величин 'значение1', 'значение2', ... Список SET может содержать максимум 64 элемента. See Раздел 6.2.3.4, «Тип множества SET ».

6.2.1. Числовые типы данных

MySQL поддерживает все числовые типы данных языка SQL92 по стандартам ANSI/ISO. Они включают в себя типы точных числовых данных (NUMERIC, DECIMAL, INTEGER и SMALLINT) и типы приближенных числовых данных (FLOAT, REAL и DOUBLE PRECISION). Ключевое слово INT является синонимом для INTEGER, а ключевое слово DEC - синонимом для DECIMAL.

Типы данных NUMERIC и DECIMAL реализованы в MySQL как один и тот же тип - это разрешается стандартом SQL92. Они используются для величин, для которых важно сохранить повышенную точность, например для денежных данных. Требуемая точность данных и масштаб могут задаваться (и обычно задаются) при объявлении столбца данных одного из этих типов, например:

    salary DECIMAL(5,2)

В этом примере - 5 (точность) представляет собой общее количество значащих десятичных знаков, с которыми будет храниться данная величина, а цифра 2 (масштаб) задает количество десятичных знаков после запятой. Следовательно, в этом случае интервал величин, которые могут храниться в столбце salary, составляет от -99,99 до 99,99 (в действительности для данного столбца MySQL обеспечивает возможность хранения чисел вплоть до 999,99, поскольку можно не хранить знак для положительных чисел).

В SQL92 по стандарту ANSI/ISO выражение DECIMAL(p) эквивалентно DECIMAL(p,0). Аналогично, выражение DECIMAL также эквивалентно DECIMAL(p,0), при этом предполагается, что величина p определяется конкретной реализацией. В настоящее время MySQL не поддерживает ни одну из рассматриваемых двух различных форм типов данных DECIMAL/NUMERIC. В общем случае это не является серьезной проблемой, так как основные преимущества данных типов состоят в возможности явно управлять как точностью, так и масштабом представления данных.

Величины типов DECIMAL и NUMERIC хранятся как строки, а не как двоичные числа с плавающей точкой, чтобы сохранить точность представления этих величин в десятичном виде. При этом используется по одному символу строки для каждого разряда хранимой величины, для десятичного знака (если масштаб > 0) и для знака ‘-’ (для отрицательных чисел). Если параметр масштаба равен 0, то величины DECIMAL и NUMERIC не содержат десятичного знака или дробной части.

Максимальный интервал величин DECIMAL и NUMERIC тот же, что и для типа DOUBLE, но реальный интервал может быть ограничен выбором значений параметров точности или масштаба для данного столбца с типом данных DECIMAL или NUMERIC. Если конкретному столбцу присваивается значение, имеющее большее количество разрядов после десятичного знака, чем разрешено параметром масштаба, то данное значение округляется до количества разрядов, разрешенного масштаба. Если столбцу с типом DECIMAL или NUMERIC присваивается значение, выходящее за границы интервала, заданного значениями точности и масштаба (или принятого по умолчанию), то MySQL сохранит данную величину со значением соответствующей граничной точки данного интервала.

В качестве расширения стандарта ANSI/ISO SQL92 MySQL также поддерживает числовые типы представления данных TINYINT, MEDIUMINT и BIGINT, кратко описанные в таблице выше. Еще одно расширение указанного стандарта, поддерживаемое MySQL, позволяет при необходимости указывать количество показываемых пользователю символов целого числа в круглых скобках, следующих за базовым ключевым словом данного типа (например INT(4)). Это необязательное указание количества выводимых символов используется для дополнения слева выводимых значений, которые содержат символов меньше, чем заданная ширина столбца, однако не накладывает ограничений ни на диапазон величин, которые могут храниться в столбце, ни на количество разрядов, которые могут выводиться для величин, у которых количество символов превосходит ширину данного столбца. Если дополнительно указан необязательный атрибут ZEROFILL, свободные позиции по умолчанию заполняются нолями. Например, для столбца, объявленного как INT(5) ZEROFILL, величина 4 извлекается как 0000