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

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

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

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

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

Преимущества работы MySQL в облаке

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

Среди преимуществ использования MySQL в облаке можно выделить:

• Обслуживание и настройка баз данных со стороны сервиса;
• Обеспечение надежности, высокой доступности и отказоустойчивости;
• Автоматическое резервное копирование;
• Быстрое горизонтальное и вертикальное масштабирование;
• Бесперебойная работа за счет репликации кластера;
• Работы БД на вычислительных ресурсах облачной платформы. 

Чтобы создать облачную базу данных, обратитесь к соответствующей инструкции в нашей базе знаний.

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

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

1. целые числа;
2. числа с плавающей точкой;
3. числа с фиксированной точкой.

Более подробно о каждом из перечисленных числовых типов данных в MySQL будет рассказано ниже.

Целые числа

Предназначен для хранения знаковых целых чисел (signed), либо беззнаковых (unsigned). Первый позволяет хранить все числа, как со знаком «+», так и «-». Второй, в свою очередь, хранит только положительные числа, однако их диапазон увеличивается вдвое. 

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

Перечень типов, их объем памяти и диапазон чисел представлены в таблице ниже.

Тип	Объем памяти (байты)	Диапазон чисел signed	Диапазон чисел unsigned
TINYINT	1	От -27 до 27-1	От 0 до 28-1
SMALLINT	2	От -215 до 215-1	От 0 до 216-1
MEDIUMINT	3	От -223 до 223-1	От 0 до 224-1
INT	4	От -231 до 231-1	От 0 до 232-1
BIGINT	8	От -263 до 263-1	От 0 до 264-1

Указание размера для целых чисел (например, MEDIUMINT (6)) не изменяет диапазон возможных значений. Это является справочно-сервисной информацией, которую используют некоторые инструменты для работы с MySQL. Данное указание лишь добавить пробелы слева от чисел, которые меньше заданной в скобках длины. 

Также можно указать специальный атрибут ZEROFILL, который вместо пробелов поставил 0 на свободные позиции. Он не является обязательным, поэтому им возможно пользоваться только при надобности. Допустим, пользователь указал SMALLINT (8) ZEROFILL, тогда значение 256 будет отображаться в системе следующим образом: 00000256. 

dbaas

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

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

FLOAT (p)

p — точность, которая может принимать значения от 0 до 53. Если значение меньше 25, то будет выбран FLOAT() — одинарная точность, иначе DOUBLE() — двойная точность.

Тип	Объем памяти (байты)	Диапазон чисел
FLOAT	4	От -3.4028 * 1038 до 3.4028 * 1038
DOUBLE	8	От -1.7976 * 1038 до 1.7976 * 1038

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

Предназначен для хранения числовых значений с заданной точностью, выделяя для целочисленной и дробной части числа определенный объем памяти. Для этого используют DECIMAL или NUMERIC.

У данных типов можно указывать два параметра в скобках — (M,D). Параметр M указывает на количество отводимых для числа символов, а параметр D на количество символов после запятой. Первый параметр принимает значения до 65 (по умолчанию 10). Второй параметр принимает значения от 0 до 30 (по умолчанию 0). 

В качестве примера приведем:

DECIMAL(6,3)

Данный пример позволяет хранить числа, состоящее из шести чисел и трех цифр после запятой. Диапазон подходящих значений будет следующий: от -999.999 до 999.999.

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

Символьные

Предназначены для хранения небольших текстов. К ним относятся CHAR и VARCHAR. Они похожи между собой. Главное различие между ними — это способ хранения и извлечения данных.

CHAR хранит строку фиксированной длины (от 0 до 2⁸-1 символов), которая задается на этапе создания таблицы. Если происходит передача строки меньшей длины, чем была указана, то оставшиеся символы заполняются пробелами.

Тип данных VARCHAR в MySQL хранит строку переменной длины (от 0 до 2¹⁶-1 символов), которая также задается на этапе создания таблицы. В отличие от CHAR он хранит только указанную длину, а также выделяет 1 байт для хранения длины строки.

Если длина строки превосходит длину указанную в CHAR или VARCHAR, то передаваемая строка обрезается до допустимой длины.

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

Для наглядности ниже будет представлена таблица с примерами хранения разной длины строки для CHAR(5) и VARCHAR(5).

Строка	CHAR(5)	VARCHAR(5)
‘A’	‘A    ’	‘A’
‘Hello’	‘Hello’	‘Hello’
‘Example’	‘Examp’	‘Examp’

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

Текстовый тип данных в MySQL TEXT предназначен для хранения больших текстовых данных, а бинарный BLOB для хранения двоичных данных, таких как изображения, компилированный программный код или звук. Оба этих метода схожи между собой. Главное их отличие — это способ хранения данных и их обработка. У BLOB сортировка и сравнение происходят с учетом регистра, а у TEXT без учета регистра.

MySQL не имеет возможности индексировать данные TEXT и BLOB по всей длине, а также использовать индексы для сортировки.

Если длина строки превосходит возможную длину TEXT или BLOB, то передаваемая строка обрезается до допустимого значения.

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

Тип	Объем памяти (байты)	Количество символов
TINYTEXT	28-1	До 28-1
TEXT	216-1	До 216-1
MEDIUMTEXT	224-1	До 224-1
LONGTEXT	232-1	До 232-1

Для BLOB будет идентичная таблица, что и для TEXT. Она представлена ниже.

Тип	Объем памяти (байты)	Количество символов
TINYBLOB	28-1	До 28-1
BLOB	216-1	До 216-1
MEDIUMBLOB	224-1	До 224-1
LONGBLOB	232-1	До 232-1

Дата и время

Далее будут представлены типы данных для хранения даты и времени. Ниже будет представлена таблица со всеми необходимыми данными.

Тип	Объем памяти (байты)	Краткая информация
DATE	3	Рассчитан на хранение только даты, формата: ‘ГГГГ-ММ-ДД’. Для разделения года, месяца и дня может служить не только дефис, но и любой другой символ. Главное, чтобы это была не цифра. Диапазон данных от 1 января 1000 года до 31 декабря 9999 года.
DATETIME	8	Предполагает хранение даты и времени суток, формата: ‘ГГГГ-ММ-ДД чч: мм: сс’.  Диапазон данных от 00:00:00 1 января 1000 года до 23:59:59 31 декабря 9999 года.
TIMESTAMP	4	Рассчитан на хранение даты и времени суток, формата: ‘ГГГГ-ММ-ДД чч: мм: сс’. Из-за меньшего диапазона значений для хранений используется в 2 раза меньше памяти, чем для DATETIME.  Диапазон значений от 00:00:01 1 января 1970 года до 03:14:07 9 января 2038 года.
TIME	3	Предполагает хранение только времени, формата: ‘чч: мм: сс’ или ‘ччч: мм: сс’ для большого количества часов.  Диапазон времени начиная с -838:59:59 и заканчивая 838:59:59
YEAR	1	Рассчитан на хранение только года, формата: ‘ГГГГ’.  Диапазон данных от 1901 года до 2155 года, а также можно ввести 0000.

JSON

MySQL поддерживает свой тип данных, который называется JSON (JavaScript Object Notation). Он был выпущен с версией 5.7.8 и предоставляет следующие преимущества по сравнению с хранением объектов в виде текста:

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

Объем памяти, необходимый для хранения данных типа JSON, примерно такой же, как и у LONGTEXT.

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

Особый строковый тип данных, который хранит значения из определенного фиксированного набора. Сюда определяют ENUM и SET. Первый хранит одно значение из набора, которое занимает до двух байт памяти. Второй, в свою очередь, может хранить до 2⁶ значений из набора и занимает до восьми байт памяти.

Например, пользователь объявляет некий список значений для ENUM:

ENUM (‘a’, ‘s’, ‘d’)

Тогда значения для такого столбца могут принимать только три значения: это ‘a’, ‘s’, либо ‘d’.

В свою очередь, если пользователь задаст такой же список для SET:

SET (‘a’, ‘s’, ‘d’)

Тогда значения для такого столбца могут принимать только следующие значения: это (‘a’), (‘s’), (‘d’), (‘a’, ‘s’), (‘a’, ‘d’), (‘s’, ‘d’), (‘a’, ‘s’, ‘d’), либо пустое множество ().

В случае, если пользователь вставит значение (‘a’, ’s’, ’a’) в таблицу, тогда при его извлечении MySQL выведет каждый элемент только один раз и в том порядке, в котором они задавались во время создания таблицы. Для (‘a’, ’s’, ’a’) отобразится (‘a’, ‘s’) при извлечении.

Разверните MySQL в облаке за минуту

Заключение

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