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

MySQL — одна из самых популярных систем управления реляционной базой данных с открытым исходным кодом (в дальнейшем просто «база данных»). Как следует из названия, эта система использует язык SQL (Structured Query Language — язык структурированных запросов).

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

Рассмотрим основные типы данных, доступные в MySQL.

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

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

Целые числа

К целым (int, integer) относят положительные и отрицательные числа без дробной части. Числа, принимающие только положительные значения, называют числами без знака (unsigned), а положительные и отрицательные — числами со знаком (signed). В зависимости от размера памяти, выделенной (зарезервированной) для хранения целых чисел, типом данных столбцов могут быть TINYINT, SMALLINT, MEDIUMINT, INT и BIGINT. Важно понимать, что при одном и том же размере ячейки (одном и том же типе данных) диапазон значений целых чисел со знаком и без будет различным:

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

Числа с плавающей точкой представляют приблизительные значения данных. В MySQL им соответствуют типы FLOAT и DOUBLE:

Обратите внимание, что числа с плавающей точкой имеют знак, то есть относятся к signed-числам. Старые версии MySQL имели поддержку беззнаковых чисел, а также могли принимать указание точности (в скобках после ключевого слова FLOAT), но в настоящее время использовать такие типы не рекомендуется.

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

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

Синтаксис объявления DECIMAL-столбца следующий: DECIMAL(M,D), где M — максимальное количество цифр в диапазоне от 1 до 65, а D — количество цифр справа от десятичной точки (дробная часть). Выражение NUMERIC(M,D) в MySQL является синонимом DECIMAL(M,D). Например, столбец DECIMAL(5,2) или NUMERIC(5,2) поддерживает диапазон значений от -999,99 до 999,99. 

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

Символьные, строковые и бинарные и составные

Символьные (строковые) типы используются для хранения строк текста. К таким типам относятся CHAR, VARCHAR и TEXT:

• CHAR хранит строку фиксированной длины от 0 до 255 символов. При сохранении строка дополняется ведущими пробелами до указанной длины. Например, CHAR(16) может хранить строку длиной до 16 символов. При извлечении данных концевые пробелы обрезаются.
• VARCHAR предназначен для хранения строк переменной длины от 0 до 65 535 символов. При сохранении строки не дополняются. В отличие от типа CHAR, концевые пробелы сохраняются при записи и извлечении данных.
• TEXT делится на типы TINYTEXT, TEXT, MEDIUMTEXT, и LONGTEXT с максимальной длиной 255, 65 535, 16 777 215 и 4 294 967 295 символов соответственно. Это означает, что текстовые поля можно использовать для хранения длинных текстовых строк объёмом до 4 ГБ.

К бинарным относятся типы BLOB, BINARY, VARBINARY и BIT.

• Типы BINARYи VARBINARY похожи на CHAR и VARCHAR, но они хранят байтовые, а не символьные строки.
• Бинарные типы BLOB (binary large object, большой двоичный объект), так же, как TEXT, могут хранить переменный объем данных размером до 4 ГБ. В зависимости от максимальной длины данных делится на TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB. Данные BLOB-типов хранятся и обрабатываются как набор двоичных данных. Сравнение и сортировка данных этого типа основаны на числовых значениях байтов. Чаще всего вы можете рассматривать столбец BLOB как большой VARBINARY.
• Тип BIT предназначен для хранения битовых значений в диапазоне от 1 до 64.

Типы BINARY и BLOB можно также считать строковыми, а BIT — числовым. 

Составными называют типы данных, которые хранят значения из заранее определённого списка. Это может помочь сэкономить дисковое пространство, если в таблице вам нужно хранить одни и те же строки. К составным относятся типы ENUM и SET:

• ENUM — позволяет хранить одно значение из списка.
• SET — может хранить одновременно от 0 до 64 значений из списка.

Экономия достигается за счёт того, что составные типы хранят не символы, а числовые указатели на нужные строки. Поэтому фактическое значение поля ENUM занимает в памяти от 1 до 2 байт, а SET — от 1 до 8 байт.

Например, если в таблице MySQL нужно хранить данные веб-формы, то текст из поля <textarea> удобно записывать в ячейку типа TEXT, строки ограниченной длины из <input type="text"> — в VARCHAR, а значения переключателей <input type="radio"> — в CHAR или ENUM.

Дата и время

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

• DATE — предназначен для хранения даты в формате ГГГГ-ММ-ДД (YYYY-MM-DD). Занимает в памяти 3 байта.
• DATETIME — дата и время в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС (YYYY-MM-DD HH:MM:SS). Размер поля — 8 байт.
• TIME — только время в формате ЧЧ:ММ:СС (HH:MM:SS). Размер поля — 3 байта.
• YEAR — год в формате ГГГГ (YYYY) или ГГ (YY). Размер — 1 байт.
• TIMESTAMP — метка времени, то есть количество секунд, прошедшее с 1 января 1970 года по гринвичу. Занимает 4 байта.

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

JSON

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

• позволяет работать с отдельными полями объекта, а не со всей строкой;
• валидирует объект при сохранении в базу данных;
• обладает лучшей производительностью за счёт преобразования объектов во внутренний формат хранения.

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

Пространственные типы

MySQL позволяет хранить и обрабатывать различные геометрические и географические данные:

• GEOMETRY — пространственное значение любого типа.
• POINT — точка (пара координат XY).
• LINESTRING — кривая (одно или несколько POINT-значений).
• POLYGON — многоугольник.
• GEOMETRYCOLLECTION — коллекция значений типа GEOMETRY.
• MULTILINESTRING — коллекция значений типа LINESTRING.
• MULTIPOINT — коллекция значений типа POINT.
• MULTIPOLYGON — коллекция значений типа POLYGON.

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

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