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

MySQL поддерживает несколько различных типов данных:
Числовые данные — это все целые числа (без дробной части) и вещественные числа (с дробной частью).
Строковые данные — последовательность символов, заключенная в кавычки. В MySQL в качестве стандарта используются одинарные кавычки и для совместимости с другими базами данных рекомендуется именно их использование.
Календарные данные — тип для обозначение даты и времени, принимает различные формы, например строковую «2007-11-23» или числовую 20071123. Особенностью этого типа данных является их хранение в едином внутреннем формате, что позволяет производить операции сложения и вычитания, в независимости от внешнего представления.
NULL — специальный тип данных, обозначает отсутствие информации.

Итак, теперь о каждом типе данных подробнее.

Числовые данные

Числовые данные делятся на точечные (это BOOLEAN, INTEGER, DECIMAL) и приближенные (FLOAT, REAL, DOUBLE PRESISION).

В {} - таких скобках заключены необязательные элементы синтаксиса.

TINYINT{(N)} - 1 байт
// от -128 до 127 (от -2^7 до 2^7-1)
// от 0 до 255 (от 0 до 2^8-1)

SMALLINT{(N)} - 2 байта
// от -32768 до 32767 (от -2^15 до 2^15-1)
// от 0 до 65535 (от 0 до 2^16-1)

MEDIUMINT{(N)} - 3 байта
// от -8388608 до 8388608 (от -2^23 до 2^23-1)
// от 0 до 16777215 (от 0 до 2^24-1)

INT{(N)}, INTEGER{(N)} - 4 байта
// от -2147683648 до 2147683648 (от -2^31 до 2^31-1)
// от 0 до 4294967295 (от 0 до 2^32-1)

BIGINT{(N)} - 8 байта
// от -2^63 до 2^63-1
// от 0 до 2^64

BIT{(N)} - (N_7)/8 байт
// от 1 до 64 бит, зависит от значения N

BOOL, BOOLEAN - 1 байт
// либо 0, либо 1

DECIMAL{(N{,D})}, DEC{(N{,D})}, NUMERIC{(N{,D})} - N+2 байта
// Повышенная точность, зависит от значений N и D.

Итого, имеем: пять целых типов TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Различающихся между собой диапазоном хранимых в них величин. Чем больше диапазон значений, тем больше памяти для его хранения потребуется.

Целые типы данных могут быть объявлены положительными. Для этого после объявления необходимо использовать ключевое слово UNSIGNED. В таком случае элементам столбца нельзя будет присвоить отрицательные значения, а допустимый диапазон положительных чисел которые может принимать элемент, удваивается. Например: TINYINT принимает от -128 до 127, а TINYINT UNSIGNED — от 0 до 255.

При объявлении значения целого типа можно задать количество отводимых под число символов N (от 1 до 255). Делать это не обязательно, данное указание используется для дополнения пробелами слева от выводимых значений символов, меньших чем заданная ширина (при этом ограничений на диапазон значений это ни каких не налагает). Если указать дополнительно необязательный атрибут ZEROFILL, то вместо пробелов свободные позиции будут заполнены нулями слева. Например INT(6) ZEROFILL, значение 34 отобразится как 000034.

Тип BIT{(N)} предназначен для хранения битовых полей. Параметр N обозначает число битовых значений, которое может принимать поле (от 1 до 64). Если параметр N не указан, то он принимает значение по умолчанию равное 1. (Тип BIT появился в MySQL с версии 5.0.3)

Тип BOOLEAN это аналог TINYINT(1). Значение 1 это истина (true), значение 0 ложь (false).

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

summarub DECIMAL(5,2)

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

Важно: Первый параметр N может принимать максимальное значение, равное 64, второй равное 30.

Величины DECIMAL, DEC и NUMERIC хранятся как строки, а не как двоичное число с плавающей точкой, для сохранения точности представления этих величин в десятичном виде. Если значение второго параметра равно 0, то величины DECIMAL и NUMERIC не содержат десятичного знака или дробной части.

Для представления вещественных типов данных MySQL содержит три типа: FLOAT, DOUBLE и DECIMAL.

В {} - таких скобках заключены необязательные элементы синтаксиса.

FLOAT{(N,D)} - 4 байта
// минимальное значение ± 1.175494351*10^(-39)
// максимальное значение ± 3.402823466*10^38

DOUBLE{(N,D)}, REAL{(N,D)}, DOUBLE PRECISION{(N,D)} - 8 байт
// минимальное значение ± 2.2250738585072014*10^(-308)
// максимальное значение ± 1.797693134862315*10^308

Параметр N — задает количество символов для всего числа, D — количество символов дробной части.

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

Вещественные числа могут быть заданы как в обычной форме, к примеру 47.85, и в научной нотации, например 4.785E+04.

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

Пример создания таблицы:

create table table1 (
number1 int default 0,
number2 float default 1.0
)

Из примера видно, что после типа столбца задано ключевое слово DEFAULT. Это поле позволяет задать значение поля по умолчанию, так для поля number1 это значение равно 0, а для number2 1.0. Ключевое слово DEFAULT является не обязательным и может не указываться, то-есть мы можем создать таблицу и так:

create table table1 (
number1 int,
number2 float
)

Строковые данные

Максимальные размеры и требования к памяти, строковых типов данных:

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

CHAR(N)
// Объем памяти - N символов
// Максимальный размер - N символов

VARCHAR(N)
// Объем памяти - L+1 символов
// Максимальный размер - N символов

TINYBLOB, TINYTEXT
// Объем памяти - L+1 символов
// Максимальный размер - 2^8-1 символов

BLOB, TEXT
// Объем памяти - L+2 символов
// Максимальный размер - 2^16-1 символов

MEDIUMBLOB, MEDIUMTEXT
// Объем памяти - L+3 символов
// Максимальный размер - 2^24-1 символов

LONGBLOB, LONGTEXT
// Объем памяти - L+4 символов
// Максимальный размер - 2^32-1 символов

ENUM('value1','value2',...)
// Объем памяти - 1 или 2 байта
// Максимальный размер - 65535 элементов

SET('value1','value2',...)
// Объем памяти - 1, 2, 3, 4 или 8 байт
// Максимальный размер - 64 элемента

Тип данных CHAR может хранить строку фиксированной длины N, его дополняет тип VARCHAR, который позволяет хранить переменные строки длиной L. Значение N может принимать значения от 0 до 65535. (До версии MySQL 5.0.3 значение N могло быть только от 0 до 255)

При выборе строкового типа данных для столбца, следует учитывать что для переменных строк VARCHAR требуется количество символов, равное длине строки плюс один байт, а для типа CHAR(N) в независимости от длины строки будут сохранены все N символов.

При создании таблицы нельзя комбинировать столбцы типов CHAR и VARCHAR. Если такое произойдет, то MySQL изменит тип столбцов CHAR на тип VARCHAR.

С версии MySQL 4.1.2, в типах CHAR и VARCHAR строки рассматриваются как последовательности символов. Таким образом, при использовании многобайтных кодировок, например UNICODE, размер строки в байтах будет больше, чем в символах. Для совместимости со старыми версиями MySQL введены два специальных типа данных: BINARY и VARBINARY, которые эквивалентны типам CHAR и VARHAR, однако строка в них рассматривается как последовательность байтов, а не символов. К BINARY строкам не применимы кодировки и сортируются они как обычные последовательности байтов.

Типы данных BLOB и TEXT в MySQL отличаются лишь в небольших деталях. Например, при выполнении действий над столбцами типа TEXT учитывается кодировка, а типа BLOB — нет.

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

Главным отличием типа TEXT от CHAR и VARCHAR является поддержка возможностей полнотекстового поиска.

Особыми типами данных являются ENUM и SET. Строки данных типов принимают значения из заранее заданного списка значений. Основное отличие между этими двумя типами в том, что значение типа ENUM должно содержать только одно значение из указанного множества, в то время как столбцы SET могут содержать любой или все элементы заранее заданного множества одновременно. К примеру, значения для столбца объявленного как ENUM('z','x'), могут принимать только два значения: либо 'z', либо 'x'.

Для типа SET, как и для типа ENUM, при объявлении задается список возможных значений, только ячейка может принимать любое значение из списка, а пустая строка означает, что ни один из элементов списка не выбран. К примеру, значения для столбца SET('z','x') могут принимать значения ('z','x'), ('z'), ('x') и пустое множество ().

Типы данных ENUM и SET строковые лишь отчасти, так как при объявлении они задаются списком строк, однако во внутреннем представлении базы данных элементы множества сохраняются в виде чисел. Элементы типа ENUM нумеруются последовательно, начиная с 1. В зависимости от числа элементов в списке под столбец может отводиться 1 байт (до 256 элементов в списке) или 2 байта (от 257 до 65536 элементов в списке).

Элементы из множества SET обрабатываются как биты, размер типа при этом тоже определяется числом элементов в списке: 1 байт (от 1 до 8 элементов), 2 байта (от 9 до 16 элементов), 3 байта (от 17 до 24 элементов), 4 байта (от 25 до 32 элементов) и 8 байт (от 33 до 64 элементов).

Календарные данные

В MySQL есть 5 видов столбцов для хранения календарных типов данных: DATE, DATETIME, TIME, TIMESTAMP и YEAR. Тип DATE — для хранения даты, TIME — для хранения времени, TIMESTAMP — для представления даты и времени в виде числа секунд, которые прошли с полуночи 1 января 1970 года. Тип данных YEAR — позволяет хранить только год.

В {} - таких скобках заключены необязательные элементы синтаксиса.

DATE - 3 байта
// от '1000-01-01' до '9999-12-31'

TIME - 3 байта
// от '-828:59:59' до '828:59:59'

DATETIME - 8 байт
// от '1000-01-01 00:00:00' до '9999-12-31 00:00:00'

TIMESTAMP{(N)} - 4 байта
// от '1970-01-01 00:00:00' до '2038-12-31 59:59:59'

YEAR{(N)} - 1 байт
// от '1901' до '2155' для YEAR(4)
// от '1970' до '2069' для YEAR(2)

Значения типов DATE и DATETIME, в качестве первого числа принимают год либо в формате «YYYY», например '2010-12-10', либо в формате «YY», например '10-12-10'. Затем через дефис указывается месяц в формате «MM» (12), а следом день аналогично через дефис день в формате «DD» (10).

В типах TIME и DATETIME время приводится в формате hh:mm:ss, где hh — часы, mm — минуты, ss — секунды. Дни, месяцы, часы, минуты и секунды можно записывать как с ведущим нулем: 01, так и без него: 1. К примеру все следующие записи идентичны:

2010-02-09 03:05:04
2010-02-09 03:05:4
2010-02-09 03:5:4
2010-02-09 3:5:4
2010-02-9 3:5:4
2010-2-9 3:5:4

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

10-02-09 03:05:04
10.02.09 03+05+04
10/02/09 03*05*04
10@02@09 03^05^04

Дата и время суток могут быть представлены и в форматах «YYYYMMDDhhmmss» и «YYMMDDhhmmss». Например, строки '20100209030504' и '100209030504' аналогичны '2010-02-09 03:05:04'. Вместо строк допустимы и целочисленные значения, например, 20100209030504 и 100209030504 интерпретируются как '2010-02-09 03:05:04'.

С версии MySQL 4.1.1, при указании времени, после секунд через точку можно указать микросекунды, использовать расширенный формат вида 'hh:mm:ss.ffffff', например '03:05:04.000002'. Помимо расширенного формата, можно использовать краткие форматы 'HH:MM' и 'HH' — вместо пропущенных величин будут подставлены нулевые значения.

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

Представление нулевых значений:

DATE '000-00-00'
TIME '00:00:00'
DATETIME '0000-00-00 00:00:00'
TIMESTAMP 00000000000000
YEAR 0000

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

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

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

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

Пример:

create table tabl1(
mytime1 timestamp default current_timestamp,
mytime2 timestamp on update current_timestamp
);

Здесь поле mytime1 — получает текущее время при создании записи, а поле mutime2 — получает текущее время при изменении записи.

Столбцы типа данных YEAR предназначены для хранения только года, указание параметра N позволяет задать формат года: двузначный CHAR(2) «YY» или четырехзначный CHAR(4) «YYYY». Если параметр N не указан то по умолчанию считается что он равен 4.

Тип данных NULL

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

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

Атрибут NOT NULL можно не указывать, поскольку он присваивается столбцу по умолчанию, если никакой из атрибутов не указан. Совместно с атрибутами NOT NULL и NULL можно использовать DEFAULT, который имеет больший приоритет.

Заключение

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