Типы данных и индексы к ним в СУБД (MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database)

Привет, Вы узнаете о том , что такое типы данных в субд , Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое типы данных в субд , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.

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

1 Контроль корректности данных

Тип данных гарантирует, что в поле попадет только допустимое значение.

Пример:

• поле age → только число

• поле email → строка

• поле is_active → только TRUE/FALSE

без типов можно было бы записать "hello" в возраст

2 Экономия памяти

Каждый тип занимает разный объем памяти.

Пример:

• INT → 4 байта

• BIGINT → 8 байт

• CHAR(100) → всегда 100 символов

• VARCHAR(100) → только фактическая длина

правильный выбор типа = меньше размер базы

3 Скорость работы

СУБД оптимизирует операции в зависимости от типа.

• числа быстрее сортируются чем строки

• индексы по INT работают быстрее чем по TEXT

• DATE быстрее сравнивать чем строку "2027-02-13"

правильные типы = быстрые запросы

4 Возможность вычислений

Тип определяет, какие операции можно выполнять:

нельзя сложить текст "hello" + "world" как числа

5 Индексация и поиск

Индексы работают по-разному в зависимости от типа:

• по числам — быстро

• по датам — быстро

• по тексту — сложнее

выбор типа влияет на скорость поиска

6 Безопасность и целостность

Типы защищают данные от ошибок:

• нельзя записать дату "abc" в поле DATE

• нельзя записать текст в поле INT

• нельзя записать отрицательное значение в UNSIGNED

это снижает количество багов

7 Логика и смысл данных

Типы помогают понять что хранится в колонке:

база становится понятной другим разработчикам

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

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

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

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

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

Сравнение типов

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

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

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. Об этом говорит сайт https://intellect.icu . Числа с плавающей точкой

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

• 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.

таблица сравнения основных типов строковых полей в MySQL и их ограничений:

Важные моменты:

• Ограничение VARCHAR зависит от кодировки:

  • latin1 (1 байт/символ) → максимум ~65,535 символов.

  • utf8mb4 (до 4 байт/символ) → максимум ~16,383 символа.

• Общий размер строки в таблице не может превышать 65,535 байт. Если у вас несколько больших VARCHAR полей, они могут превысить лимит.

• Для длинных текстов лучше использовать TEXT или его расширенные варианты.

В MySQL возможность индексирования зависит от типа поля и его длины:

Важные моменты:

• Для VARCHAR и CHAR можно создавать обычные B-Tree индексы.

• Для TEXT и его разновидностей — только префиксные индексы (например, первые 100 символов).

• Для поиска по длинным текстам лучше использовать FULLTEXT индекс (поддерживается для CHAR, VARCHAR, TEXT).

• Ограничение длины индекса зависит от кодировки:

  • latin1 (1 байт/символ) → можно индексировать больше символов.

  • utf8mb4 (до 4 байт/символ) → индексируемая длина строки уменьшается.

Таким образом:

• например VARCHAR(266) можно индексировать напрямую.

• TEXT, MEDIUMTEXT, LONGTEXT — только префиксами или через FULLTEXT.

Дата и время

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

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

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

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

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

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

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

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

В 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 предназначены для хранения данных в виде необработанных байтов или битов, что делает их полезными для хранения изображений, файлов, криптографических ключей и других видов бинарных данных. Они позволяют сохранять и управлять данными, которые не могут быть представлены как обычный текст или числа.

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

Специальные типы (в продвинутых СУБД)

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