Серверная (www.it-simple.ru)

Типы данных MySQL


Напомню синтаксис объявления поля (столбца) таблицы при её описании:

имя тип [атрибуты_типа] [общие_атрибуты]

Например:

my_field_name INT UNSIGNED NOT NULL PRIMARY KEY

Здесь и дальше в квадратных скобках указываются необязательные значения.

UNSIGNED NOT NULL PRIMARY KEY - необязательные атрибуты в примере.

Целые числа

Целые числа можно задавать как обычным десятичным видом, так и шестнадцатиричным представлением (с префиксом 0x).

Атрибуты целых чисел

AUTO_INCREMENT
Автоматически увеличивать значение ячейки на 1 для каждой следующей строки. Для столбца с AUTO_INCRMENT также должны быть выставлены атрибуты NOT NULL и PRIMARY KEY. В таблице не может быть больше одного столбца с автоинкрементом.

ZEROFILL
Дополнить значение столбца ведущими нулями.

UNSIGNED
По умолчанию, первый бит числа отвечает за его знак (0 – положительное, 1 – отрицательное). При указании типа с UNSIGNED, максимальное значение числа увеличивается в два раза, благодаря первому биту (за счёт диапазона отрицательных чисел).

Таблица целочисленных типов

ТипДлина (байт)Описание
TINYINT[(M)]1Хранение целых чисел от -128 до 127. В сочетании с UNSIGNED - от 0 до 255.
SMALLINT[(M)]2Хранение целых чисел от -32768 до 32767. В сочетании с UNSIGNED - от 0 до 65535.
MEDIUMINT[(M)]3Диапазон от -8 388 608 до 8 388 607. В сочетании с UNSIGNED - от 0 до 16777215.
INT[(M)]
INTEGER[(M)]
4Диапазон от -231 до 231-1. В сочетании с UNSIGNED - от 0 до 232. Тип отлично подходит для хранения IP-адресов (IPv4) и для даты в формате UNIX (число секунд с начала эпохи UNIX, 00:00:00 01.01.1970)
BIGINT[(M)]8Соответственно, от -263 до 263-1 без UNSIGNED и от 0 до 264 с ним. 264 = 18446744073709551616, то есть просто дохрена.

Здесь [(M)] -необязательный параметр, целое число в круглых скобках. Само число M может находиться в диапазоне от 1 до 255 и задаёт максимальную ширину отображения типа, в количестве символов. Если параметр не указан, то он задаётся автоматически в, соответствии с типом. Если выводимое значение «шире» чем M, то оно будет выведено полностью, без усечений. При использовании параметра с атрибутом ZEROFILL выводимые значения будут дополняться нулями до ширины M.

Пример

mysql> CREATE TABLE test (test_zerofill INT(4) ZEROFILL,test_tinyint TINYINT,test_utinyint TINYINT UNSIGNED);
mysql> INSERT INTO test VALUES (130,130,130),(10001,10001,10001);
mysql> SELECT * FROM test;
+---------------+--------------+---------------+
| test_zerofill | test_tinyint | test_utinyint |
+---------------+--------------+---------------+
|          0130 |          127 |           130 | 
|         10001 |          127 |           255 | 
+---------------+--------------+---------------+

Вещественные числа

Вещественные числа можно также задавать и научной нотацией (Ae[+|-]B или AE[+|-]B, где A и B - числа, [+|-] - один из знаков, плюс или минус).

ТипДлина (байт)Описание
FLOAT[(M,D)]4Минимальное ненулевое значение +-1.175494351e-38.
FLOAT(4)4Максимальное ненулевое значение +-3.402823466e+38.
DOUBLE[(M,D)]
DOUBLEPRECISION[(M,D)]
REAL[(M,D)]
8Минимальное ненулевое значение +-2.2250738585072014e-308.
FLOAT(8)8Максимальное ненулевое значение +-1.797693134862315e+308.
DECIMAL(M,D)
NUMERIC(M,D)
M+2Хранение дробных чисел в виде строки.

Здесь M - число от 1 до 255, задающее ширину отображения числа, включая D символов дробной части. D - число от 0 до 30.

Для типа DECIMAL, M - общая ширина числа в символах, без учёта разделителя, но с учётом знака в отрицательных числах.

Строковые (двоичные) данные

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

Атрибуты строковых типов

BINARY
Используется только с типами CHAR и VARCHAR. Позволяет оперировать столбцом как двоичной строкой.

Таблица строковых типов

ТипДлина (байт)Описание
CHAR(M)MХранение строк фиксированной длины. При сохранении строки в ячейку этого типа, она либо обрезается, либо дополняется пробелами справа до длины M. При возврате значения пробелы удаляются. Может хранить не более 255 символов.
VARCHAR(M)L+1Хранение строк переменной длины. Может хранить не более M байт. Но в памяти занимает L байт данных плюс 1 байт для описания их длины. Таким образом, максимальная длина данных - 255 байт.
TINYBLOB
TINYTEXT
L+1Может хранить не более 255 байт данных.
BLOB
TEXT
L+2Может хранить не более 65 535 байт (64 килобайта) данных.
MEDIUMBLOB
MEDIUMTEXT
L+3Может хранить не более 224-1 байт (8 мегабайт) данных.
LONGBLOB
LONGTEXT
L+4Может хранить не более 232-1 байт (4 гигабайта) данных.
ENUM("v1", "v2",..)1 или 2Может хранить не более 65535 элементов. Объём занимаемой памяти зависит от их количества. Это особый строковый тип, который хранит только определённый набор значений. В ячейке может находиться только одно значение из списка доступных.
SET("v1","v2",..)1, 2, 3, 4 или 8Может хранить не более 64 элементов. В отличии от ENUM, в ячейке может находиться несколько значений одновременно. Набор значений ячейки определяется по битовой маске (8 байт=64 бита).

Здесь L - длина содержимого столбца в байтах. Дополнительные байты (1, 2, 3 или 4) нужны для того, чтобы задать точный размер данных.

Таблицы со строками фиксированной длины обрабатываются эффективнее, чем со строками переменной длины, но если в таблице есть столбцы типа VARCHAR, тип CHAR использовать не имеет смысла. MySQL умеет преобразовывать типы данных для оптимизации работы, что она в этом случае и проделает.

По этой же причине, при задании длины типа VARCHAR меньше 4 байт - он преобразуется в тип CHAR.

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

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

ТипДлина (байт)Описание
DATE3Дата в формате YYYY-MM-DD (ГГГГ-ММ-ДД).
TIME3Время в формате hh:mm:ss (чч:мм:сс).
DATETIME8Смешаный тип данных. Дата и время в формате YYYY-MM-DD hh:mm:ss (ГГГГ-ММ-ДД чч:мм:сс)
TIMESTAMP[(M)]4Дата и время в формате YYYYMMDDhhmmss (ГГГГММДДччммсс).
YEAR[(M)]1Четыре цифры года.

NULL

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

Ссылки

  1. http://www.spravkaweb.ru/mysql/sql/vartype (http://www.spravkaweb.ru/mysql/sql/vartype)
  2. http://www.kamaikin.ru/artical/sql/1/3/ (http://www.kamaikin.ru/artical/sql/1/3/)