Оптимизировать нельзя удалить: Типы данных в SQL

SQL сегодня - это универсальный инструмент для работы с базами данных и маст-хэв не только для аналитиков и разработчиков, но и для широкого ряда профессий внутри IT и за его пределами.

Понимание типов данных - один из базовых аспектов, без которого вся дальнейшая работа с базами данных невозможна. При этом типы данных могут различаться в зависимости от используемой СУБД, поэтому в этой статье мы рассмотрим самые распространенные из них в PostgreSQL, MySQL, SQL Server, SQLite и Oracle, их особенности и применение.

Зачем вообще нужны типы данных?

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

• Оптимизировать хранение данных. Использование подходящего типа данных позволяет экономить пространство в базе данных.
• Обеспечить целостность данных. Установка соответствующих типов данных помогает предотвратить ошибки ввода и обеспечить соответствие данные ожидаемому формату.
• Ускорить запросы. SQL-серверы оптимизируют выполнение запросов на основе типов данных, а это может существенно повысить производительность.

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

Какие бывают типы данных?

Самые популярные типы данных в SQL условно можно разделить на пять основных категорий:

1. Числовые типы данных
2. Строковые типы данных
3. Дата и время
4. Логические типы данных
5. Двоичные типы данных

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

Числовые типы данных

Используются для хранения чисел и позволяют производить с ними арифметические операции, сравнения и другие математические расчеты. Числовые типы данных можно разделить на такие основные категории:

1. Целочисленные типы данных

Используются для хранения чисел без десятичных дробей. Они различаются по диапазону значений и объему занимаемой памяти.

• TINYINT
• Диапазон значений: от -128 до 127 для знаковых значений и от 0 до 255 для беззнаковых.
• Размер: 1 байт.
• Применение: Часто используется для хранения небольших чисел, таких как возраст.
• СУБД: MySQL, SQL Server.
• SMALLINT
• Диапазон значений: от -32 768 до 32 767 для знаковых значений и от 0 до 65 535 для беззнаковых.
• Размер: 2 байта.
• Применение: Подходит для данных, которые требуют большего диапазона, чем TINYINT, но не настолько большого, чтобы использовать INT.
• СУБД: PostgreSQL, MySQL, SQL Server.
• MEDIUMINT
• Диапазон значений: от -8 388 608 до 8 388 607 для знаковых значений и от 0 до 16 777 215 для беззнаковых.
• Размер: 3 байта.
• Применение: Используется в ситуациях, когда необходимо сбалансировать объем памяти и диапазон значений.
• СУБД: MySQL.
• INT (или INTEGER)
• Диапазон значений: от -2 147 483 648 до 2 147 483 647 для знаковых значений и от 0 до 4 294 967 295 для беззнаковых.
• Размер: 4 байта.
• Применение: Один из самых часто используемых типов данных, подходящий для большинства целочисленных значений.
• СУБД: PostgreSQL, MySQL, SQL Server, SQLite.
• BIGINT
• Диапазон значений: от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807 для знаковых значений и от 0 до 18 446 744 073 709 551 615 для беззнаковых.
• Размер: 8 байт.
• Применение: Используется для хранения очень больших чисел, например, уникальных идентификаторов или подсчета объектов в крупных системах.
• СУБД: PostgreSQL, MySQL, SQL Server.

2. Числа с фиксированной точностью и масштабом

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

• DECIMAL(p, s) или NUMERIC(p, s): Число с фиксированной точностью и масштабом, где p (precision) — это общее количество цифр, а s (scale) — количество цифр после десятичной точки. Например, DECIMAL(10, 2) может хранить числа с до 10 цифр, из которых 2 будут после десятичной точки.
• Размер: Такой тип данных занимает переменное количество байт в зависимости от значения параметра p.
• Применение: Часто используется в финансовых приложениях, где требуется высокая точность при расчетах (например, цены, балансы счетов).
• СУБД: PostgreSQL, MySQL, SQL Server, SQLite.

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

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

• FLOAT
• Описание: FLOAT представляет числа с плавающей запятой, которые могут хранить значения с дробной частью. В зависимости от реализации СУБД, FLOAT может различаться по точности и диапазону значений. В некоторых СУБД FLOAT можно уточнить точность с помощью параметра p - FLOAT(p), где p указывает количество цифр в мантиссе. Например, FLOAT(7) может сохранять до 7 значащих цифр.
• REAL: В некоторых СУБД FLOAT может быть синонимом REAL, который обычно имеет фиксированную точность и диапазон.
• Размер: 4 или 8 байт в зависимости от СУБД.
• Применение: Используется, когда требуется хранить числа с широкой степенью точности, например, результаты научных расчетов.
• СУБД: PostgreSQL(Real), MySQL, SQL Server, SQLite (Real), Oracle.
• DOUBLE PRECISION или просто DOUBLE
• Описание: Число с плавающей точкой, аналогично FLOAT, но с удвоенной точностью.
• Размер: 8 байт.
• Применение: Подходит для хранения чисел, требующих большей точности, чем FLOAT, но с тем же компромиссом в плане точности при округлении.
• СУБД: PostgreSQL, MySQL, Oracle.

Строковые типы данных

Строковые типы данных в SQL предназначены для хранения текстовой информации, такой как имена, адреса, описания и любые другие символы.

• CHAR(n)
• Описание: Тип данных CHAR(n) используется для хранения строк фиксированной длины. Здесь n — это количество символов, которое будет выделено для хранения строки.
• Особенности: Если строка короче указанной длины, она автоматически дополняется пробелами до заданной длины. Например, если задан CHAR(5) и строка "SQL" имеет длину 3 символа, то она будет храниться как "SQL " (с двумя пробелами в конце).
• Применение: CHAR используется для хранения данных, которые всегда имеют одинаковую длину, таких как коды стран, почтовые индексы или номера телефонов с фиксированной длиной.
• СУБД: PostgreSQL, MySQL, SQL Server, Oracle.
• VARCHAR(n)
• Описание: Тип данных VARCHAR(n) используется для хранения строк переменной длины, где n — это максимальное количество символов, которое может быть сохранено.
• Особенности: В отличие от CHAR, VARCHAR хранит только фактическое количество символов в строке и не дополняет её пробелами. Например, строка "SQL" в VARCHAR(5) будет занимать ровно 3 символа, без добавления пробелов.
• Применение: VARCHAR широко используется для хранения данных, длина которых может варьироваться, таких как имена, электронные адреса, адреса и описания.
• СУБД: PostgreSQL, MySQL, SQL Server, Oracle.
• TEXT
• Описание: TEXT используется для хранения длинных текстовых данных, таких как статьи, описания, комментарии или большие текстовые блоки.
• Особенности: В отличие от CHAR и VARCHAR, тип TEXT может хранить строки большой длины (обычно до нескольких гигабайт). Однако он может иметь некоторые ограничения в зависимости от СУБД.
• Применение: Применяется для хранения больших объемов текста, таких как описание продукта, статьи блога, комментарии пользователей и т.д.
• СУБД: PostgreSQL, MySQL, SQL Server, SQLite.

Дата и время

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

• DATE
• Описание: DATE — это тип данных, используемый для хранения даты в формате ГГГГ-ММ-ДД (год, месяц, день). Он хранит только информацию о дате, без учета времени.
• Пример: 2024-08-15
• Применение: Тип данных DATE применяется, когда требуется хранить только дату без учета времени. Например, это может быть дата рождения, дата регистрации пользователя или дата выполнения какого-либо действия.
• СУБД: PostgreSQL, MySQL, SQL Server, Oracle.
• TIME
• Описание: TIME — это тип данных, используемый для хранения времени суток в формате ЧЧ:ММ:СС (часы, минуты, секунды). Этот тип данных может включать доли секунды (микросекунды).
• Пример: 14:30:00 или 14:30:00.123456 (с долями секунды)
• Применение: Тип данных TIME используется для хранения времени без привязки к конкретной дате. Это может быть полезно для записи времени начала рабочего дня, времени встречи или расписания.
• СУБД: PostgreSQL, MySQL, SQL Server.
• DATETIME
Описание: DATETIME — это тип данных, который сочетает в себе дату и время. Формат хранения ГГГГ-ММ-ДД ЧЧ:ММ:СС. В некоторых СУБД он может включать доли секунды.
• Пример: 2024-08-15 14:30:00
• Применение: DATETIME используется в случаях, когда необходимо хранить и дату, и время вместе. Это может быть дата и время транзакции, создания записи или изменения данных.
• СУБД: MySQL, SQL Server.
• TIMESTAMP
• Описание: TIMESTAMP — это тип данных, который также хранит дату и время, но с учетом временной зоны. Он автоматически обновляется при каждом изменении записи, что делает его полезным для отслеживания изменений в данных. Формат хранения схож с DATETIME.
• Пример: 2024-08-15 14:30:00
• Применение: TIMESTAMP используется для автоматического ведения журналов и отслеживания изменений в данных, например, когда необходимо знать точное время последнего обновления записи.
• СУБД: PostgreSQL, MySQL, Oracle.
• INTERVAL
• Описание: INTERVAL — это тип данных, используемый для хранения временных интервалов, таких как разница между двумя датами или временными метками. В зависимости от реализации SQL, интервалы могут включать дни, месяцы, годы, часы, минуты и секунды.
• Пример: INTERVAL '2 days 3 hours'
• Применение: Тип данных INTERVAL применяется для вычисления разницы между двумя временными значениями, например, для подсчета количества дней между двумя датами или времени, прошедшего с момента выполнения определенного действия.
• СУБД: PostgreSQL, Oracle.

Поддержка долей секунды

Многие типы данных, такие как TIME, DATETIME и TIMESTAMP, поддерживают хранение долей секунды (микросекунд), что позволяет хранить и работать с более точными временными метками. Например, формат TIMESTAMP(6) позволяет хранить временные метки с точностью до миллисекунд.

Работа с временными зонами

Некоторые СУБД поддерживают работу с временными зонами для типов данных TIMESTAMP WITH TIME ZONE, что позволяет хранить дату и время с учетом временной зоны. Это полезно для глобальных приложений, где время события важно фиксировать с учетом часового пояса.

Логические типы данных

Логические типы данных в SQL используются для хранения булевых значений, то есть значений, которые могут быть либо истинными (TRUE), либо ложными (FALSE). Эти типы данных часто применяются в условиях запросов, проверках и фильтрации данных. Хотя SQL стандартно не определяет логический тип данных как отдельный тип, большинство современных СУБД поддерживают их или аналогичные конструкции.

• BOOLEAN (BOOL)
• Описание: BOOLEAN — это тип данных, предназначенный для хранения логических значений. Значение типа BOOLEAN может быть TRUE, FALSE или NULL (неопределённое значение). В некоторых СУБД тип данных BOOLEAN сокращенно обозначается как BOOL.
• Применение: Логические типы данных используются для хранения флагов или состояний, таких как:
• Активен ли пользователь? (is_active)
• Успешно ли завершено задание? (is_completed)
• Подтверждено ли действие? (is_confirmed)

Поддержка логических типов в различных СУБД

В PostgreSQL тип данных BOOLEAN полностью поддерживается и может принимать значения TRUE, FALSE, или NULL. Альтернативные записи: `t` или `f`, `1` или `0` (где `1` интерпретируется как TRUE, а `0` как FALSE).

В MySQL тип данных BOOLEAN является псевдонимом для TINYINT(1). Значение 0 интерпретируется как FALSE, а любое ненулевое значение — как TRUE.

В SQLite логический тип данных также представлен как INTEGER, где 0 означает FALSE, а 1 — TRUE.

В Microsoft SQL Server логический тип данных представлен как BIT. Он может принимать значения 0 (FALSE), 1 (TRUE) или NULL.

Двоичные типы данных

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

• BINARY
• Описание: BINARY(n) — это тип данных, который используется для хранения фиксированного количества байтов. Значение должно иметь точную длину, равную n, и, если вводимое значение короче, оно заполняется нулями до указанной длины.
• Пример: BINARY(5) для хранения 101010 будет хранить его как 10101000 (добавляются нули до достижения длины 5 байтов).
• Применение: BINARY используется в тех случаях, когда требуется хранить бинарные данные фиксированной длины, например, зашифрованные пароли, идентификаторы или хеши.
• СУБД: MySQL, SQL Server.
• VARBINARY
• Описание: VARBINARY(n) — это тип данных, который используется для хранения бинарных данных переменной длины, где n — это максимальное количество байтов, которое может быть сохранено. В отличие от BINARY, этот тип данных хранит только фактическое количество байтов, и длина может варьироваться.
• Пример: VARBINARY(5) для хранения 101010 будет хранить ровно 101010, без добавления нулей.
• Применение: VARBINARY используется для хранения данных переменной длины, таких как изображения, файлы, документы, мультимедиа или любые другие двоичные данные, длина которых может меняться.
• СУБД: MySQL, SQL Server.
• BLOB (Binary Large Object)
• Описание: BLOB — это тип данных, который используется для хранения больших объемов двоичных данных, таких как изображения, аудио и видеофайлы, документы и другие типы файлов. В зависимости от СУБД, BLOB может быть разделен на несколько категорий, таких как TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB, которые различаются по максимальному объему данных, которые могут быть сохранены.
• Применение: BLOB используется для хранения больших объемов данных, таких как фотографии, аудио- и видеозаписи, документы и другие типы файлов, которые невозможно хранить в текстовых или числовых полях.
• СУБД: MySQL, SQLite, Oracle.

Эпилог

Надеемся, что теперь вам будет легче ориентироваться в типах данных! Их понимание в SQL очень важно для эффективного использования баз данных, сохранения информации в целостности и улучшения производительность запросов. Не забывайте заглядывать в документацию, чтобы точно знать, какие типы данных поддерживает именно ваша СУБД.