Типы данных в MySQL

В этой инструкции рассказываем про основные типы данных в MySQL и даем советы, где лучше использовать тот или иной тип.

Введение

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

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

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

Используются для хранения чисел. Бывают целочисленными, с плавающей точкой и с фиксированной точкой.

Целые числа

Хранят только целые числа, без дробной части. Делятся на signed и unsigned. Типы singed позволяют хранить как положительные, так и отрицательные значения. Типы unsigned исключает отрицательные значения из диапазона (от 0 и до максимального положительного значения). Это может быть полезно, например, для указания количества товара или идентификатор записи в таблице.

Тип	Размер (байт)	Диапазон signed	Диапазон unsigned
TINYINT	1	-128 … 127	0 … 255
SMALLINT	2	-32 768 … 32 767	0 … 65 535
MEDIUMINT	3	-8 388 608 … 8388607	0 … 16 777 215
INT	4	-2 147 483 648 … 2 147 483 647	0 … 42 294 967 295
BIGINT	8	-2⁶³… 2⁶³-1	0 … 2⁶⁴-1

Числа с плавающей точкой

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

Тип	Размер (байт)	Диапазон
FLOAT	4	-3.402823466E+38 … -1.175494351E-38 и 1.175494351E-38 … 3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308 … -2.2250738585072014E-308 и2.2250738585072014E-308 … 1.7976931348623157E+308

До MySQL 8.0.17 атрибут UNSIGNED для FLOAT и DOUBLE ограничивал значения снизу нулем, но не увеличивал максимальное значение, как это происходит с целочисленными типами. Начиная с MySQL 8.0.17 атрибут UNSIGNED для FLOAT и DOUBLE объявлен не рекомендуемым к использованию.

Числа с фиксированной точкой

Эти типы данных используются для сохранения заданной точности. Эти типы данных используются, когда нужна фиксированная точность. В DECIMAL(M,D) параметр M задает общее количество цифр, а D — количество цифр после десятичного разделителя. Такие типы подходят для случаев, когда важна точность, например для денежных значений.

DECIMAL(M,D) и NUMERIC(M,D) — синонимы. Можно использовать любой из этих типов, результат будет одинаковым. В таких столбцах можно хранить число, содержащее до 65 цифр вместе в целой и дробной частях.

Символьные (строковые)

Символьные типы используются для хранения текстов. Есть два основных типа: CHAR и VARCHAR. С точки зрения пользователя они выглядят похоже, но MySQL хранит и обрабатывает их по-разному.

CHAR хранит строку фиксированной длины до 255 символов. Если длина вставляемой записи меньше, то MySQL автоматически дополняет значение пробелами. Например, если мы указали тип CHAR(10) и сохранили строку «Привет», то по факту в БД будет храниться строка «Привет » (обратите внимание на четыре пробела в конце строки).
CHAR удобно использовать для значений фиксированной длины. Например, его можно использовать для хранения хешей, у которых длина всегда одинакова.
VARCHAR(M) задает длину в символах, но максимальный размер строки ограничен 65 535 байтами, поэтому фактическое количество символов зависит от кодировки.

Текстовые и бинарные

Текстовые (TEXT) и бинарные (BLOB) типы данных используются для хранения больших объемов текста или двоичных данных. Эти типы похожи, но отличаются по способу хранения и обработки внутри MySQL.

BLOB обрабатывается как двоичные данные. В нем не хранится набор символов, а операции сортировки и сравнения основаны на числовых значениях байтов.

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

Текстовые типы отлично подходят для хранения больших текстов: статей, докладов и так далее. А бинарные типы могут хранить любые файлы или мультимедиа-контент.

Тип	Макс. размер значения
TINYTEXT / TINYBLOB	255 байт
TEXT / BLOB	65 535 байт
MEDIUMTEXT / MEDIUMBLOB	16 777 215 байт
LONGTEXT / LONGBLOB	4 294 967 295 байт

Для TEXT количество символов зависит от используемой кодировки. Для BLOB размер непосредственно измеряется в байтах.

Дата/время

Типы данных, которые позволяют работать с датой и временем.

Тип	Размер (байт)	Описание
DATE	3	Только дата в формате YYYY-MM-DD. Допустимые значения от 1000-01-01 до 9999-12-31
DATETIME	5	Дата и время в формате YYYY-MM-DD HH:MM:SS. Допустимые значения от 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP	4	Дата и время. `TIMESTAMP` хранит временную метку и при сохранении или извлечении преобразуется между часовым поясом сессии и UTC. Занимает в два раза меньше места, чем тип DATETIME. Но при этом диапазон ограничен значениями от 1970-01-01 00:00:01 до 2038-01-19 03:14:07
TIME	3	Время или интервал в формате HH:MM:SS. Допустимые значения от -838:59:59 до 838:59:59
YEAR(N)	1	Год в формате YYYY. Основной диапазон — от 1901 до 2155, также поддерживается значение 0000. Для нового кода рекомендуется использоватьYEAR без параметра.

JSON

Это относительно новый тип данных, который появился в MySQL версии 5.7.8. Он позволяет нативно хранить и обрабатывать данные в JSON-формате.

В отличие от хранения объектов в виде текста, в использовании специального типа данных есть несколько преимуществ:

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

Составные типы

ENUM и SET относятся к строковым типам, но MySQL использует внутреннее числовое представление элементов заданного списка. Поэтому они могут занимать относительно мало места.

ENUM может хранить только одно значение из списка, занимает 1-2 байта.
SET может хранить одновременно до 64 значений из списка, занимает от 1 до 8 байт.

Заключение

Мы рассмотрели основные типы данных и полей в MySQL, объяснили разницу между схожими типами. Теперь вы можете создавать свои структуры БД, используя полученные знания.

MySQL Базы данных