[ новости /+++ | форум | теги | ]

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа

5 Типы столбцов

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

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

M

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

D

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

Квадратные скобки ([ и ]) указывают части спецификаторов типа, которые являются факультативными.

Обратите внимание, что, если Вы определяете для столбца ZEROFILL, MySQL автоматически добавит к столбцу атрибут UNSIGNED.

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

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

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.
• Вы можете всегда сохранять точное целочисленное значение в столбце BIGINT, сохраняя его как строку, в этом случае не будет никакого промежуточного двойного представления.
• В MySQL 4.0 Вы можете использовать целые числа, чтобы сохранить большие значения без знака в строках BIGINT.
• -, + и * используют арифметику BIGINT, когда оба параметра представляют собой значения типа INTEGER. Это означает, что, если Вы умножаете два больших целых числа (или результаты функций, которые возвращают целые числа), Вы можете получить непредвиденные эффекты, когда результат больше, чем 9223372036854775807.

FLOAT(precision) [ZEROFILL]

Число с плавающей запятой. Не может быть без знака. Точность (precision) может быть в пределах <=24, для числа с одиночной точностью, или между 25 и 53, для числа двойной точности. Эти типы подобны FLOAT и DOUBLE, описанным ниже. FLOAT(X) имеет тот же самый диапазон, что и соответствующие типы FLOAT и DOUBLE, но размер отображения и число десятичных чисел неопределенны. В MySQL Version 3.23 это и есть истинное значение с плавающей запятой. В старых версиях MySQL FLOAT(precision) всегда имеет 2 десятичных числа. Обратите внимание, что использование типа FLOAT может создавать Вам некоторые непредвиденные проблемы, поскольку все вычисления в MySQL всегда выполнены с двойной точностью. Этот синтаксис предусмотрен для ODBC-совместимости.

FLOAT[(M,D)] [ZEROFILL]

Маленькое (с одиночной точностью) число с плавающей запятой. Не может быть без знака. Допустимые значения от -3.402823466E+38 до -1.175494351E-38, 0 и от 1.175494351E-38 до 3.402823466E+38. M задает размер отображения, а D число десятичных чисел. Соответствует FLOAT(X), где X <= 24.

DOUBLE[(M,D)] [ZEROFILL]

Число нормального размера (двойная точность) с плавающей запятой. Не может быть без знака. Допустимые значения от -1.7976931348623157E+308 до -2.2250738585072014E-308, 0 и от 2.2250738585072014E-308 до 1.7976931348623157E+308. M задает размер отображения, а D число десятичных чисел. Соответствует DOUBLE без параметра или FLOAT(X), где 25 <= X <= 53.

DOUBLE PRECISION[(M,D)] [ZEROFILL]

REAL[(M,D)] [ZEROFILL]

Синонимы для DOUBLE.

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

Распакованное число с плавающей запятой. Не может быть без знака. Ведет себя подобно столбцу CHAR: "распакованный" означает, что число сохранено как строка, используя один символ для каждой цифры значения. Десятичная отметка и, для отрицательных чисел, знак - не рассчитана в M (но пробел для них зарезервирован). Если D=0, значения не будут иметь никакой десятичной отметки или дробной части. Максимальный диапазон значений DECIMAL аналогичен DOUBLE, но фактический диапазон для данного столбца DECIMAL может быть задан через M и D. Если D не задано, оно будет установлено в 0. Если же не задано M, оно считается равным 10. Обратите внимание, что в MySQL Version 3.22 аргумент M должен включать пробел, необходимый для знака и десятичной отметки.

NUMERIC(M,D) [ZEROFILL]

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

DATE

Дата. Поддерживаемый диапазон: от 1000-01-01 до 9999-12-31. MySQL отображает значения DATE в формате YYYY-MM-DD, но позволяет Вам назначать значения столбцам DATE используя строки или числа.

DATETIME

Комбинация даты и времени. Поддерживаемый диапазон: от 1000-01-01 00:00:00 до 9999-12-31 23:59:59. MySQL отображает значения типа DATETIME в формате YYYY-MM-DD HH:MM:SS, но позволяет Вам назначать значения столбцам DATETIME, используя строки или числа.

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. TIMESTAMP всегда сохраняется в 4 байтах. Параметр M воздействует только на то, как отображается столбец TIMESTAMP. Обратите внимание, что столбцы TIMESTAMP(X) при X, равном 8 или 14, являются числами, в то время как другие столбцы TIMESTAMP(X) представляют собой строки! Это только должно гарантировать, что можно надежно сбросить таблицу в дамп, а потом восстанавливать ее с этими типами!

TIME

Время. Поддерживаемый диапазон: от -838:59:59 до 838:59:59. MySQL отображает значения типа TIME в формате HH:MM:SS, но позволяет Вам задавать значения столбцам TIME, используя строки или числа.

YEAR[(2|4)]

Год с двумя или четыремя цифрами (по умолчанию задано 4). Допустимые значения: от 1901 до 2155, 0000 в формате года с 4 цифрами, и 1970-2069, если Вы используете формат с 2 цифрами (70-69). MySQL отображают значения YEAR в формате YYYY, но позволяет Вам указывать значения столбцам типа YEAR, используя строки или числа. Впервые тип YEAR появился в MySQL Version 3.22.

[NATIONAL] CHAR(M) [BINARY]

Строка фиксированной длины, которая всегда дополняется справа пробелами до определенной длины. Диапазон M от 1 до 255 символов. Конечные пробелы будут удалены, когда значение извлекается из таблицы. Переменные CHAR сортируются и сравниваются без учета регистра согласно заданному по умолчанию набору символов, если не задано ключевое слово BINARY. NATIONAL CHAR (короткая форма NCHAR) представляет собой взятый из ANSI SQL способ определить, что столбец CHAR должен использовать заданный набор символов CHARACTER. Это значение по умолчанию в MySQL. CHAR является сокращением для CHARACTER. MySQL позволяет Вам создавать столбец типа CHAR(0). Это главным образом полезно, когда Вы должны обеспечить совместимость с некоторыми старыми прикладными программами, которые зависят от существования столбца, но фактически не использует значение. Это также очень удобно, когда Вы нуждаетесь в столбце, который может брать только 2 значения: CHAR(0), который не определен как NOT NULL, займет всего лишь один бит и может принимать только 2 значения: NULL или "".

[NATIONAL] VARCHAR(M) [BINARY]

Строка переменной длины. A variable-length string. ОБРАТИТЕ ВНИМАНИЕ: Конечные пробелы будут автоматически удалены, когда значение сохранено (это отличается от спецификаций ANSI SQL). Диапазон M от 1 до 255 символов. Значения типа VARCHAR сортируются и сравниваются без учета регистра согласно заданному по умолчанию набору символов, если не задано ключевое слово BINARY. VARCHAR является сокращением для CHARACTER VARYING.

TINYBLOB

TINYTEXT

Столбцы BLOB или TEXT имеют максимальную длину в 255 (2^8-1) символов.

BLOB

TEXT

Столбцы BLOB или TEXT имеют максимальную длину в 65535 (2^16-1) символов.

MEDIUMBLOB

MEDIUMTEXT

Столбцы MEDIUMBLOB или MEDIUMTEXT имеют максимальную длину в 16777215 (2^24-1) символов.

LONGBLOB

LONGTEXT

Столбцы LONGBLOB или LONGTEXT имеют максимальную длину в 4294967295 (2^32-1) символов. Обратите внимание, что поскольку протокол клиент-сервер и таблицы MyISAM имеет в настоящее время ограничение в 16M на пакет связи/строку таблицы, Вы не можете использовать весь диапазон этого типа.

ENUM('value1','value2',...)

Перечисление. Строковый объект, который может иметь только одно значение, выбранное из списка значений value1, value2, ..., NULL или специальное значение ошибки "". ENUM может иметь максимум 65535 разных значений.

SET('value1','value2',...)

Множество. Строковый объект, который может иметь ноль или больше значений, каждое из которых должно быть выбрано из списка значений value1, value2, .... SET может иметь максимум 64 члена.

5.1 Числовые типы

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

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

   salary DECIMAL(9,2)

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

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

Значения типов DECIMAL и NUMERIC сохранены как строки, в не как двоичные числа с плавающей запятой, чтобы сохранить десятичную точность значений. Один символ используется для каждой цифры значения, десятичной отметки (если scale > 0) и знака - (для отрицательных чисел). Если scale равен 0, значения DECIMAL и NUMERIC не содержат никакой десятичной отметки или дробной части.

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

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

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

Тип FLOAT используется, чтобы представить приблизительные числовые типы данных. Стандарт ANSI/ISO SQL92 позволяет факультативную спецификацию точности (но не диапазон образца) в битах после ключевого слова FLOAT в круглых скобках. MySQL-реализация также поддерживает эту факультативную спецификацию точности. Когда ключевое слово FLOAT используется для типа столбца без спецификации точности, MySQL применяет четыре байта, чтобы сохранить значения. Различный синтаксис также поддержан, с двумя числами, заданными в круглых скобках после ключевого слова FLOAT. С этой опцией первое число продолжает представлять требования к памяти для значения в байтах, а второе определяет число цифр, которые будут сохранены и отображаться после десятичной отметки (как с DECIMAL и NUMERIC). Когда MySQL нужно сохранить число для такого столбца с большим количеством цифр после десятичной отметки, чем определено для столбца, значение будет автоматически округлено, чтобы устранить лишние цифры.

Типы REAL и DOUBLE PRECISION не принимают спецификации точности. В качестве дополнения к стандарту ANSI/ISO SQL92 MySQL распознает DOUBLE как синоним для DOUBLE PRECISION. В отличие от требования стандарта, чтобы точность для REAL была меньшие, чем используемая для DOUBLE PRECISION, MySQL реализует то и другое как 8-байтные double-precision floating-point значения (при работе не в ANSI mode). Для максимальной мобильности код, требующий хранение приблизительных числовых значений данных должен использовать FLOAT или DOUBLE PRECISION без спецификации точности или количества чисел после десятичной точки.

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

Например, диапазон столбца INT от -2147483648 до 2147483647. Если Вы пробуете вставлять -9999999999 в столбец типа INT, значение будет усечено к нижей границе диапазона и сохранено -2147483648. Точно так же, если Вы пробуете вставлять 9999999999, вместо этого будет сохранено 2147483647.

Если столбец INT определен как UNSIGNED, размер диапазона столбца тот же самый, но произойдет сдвиг значений на 0 и 4294967295. Если Вы пробуете сохранять -9999999999 и 9999999999, значения, сохраненные в столбце, станут соответственно 0 и 4294967296.

Преобразования, которые происходят из-за усечения, сообщаются как ``warnings'' для инструкций ALTER TABLE, LOAD DATA INFILE, UPDATE и многострочного варианта INSERT.

5.2 Типы Date и Time

Типы даты и времени: DATETIME, DATE, TIMESTAMP, TIME и YEAR. Каждый из них имеет диапазон допустимых значений, также как и некий ноль, который используется, когда Вы определяете, действительно, запрещенное значение. Обратите внимание, что MySQL позволяет Вам сохранять некоторые недопустимые значения даты, например, 1999-11-31. Причина этого в том, что обрабатывать проверку даты ответственность прикладной программы, а не сервера SQL. Чтобы сделать проверку даты быстрой, MySQL проверяет только то, что месяц находится в диапазоне 0-12, и день находится в диапазоне 0-31. Вышеупомянутые диапазоны определены так потому, что MySQL позволяет Вам сохранять в столбцах DATE или DATETIME даты, где день или месяц нулевой. Это чрезвычайно полезно для прикладных программ, которые должны сохранить дату рождения, для которой Вы не знаете точную дату. В этом случае Вы просто сохраняете дату подобно 1999-00-00 или 1999-01-00. Вы, конечно, не можете получать правильное значение из функций, подобных DATE_SUB() или DATE_ADD, для таких неправильных дат.

Имеются некоторые общие вещи, которые стоит иметь в виду при работе с типами даты и времени:

• MySQL возвращает значения для данного типа в стандартном формате, но пытается интерпретировать ряд форматов для значений, которые Вы обеспечиваете (например, когда Вы определяете значение, которое будет назначено переменной или сравниваться). Однако, поддержаны только форматы, описанные в следующих разделах. Ожидается, что Вы обеспечите допустимые значения, и непредсказуемые результаты могут быть получены, если Вы используете значения в других форматах.
• Хотя MySQL пробует интерпретировать значения в нескольких форматах, он всегда ожидает, что часть, определяющая год, стоит на крайнем слева месте. Даты должны быть даны в порядке год-месяц-день (например, 98-09-04).
• MySQL автоматически преобразует значения даты и времени в числа, если значение используется в числовом контексте, и наоборот.
• Когда MySQL сталкивается со значением, которое находится вне диапазона или иначе запрещено для типа, он устанавливает значение в ноль для этого типа. Исключительная ситуация: вышедшие за границу диапазона значения TIME урезаются до соответствующей границы. Таблица ниже показывает нулевые значения каждого типа:

  Тип столбца	Нулевое значение
  DATETIME	'0000-00-00 00:00:00'
  DATE	'0000-00-00'
  TIMESTAMP	00000000000000 (длина зависит от размера отображения)
  TIME	'00:00:00'
  YEAR	0000

• Нулевое значение специально, но Вы можете сохранять или обращаться к ним, явно используя значения, показанные в таблице. Вы можете также делать это, применяя значения '0' или 0.
• Нулевые значения даты и времени, используемые через MyODBC, будут преобразованы автоматически в NULL в MyODBC Version 2.50.12 и выше потому, что ODBC не может обрабатывать такие значения.

5.2.1 Проблема Y2K и типы Date

MySQL непосредственно Y2K-безопасен, но вводимые значения таковыми не явдяются. Любой ввод, содержащий значения года с 2 цифрами, неоднозначен потому, что столетие неизвестно. Такие значения должны интерпретироваться в форму с 4 цифрами потому, что MySQL сохраняет годы, внутренне всегда используя четыре цифры.

Для типов DATETIME, DATE, TIMESTAMP и YEAR MySQL интерпретирует даты с неоднозначным годом так:

• Значения года в диапазоне 00-69 будут преобразованы в 2000-2069.
• Значения года в диапазоне 70-99 будут преобразованы в 1970-1999.

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

ORDER BY сортирует значения типов YEAR/DATE/DATETIME с 2 цифрами правильно.

Обратите внимание также, что некоторые функции, подобно MIN() и MAX(), преобразуют TIMESTAMP/DATE в число. Это означает, что timestamp с годом с 2 цифрами не будет работать правильно с этими функциями. Исправление в этом случае должно преобразовать TIMESTAMP/DATE к формату года с 4 цифрами или использовать нечто вроде MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS)).

5.2.2 Типы DATETIME, DATE и TIMESTAMP

Типы DATETIME, DATE и TIMESTAMP связаны. Этот раздел описывает их характеристики и чем они отличаются.

Тип DATETIME используется, когда Вы нуждаетесь в значениях, которые содержат информацию о дате и времени. MySQL получает и отображает значения DATETIME в формате YYYY-MM-DD HH:MM:SS. Поддерживаемый диапазон от 1000-01-01 00:00:00 до 9999-12-31 23:59:59.

Тип DATE используется, когда Вы нуждаетесь только в значении даты, без части времени. MySQL получает и отображает значения DATE в формате YYYY-MM-DD. Поддерживаемый диапазон от 1000-01-01 до 9999-12-31.

Тип столбца TIMESTAMP обеспечивает тип, который Вы можете использовать, чтобы автоматически отметить операции INSERT или UPDATE текущей датой и временем. Если Вы имеете много столбцов типа TIMESTAMP, только первый модифицируется автоматически.

Автоматическое модифицирование первого столбца TIMESTAMP происходит при любом из условий:

• Столбец не определен явно в инструкции INSERT или LOAD DATA INFILE.
• Столбец не определен явно в инструкции UPDATE и некотором другом значении изменений столбца. Обратите внимание, что UPDATE, который устанавливает столбец к значению, которое тот уже имеет, не будет заставлять столбец типа TIMESTAMP модифицироваться потому, что, если Вы устанавливаете столбец к текущему значению, MySQL игнорирует модификацию для эффективности.
• Вы явно устанавливаете столбец типа TIMESTAMP в NULL.

Второй и последующие столбцы типа TIMESTAMP также могут быть установлены к текущей дате и времени. Только установите столбец в NULL или в NOW().

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

• Позвольте MySQL устанавливать столбец, когда строка создана. Это инициализирует переменную и установит ее в текущую дату и время.
• Когда Вы выполняете последующие модификации к другим столбцам в строке, установите столбец TIMESTAMP явно в текущее значение.

Значения TIMESTAMP имеют точность в одну секунду. Значения отображаются как числа.

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

Все столбцы типа TIMESTAMP имеют тот же самый размер памяти, независимо от размера отображения. Наиболее распространенные размеры: 6, 8, 12 и 14. Вы можете определять произвольный размер при создании таблицы, но значения 0 или больше, чем 14, будут установлены в 14. Нечетные размеры в диапазоне от 1 до 13 будут приведены к следующему четному числу.

Вы можете определять значения DATETIME, DATE и TIMESTAMP, использующие любой из общего набора форматов:

• Строка в формате YYYY-MM-DD HH:MM:SS или YY-MM-DD HH:MM:SS. Облегченный синтаксис позволяется: любой символ пунктуации может использоваться как разделитель между частями даты или частями времени. Например, 98-12-31 11:30:45, 98.12.31 11+30+45, 98/12/31 11*30*45 и 98@12@31 11^30^45 равны.
• Строка в формате YYYY-MM-DD или YY-MM-DD. Облегченный синтаксис позволяется и здесь: например, строки 98-12-31, 98.12.31, 98/12/31 и 98@12@31 являются эквивалентными.
• Строка без разделителей в формате YYYYMMDDHHMMSS или YYMMDDHHMMSS, при условии, что строка имеет смысл как дата. Например, 19970523091528 и 970523091528 интерпретируются как 1997-05-23 09:15:28, но 971122129015 запрещен (это имеет неправильную минутную часть) и становится 0000-00-00 00:00:00.
• Строка без разделителей в формате YYYYMMDD или YYMMDD, при условии, что строка имеет смысл как дата. Например, 19970523 и 970523 интерпретируются как 1997-05-23, но 971332 запрещен (это имеет неправильную часть месяца) и становится 0000-00-00.
• Число в формате YYYYMMDDHHMMSS или YYMMDDHHMMSS, при условии, что число имеет смысл как дата. Например, 19830905132800 и 830905132800 интерпретируются как 1983-09-05 13:28:00.
• Число в формате YYYYMMDD или YYMMDD при условии, что число имеет смысл как дата. Например, 19830905 и 830905 интерпретируются как 1983-09-05.
• Результат функции, которая возвращает значение которое является приемлемым в контексте DATETIME, DATE или TIMESTAMP, типа NOW() или CURRENT_DATE.

Запрещенные значения DATETIME, DATE или TIMESTAMP преобразованы в нулевое значение соответствующего типа (0000-00-00 00:00:00, 0000-00-00 или 00000000000000).

Для значений, определенных как строки, которые включают разделители частей даты, не требуется определять две цифры для значений месяца или дня, которые являются меньше, чем 10. 1979-6-9 эквивалентно 1979-06-09. Точно так же для значений, определенных как строки, которые включают разделители части времени, необязательно определять две цифры для значений часа, минут или секунды, которые меньше, чем 10. 1979-10-30 1:2:3 то же самое, что и 1979-10-30 01:02:03.

Значения, определенные как числа, должны быть длиной в 6, 8, 12 или 14 цифр. Если число длиной в 8 или 14 цифр, считается, что оно в формате YYYYMMDD или YYYYMMDDHHMMSS, и что год задан первыми 4 цифрами. Если число длиной в 6 или 12 цифр, считается, что оно в формате YYMMDD или YYMMDDHHMMSS, и что год задан первыми 2 цифрами. Числа, которые не соответствуют этим длинам, дополняются нулями до самой близкой длины.

Значения, определенные как неразграниченные строки интерпретируются, используя их длину как она есть. Это означает, что Вы не должны использовать строки короче шести символов. Например, если Вы определяете 9903, думая, что представите март 1999 года, Вы найдете, что MySQL вставляет ноль для даты в Вашу таблицу. Это потому, что значения года и месяца 99 и 03, но часть дня пропущено, так что значение не является допустимой датой.

Столбцы TIMESTAMP сохраняют допустимые значения, используя полную точность, с которой значение было определено, независимо от размера отображения. Это имеет несколько значений:

• Всегда определяйте год, месяц и день, даже если Ваш тип столбца TIMESTAMP(4) или TIMESTAMP(2). Иначе, значение не будет допустимой датой, и будет сохранен 0.
• Если Вы используете ALTER TABLE, чтобы расширить узкий столбец TIMESTAMP, будет отображаться та информация, которая раньше была скрыта.
• Точно так же сужение столбца TIMESTAMP не уничтожит информацию, просто меньшее количество информации будет показываться, когда значения столбца отображаются.
• Хотя значения TIMESTAMP сохранены в полной точности, единственная функция, которая функционирует непосредственно на основном сохраненном значении, это UNIX_TIMESTAMP(). Другие функции имеют дело с форматируемым значением. Вы не можете использовать функции типа HOUR() или SECOND(), если релевантная часть значения TIMESTAMP не включена в форматируемое значение. Например, часть HH столбца TIMESTAMP не отображается, если размер отображения меньше 10.

Вы можете до некоторой степени назначать значения одного типа даты объекту иного типа даты. Однако, может иметься некоторое изменение значения или потери информации:

• Если Вы назначаете значение DATE объекту DATETIME или TIMESTAMP, часть времени возникающего в результате значения будет установлена в 00:00:00 потому, что значение DATE не содержит информацию для времени.
• Если Вы назначаете значение DATETIME или TIMESTAMP объекту a DATE, часть времени возникающего в результате значения удалена потому, что тип DATE не сохраняет информацию о времени.
• Не забудьте, что, хотя DATETIME, DATE и TIMESTAMP могут быть определены, используя тот же самый набор форматов, они имеют разный диапазон значений. Например, TIMESTAMP не могут быть ранее, чем 1970, или позже, чем 2037. Это означает, что дата типа 1968-01-01, которая вполне нормальна для DATETIME или DATE, недопустима для переменной типа TIMESTAMP и превратится в 0.

Знайте некоторые ловушки при определении значений даты:

• Ослабленный формат, позволенный для значений, определенных как строки, может обманывать. Например, значение типа 10:11:12 могло бы напоминать значение времени из-за разделителя :, но если оно используется в контексте даты будет интерпретироваться как год 2010-11-12. Значение 10:45:15 будет преобразовано в 0000-00-00 потому, что 45 недопустимый месяц.
• Значения года, определенные как две цифры, неоднозначны потому, что столетие неизвестно. MySQL интерпретирует их так:
  • Значения года в диапазоне 00-69 преобразованы в 2000-2069.
  • Значения года в диапазоне 70-99 преобразованы в 1970-1999.

5.2.3 Тип TIME

MySQL получает и отображает значения TIME в формате HH:MM:SS (или HHH:MM:SS для больших значений часов). Значения TIME могут располагаться в диапазоне от -838:59:59 до 838:59:59. Причина того, что часть часов может быть настолько большой в том, что тип TIME может использоваться не только, чтобы представить время дня (которое должно быть меньше, чем 24 часа), но также и прошедшее время или интервал времени между двумя событиями (который может быть намного больше, чем 24 часа).

Вы можете определять значения TIME разными способами:

• Как строку в формате D HH:MM:SS.fraction. Обратите внимание, что MySQL все же не сохраняет дробную часть для столбца времени. Можно также использовать один из следующих ослабленных синтаксисов: HH:MM:SS.fraction, HH:MM:SS, HH:MM, D HH:MM:SS, D HH:MM, D HH или SS. Здесь под D понимаются дни в пределах 0-33.
• Как строку без разделителей в формате HHMMSS, при условии, что это имеет смысл как время. Например, 101112 допустимо и будет воспринято как 10:11:12, но 109712 из-за неправильной минутной части превратится в 00:00:00.
• Как число в фромате HHMMSS, при условии, что это имеет смысл как время. Например, 101112 допустимо и будет воспринято как 10:11:12. Следующие альтернативные форматы также будут поняты: SS, MMSS, HHMMSS, HHMMSS.fraction. Обратите внимание, что MySQL все же не сохраняет дробную часть.
• Как результат функции, которая возвращает значение, которое является приемлемым в контексте TIME, например, CURRENT_TIME.

Для значений TIME, определенных как строки, которые включают разделитель частей времени, необязательно задавать две цифры для часов, минут или секунд, которые меньше 10. Указание 8:3:2 аналогично 08:03:02.

Будьте внимательны относительно назначения коротких значений TIME. Без точки с запятой MySQL интерпретирует значения, используя предположение, что крайние справа цифры представляют секунды. MySQL интерпретирует значения TIME как прошедшее время, а не время дня. Например, Вы могли задавать 1112 и 1112 как 11:12:00, но MySQL поймет это как 00:11:12. Точно так же 12 и 12 интерпретируются как 00:00:12. Значения TIME с точкой с запятой вместо этого, всегда обрабатываются как время дня. Так 11:12 будет означать 11:12:00, но не 00:11:12.

Значения, которые находятся вне диапазона TIME, но допустимы во всем остальном, будут усечены до соответствующей границы диапазона. Например, -850:00:00 и 850:00:00 превратятся в -838:59:59 и 838:59:59.

Недопустимые значения TIME превратятся в 00:00:00. Обратите внимание, что так как 00:00:00 представляет собой допустимое значение TIME, нет никакого способа понять по сохраненному в таблице 00:00:00, было ли первоначальное значение определено как 00:00:00 или нет.

5.2.4 Тип YEAR

Тип YEAR представляет собой тип с 1 байтом, используемый для представления лет.

MySQL получает и отображает значения типа YEAR в формате YYYY. Диапазон: от 1901 до 2155.

Вы можете определять значения YEAR в форматах:

• Как строка с четырьмя цифрами в диапазоне от 1901 до 2155.
• Как число с четырьмя цифрами в диапазоне от 1901 до 2155.
• Как строка с двумя цифрами в диапазоне от 00 до 99. Значения в диапазонах от 00 до 69 и от 70 до 99 будут преобразованы в значения YEAR в диапазонах от 2000 до 2069 и от 1970 до 1999.
• Как число с двумя цифрами в диапазоне от 1 до 99. Значения в диапазонах от 1 до 69 и от 70 до 99 будут преобразованы в значения YEAR в диапазонах от 2001 до 2069 и от 1970 до 1999. Обратите внимание, что диапазон для чисел с двумя цифрами немного иной, чем для строк с двумя цифрами потому, что Вы не можете определять ноль непосредственно как число. Вы должны определить это как строку 0 (или 00), или это будет интерпретироваться как 0000.
• Как результат функции, возвращающей значение, которое является приемлемым в контексте YEAR, например, NOW().

Запрещенные значения YEAR будут преобразованы в 0000.

5.3 Строковые типы

Строковыми считаются типы: CHAR, VARCHAR, BLOB, TEXT, ENUM и SET. Этот раздел описывает как работу типов, их требования к памяти и как использовать их в Ваших запросах. Сразу следует отметить, что к строковым причислены типы, которые в разных языках программирования таковыми не являются, например, ENUM и SET.

5.3.1 Типы CHAR и VARCHAR

Типы CHAR и VARCHAR подобны, но отличаются способом их хранения и получения.

Длина столбца CHAR фиксирована и задается, когда Вы создаете таблицу. Длина может быть любым значением между 1 и 255. В MySQL Version 3.23 длина CHAR может быть от 0 до 255. Когда значения CHAR сохраняются в таблице, они дополняются справа пробелами до нужной длины. Когда значения CHAR получены, конечные пробелы будут автоматически удалены.

Значения в столбцах VARCHAR представляют собой строки переменной длины. Вы можете объявлять, что столбец VARCHAR будет любой длины между 1 и 255, точно как для столбцов типа CHAR. Однако, в отличие от CHAR, значения VARCHAR сохранены, используя ровно столько символов, сколько надо, плюс один байт, чтобы записать длину (аналог типа String в языке Pascal). Значения не дополняются, вместо этого конечные пробелы будут удалены, когда значения сохранены в таблице. Это удаление пробелов отличается от спецификации ANSI SQL, там они остаются.

Если Вы назначаете значение столбцу CHAR или VARCHAR, которое превышает максимальную длину столбца, значение будет усечено, чтобы поместиться в столбец.

Таблица ниже иллюстрирует различия между двумя типами столбцов, показывая результат сохранения различных значений в CHAR(4) и VARCHAR(4):

Значения, полученные из столбцов CHAR(4) и VARCHAR(4), будут те же самые в каждом случае потому, что конечные пробелы удалены из столбцов CHAR после поиска.

Значения в столбцах CHAR и VARCHAR сортируются и сравниваются без учета регистра, если атрибут BINARY не был определен, когда таблица была создана. Атрибут BINARY означает, что значения столбца сортируются и сравниваются с учетом регистра согласно порядку символов серверной (не клиентской!) машины MySQL. BINARY не воздействует на то, как столбец будет сохранен или получен.

Атрибут BINARY липкий. Это означает, что, если столбец, отмеченный как BINARY, используется в выражении, все выражение сравнивается как значение BINARY.

MySQL может тихо изменять тип столбца CHAR или VARCHAR при создании таблицы. Подробности в разделе "7.3.1 Тихие изменения спецификации столбца".

5.3.2 Типы BLOB и TEXT

BLOB представляет собой двоичный большой объект, который может хранить переменное количество данных. Есть четыре типа BLOB'ов: TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB, отличающиеся только по максимальной длине значений, которые они могут хранить.

Четыре типа TEXT: TINYTEXT, TEXT, MEDIUMTEXT и LONGTEXT соответствуют четырем типам BLOB и имеют те же самые максимальные длины и требования к памяти. Единственное различие между типами BLOB и TEXT в том, что сортировка выполняется с учетом регистра для значений типа BLOB и без учета регистра для TEXT.

Если Вы назначаете значение столбцу BLOB или TEXT, которое превышает максимальную длину для типа столбца, значение будет усечено, чтобы поместиться.

В большинстве случаев Вы можете расценивать столбец TEXT как VARCHAR, который может быть столь большим, как Вы хотите. Точно так же Вы можете расценивать столбец BLOB как VARCHAR BINARY. Различия в том, что:

• Вы можете иметь индексы на столбцах BLOB и TEXT, начиная с MySQL Version 3.23.2 и выше. Старые версии MySQL не поддерживали это.
• Не имеется никакого удаления пробелов для BLOB и TEXT, когда значения сохранены, в противоположность VARCHAR.
• Столбцы типов BLOB и TEXT не могут иметь значений DEFAULT.

MyODBC определяет значения BLOB как LONGVARBINARY, а значения типа TEXT как LONGVARCHAR.

Поскольку значения BLOB и TEXT могут быть чрезвычайно длинными, Вы можете столкнуться с ограничениями при использовании:

• Если Вы хотите использовать GROUP BY или ORDER BY на столбце типа BLOB или TEXT, Вы должны преобразовать значение столбца в объект фиксированной длины. Стандартный способ сделать это: функция SUBSTRING, например:

  mysql> select comment from tbl_name,substring(comment,20) as substr
                    ORDER BY substr;
  

  Если Вы не сделаете это, только первые max_sort_length байт столбца используются при сортировке. Значение по умолчанию для max_sort_length равно 1024, это значение может быть изменено, используя опцию -O при запуске сервера mysqld. Вы можете группировать на выражении, включающем значения BLOB или TEXT, определяя позицию столбца или используя псевдоним:

  mysql> select id,substring(blob_col,1,100) from tbl_name
             GROUP BY 2;
  mysql> select id,substring(blob_col,1,100) as b from tbl_name
             GROUP BY b;
  

• Максимальный размер объекта BLOB или TEXT определен типом, но самое большое значение, которое Вы можете фактически передать между клиентом и сервером, зависит от количества доступной памяти и размера буферов связей. Вы можете изменять размер буфера сообщения, но Вы должны сделать это на клиенте и на сервере.

Обратите внимание, что каждое значение BLOB или TEXT внутренне представляется отдельно распределенным объектом. Это отличие от всех других типов столбца, для которых память распределена один раз на столбец, когда таблица открывается.

5.3.3 Тип ENUM

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

Значение может также быть пустой строкой ("") или NULL при некоторых обстоятельствах:

• Если Вы вставляете недопустимое значение в ENUM (то есть, строку, которая не указана в перечне допустимых значений), взамен будет вставлена пустая строка как специальное значение ошибки. Эта строка может отличаться от нормальной пустой строки тем фактом, что эта имеет числовое значение 0. Подробнее относительно этого позже...
• Если ENUM объявлен как NULL, то NULL также допустимое значение для столбца, и значением по умолчанию как раз и будет именно NULL. Если ENUM объявлен как NOT NULL, значением по умолчанию будет первый элемент из списка дозволенных значений.

Каждое значение перечисления имеет индекс:

• Значения из списка допустимых элементов в спецификации столбца пронумерованы, начиная с 1.
• Индексное значение пустого значения ошибки 0. Это означает, что Вы можете использовать следующую инструкцию SELECT, чтобы найти строки, в которых были установлены недопустимые для ENUM значения:

  mysql> SELECT * FROM tbl_name WHERE enum_col=0;
  

• Индексом значения NULL является само NULL.

Например, столбец, определенный как ENUM("one","two","three"), может иметь любое из значений, показанных ниже. Индекс каждого значения также показывается:

Перечисление может иметь максимум 65535 элементов.

Регистр неважен, когда Вы назначаете значения столбцу ENUM. Однако, значения, полученные из столбца позже имеют регистр с соответствием значений, которые использовались, чтобы определить допустимые значения при создании данной таблицы.

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

mysql> SELECT enum_col+0 FROM tbl_name;

Если Вы сохраняете число в ENUM, он обрабатывается как индекс, и сохраненное значение представляет собой член перечисления с этим индексом. Однако, это не будет работать с вызовом LOAD DATA, который обрабатывает весь свой ввод как строки.

Значения ENUM сортируются согласно порядку, в котором члены перечисления были указаны в спецификации столбца. Другими словами, значения ENUM сортируются согласно их индексным числам. Например, "a" окажется перед "b" для ENUM("a","b"), но "b" опередит "a" для ENUM("b", "a"). Пустые строки окажутся перед непустыми строками, а значения NULL перед всеми другими значениями перечисления.

Если Вы хотите получать все возможные значения для столбца ENUM, Вы должны использовать SHOW COLUMNS FROM table_name LIKE enum_column_name и анализировать определение ENUM во втором столбце вывода.

5.3.4 Тип SET

SET представляет собой строковый объект, который может иметь ноль или большее количество значений, каждое из которых должно быть выбрано из списка допустимых значений, которые перечислены явно в спецификации столбца при создании таблицы. Значения столбца SET, которые состоят из многих значений набора, определены членами, отделяемыми запятыми (,). Следствие этого: значения членов SET не могут самостоятельно содержать запятые.

Например, столбец, определенный как SET("one","two") NOT NULL, может иметь любое из этих значений:

""
"one"
"two"
"one,two"

SET может иметь максимум 64 различных членов.

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

mysql> SELECT set_col+0 FROM tbl_name;

Если число сохранено в столбце SET, биты, которые установлены в двоичном представлении числа, определяют члены набора в значении столбца. Предположим, что столбец определен как SET("a","b","c","d"). Члены имеют следующие разрядные значения:

Если Вы назначаете число 9 этому столбцу (в двоичной системе это будет 1001), то первый и четвертый члены значения SET (то есть, "a" и "d") будут выбраны, и возникающим в результате значением будет "a,d".

Для значения, содержащего больше, чем один элемент SET, не имеет значения порядок элементов, в котором они перечислены, когда Вы вставляете значение. Также не важно, сколько раз данный элемент перечислен в значении. Когда значение потом будет получено из таблицы, каждый элемент в нем появится лишь однажды, причем элементы будут перечислены согласно порядку, в котором они были определены при создании таблицы. Например, если столбец определен как SET("a","b","c","d"), то "a,d", "d,a" и "d,a,a,d,d" будут появляться как "a,d" при получении данных из таблицы.

Значения SET сортируются в цифровой форме. NULL расположится перед другими значениями.

Обычно, Вы выполняете SELECT на столбце SET, применяя оператор LIKE или функцию FIND_IN_SET():

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

Но следующее будет также работать:

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

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

Если Вы хотите получать все возможные значения для столбца SET, Вы должны использовать: SHOW COLUMNS FROM table_name LIKE set_column_name и анализировать второй столбец определения.

5.4 Выбор правильного типа для столбца

Для наиболее эффективного использования памяти, попробуйте использовать наиболее подходящий тип во всех случаях. Например, если целочисленный столбец будет использоваться для значений в диапазоне между 1 и 99999, лучшим является тип MEDIUMINT UNSIGNED.

Точное представление денежных величин обычно является проблемой. В MySQL Вы должны использовать тип DECIMAL. Это сохранено как строка, так что никакая потеря точности не должна произойти. Если точность не слишком важна, тип DOUBLE также может быть достаточен.

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

5.5 Использование типов столбцов из других СУБД

Чтобы использовать код, написанный для реализаций SQL от других авторов, MySQL отображает типы столбцов как показано в таблице ниже. Эти отображения делают проще перенос определения таблиц:

Отображение типа столбца происходит при создании таблицы. Если Вы создаете таблицу с типами, используемыми другими авторами, а затем выдаете команду DESCRIBE tbl_name, MySQL сообщает структуру таблицы, использующую эквивалентные типы MySQL.

5.6 Требования столбцов к памяти

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

5.2.6.1 Требования к памяти для чисел

5.2.6.2 Требования к памяти для даты и времени

5.2.6.3 Требования к памяти для строк

Типы VARCHAR, BLOB и TEXT представляют собой типы переменной длины, для которых требования к памяти зависят от фактической длины значения столбца (представлена как L в предшествующей таблице). Например, столбец VARCHAR(10) может хранить строку с максимальной длиной в 10 символов. Фактическая требуемая память равна длине строки (L) плюс 1 байт, чтобы записать длину строки. Для строки abcd, L равно 4, а требования к памяти равны 5 байтам.

Типы BLOB и TEXT требуют 1, 2, 3 или 4 байта, чтобы записать длину значения столбца, в зависимости от максимальной возможной длины типа.

Если таблица включает любые столбцы переменной длины, формат записи будет также переменной длины. Обратите внимание, что, когда таблица создана, MySQL может, при некоторых условиях, изменять столбец с типа переменной длины на тип фиксированной длины. Подробности в разделе "7.3.1 Тихие изменения спецификации столбца".

Размер объекта ENUM определен числом различных значений перечисления. Один байт используется для перечислений до 255 возможных значений. Два байта используются для перечислений до 65535 значений. Подробности в разделе "5.3.3 Тип ENUM ".

Размер объекта SET определен числом различных членов набора. Если размер набора равен N, то объект занимает (N+7)/8 байт, округленных до 1, 2, 3, 4 или 8 байт. SET может иметь максимум до 64 членов. Подробности приведены в разделе "5.3.4 Тип SET".

Партнёры:

Хостинг: